Switch from using DLookup to using a Query to lookup customer info.

[dmon2010]

There are 3 fields on my access 2007 form that are called "AdministeredBy", "PaymentBy" and "ShipTo". They are currently combo boxes where I select a customer number. Under each of these are several fields that use a DLookup function to bring up customer info from another table (First field looks up customer name, second looks up street address 1, third is street address 2, forth is city, then state, then zip).

The dlookup will sometimes act up whenever I make any design changes to the form, such as moving, adding or deleting a button.



I want to go from using the DLookup function to using a query to lookup the information, but I don't know how to start this.

I'm still new to Access. Any help would be greatly appreciated.

[ranman256]

Select * from tCustomer where [CustID] = " & forms!frmMain!cboCustID

will pull the customer info based on the combo box.
Save as a query and you can just open it or use it a a sub form too.

[dmon2010]

I paste that into each field?

[ItsMe]

Select * from tCustomer where [CustID] = " & forms!frmMain!cboCustID

will pull the customer info based on the combo box.
Save as a query and you can just open it or use it a a sub form too.

That is not the correct syntax it would be more like
where (([tCustomer.CustID])=forms!frmMain.cboCustID)

[dmon2010]

(([tCustomer.CustID])=forms!frmMain.cboCustID)

So I would change:

Customer = the name of my customers table
frmMain = The name of my form (or do I leave the 'frm')
CustID = the name of each combo box (AdminBy, PayBy or ShipTo)

Is that correct?

[ItsMe]

Is that correct?

Sounds about right. The easiest approach to create a parameterized query is to type directly into the grid located towards the bottom of the Query Builder while in Design View. So, in the Criteria field

Forms!
then hit tab
Select the form name and hit tab

then type the name of the control you are after.

The result will be a WHERE clause similar to what has been posted here. One word of advise, you want to test for null or empty strings incase your combo's do not have anything in them, like when the form opens. I don't normally build Dynamic Parameterized Queries but my preference is an Instant If function to test for Null.