Passing values in Access Forms

Question

I have two tables and a form in a Microsoft Access Database. Lets the first table tblCUSTOMERS, and the second table tblINVOICES. tblCUSTOMERS contains fields called CustomerID (primary key), FirstName, and LastName. tblINVOICES contains fields called InvoiceID (primary key), CustomerID (foreign key), and Amount. The form is called frmInvoices and contains textboxes for the fields in tblINVOICES.

What I what to do is create functionality that allows me to search through my customers table, select a customer record from that table, and then return the CustomerID of that record to frmInvoices. Ideally the searching would be in a format like a data grid that allows searching by FirstName, LastName, and CustomerID.

Specifically can you advise me of the simplest way to: 1. Insert a form (lets call this new form frmCUSTOMERS) with something like a datagrid control to show customer records. 2. Update the datagrid on frmCUSTOMERS to display only records matching a query of tblCUSTOMER (such as only customers where firstname starts with 'B') 3. Pass the CustomerID from frmCUSTOMERS to frmINVOICES.

Many thanks,

Brett

HK1 HK1 · Accepted Answer · 2012-05-02T03:11:57

You can pass a value as an opening argument to a form when you open it.

DoCmd.OpenForm "frmFormName", , , , , ,"B"

Or you can pass in a criteria statement:

DoCmd.OpenForm "frmFormName", , , "FirstName LIKE 'B*'"

If you go the first route you would do something like this:

Private Sub Form_Load()
    Me.Filter = "FirstName LIKE '" & Nz(Me.OpenArgs, "") & "*'"
    'or
    Me.Subform1.Form.Filter = "FirstName LIKE '" & Nz(Me.OpenArgs, "") & "*'"
End Sub

To hand a value such as CustomerID back from the search form, there are numerous options. You could put the value in a global variable. Or you could have the search form give the value back to the calling form by using a Public Variable or Public Function on the calling form.

Arguably, a more correct and cleaner way of doing all this would be to design a Class that handles opening the search form and passing of the inbound and outbound values. Classes do take more knowledge and skill to write and it would probably be overkill for what you are trying to do. A class is certainly not required to make this work. It would only be a little cleaner. In reality, you would use many of the same techniques I listed above if you did write a class for this.

Passing values in Access Forms

1 Answers