Search code examples
sqlformsms-access

MS Access: Using the value of the field in a given column of the selected row of a ListBox as criterion in a query

On a form "Search Invoices By Customer ID 2" I have a ListBox control "invoiceList" showing a set of records from the table "InvoiceFirsts".

I want to get a the contents of a column from the selected row in the ListBox.

I have a test text box "Text6" on the same form whose control source is "=[invoiceList].Column" , and the value I want is there.

I want to use this same value as a criterion to select a single record in a query "one invoice". I set the criteria for the field INVOICE_NO in the table InvoiceFirsts to

=[Forms]![Search Invoices By Customer ID 2]![invoiceList].Column

Running the query returns a dialog "Undefined function '[Forms]![Search Invoices By Customer ID 2]![invoiceList].[Column]' in expression.

The SQL view shows me:

SELECT InvoiceFirsts.INVOICE_NO FROM InvoiceFirsts WHERE (((InvoiceFirsts.INVOICE_NO)=[Forms]![Search Invoices By Customer ID 2]![invoiceList].Column));

Why can't I use the same syntax for the query as I do for the text box, or alternatively, how should I go about selecting by the contents of a field in the selected row of a ListBox?

Solution

  • You can reference a function in your SQL so you can create a public function in a module to get the invoice number and return it to the SQL:

    Public Function fInvoice() As Variant
  fInvoice = [Forms]![Search Invoices By Customer ID 2]![invoiceList].Column(1)
End Function

    Change your SQL:

    SELECT InvoiceFirsts.INVOICE_NO FROM InvoiceFirsts WHERE InvoiceFirsts.INVOICE_NO=fInvoice();

    This will only work if your form is still open. The query cannot be run by itself.