Help with DLookup

[Topham]

Hello,

Im having some issues with dlookup. Im trying to get a value from a query and insert it into a specific field on a form.

Here is the code im trying to use:

Forms![Main Booking Form]!CustomerID=DLookUp("[CustomerID]","Qry: Contacts","CustomerID=" & Forms![Main Booking Form]!CustomerID)

The form field is 'Forms![Main Booking Form]!CustomerID' i want the data to come from the 'CustomerID' column from the 'Qry:Contacts' query. Now this part i cant get round to. I want the record thats in the 'CustomerID' Column within the 'Qry:Contacts' Query to go in the 'Forms![Main Booking Form]!CustomerID' which is a field called 'CustomerID' on the 'Main Bookin Form' (as if you couldnt tell ).

I cant seem to work this out and ive also searched forums and google.

Much Appreciated for the help.

Regards,
Topham

 

[rainman89]

You are asking it to get its value from itself!

you need to base the customer ID off of something else... is this form based off of a query or is it an unbound form?

 

[boblarson]

1. You should NOT use special characters in object or field names. Using a colon in a query name is NOT GOOD. Remove that (and I would suggest not using spaces either as it complicates matters having to know when to use square brackets and all).

2. For what you do have you need to include the query name in square brackets because it has a special character in it and spaces.

 

[Topham]

Thanks for the quick replies. Im taking on this database half done, so im not too sure if the form is bound or not.

Also the CustomerID is used in Bookings and Rides tables to associate bookings and rides to that particular Customer.

 

[boblarson]

So, without name changes you would use:

If CustomerID is numeric:
Forms![Main Booking Form]!CustomerID = DLookUp("[CustomerID]","[Qry: Contacts]","CustomerID=" & Forms![Main Booking Form]!CustomerID)

If CustomerID is Text:
Forms![Main Booking Form]!CustomerID = DLookUp("[CustomerID]","[Qry: Contacts]","CustomerID=" & Chr(34) & Forms![Main Booking Form]!CustomerID & Chr(34))

 

[Topham]

Boblarson

Ive changed the Query name to ContactSearch so intead im using:

Forms![Main Booking Form]!CustomerID=DLookUp("[CustomerID]","ContactSearch","CustomerID=" & Chr(34) & Forms![Main Booking Form]!CustomerID & Chr(34))

But im getting Error 2950.

Im using a button with OnClick:Embedded macro with the macros listed as.
Action: OpenQuery Arguments: ContactSearch, Datasheet, Read Only
Action: RunCode Arguments: Forms![Main Booking Form]!CustomerID=DLookUp("[CustomerID]","ContactSearch","CustomerID=" & Chr(34) & Forms![Main Booking Form]!CustomerID & Chr(34))
Action: Close Arguments: Query, ContactSearch, No

Regards,
Mathew

 

[Topham]

Cant get this to work so ive tried putting code into a private sub.

Code used:

Private Sub Open_search_contact_form_Click()

DoCmd.OpenQuery ("Contacts Query")

Forms![Main Booking Form]!CustomerID = DLookup(CustomerID, [Contacts Query], "Forms![Main Booking Form]!CustomerID=" & [CustomerID])

DoCmd.Close (acQuery = ContactSearch)


End Sub

what i need done is once the query has completed. I need Result from CustomerID from the query to be inserted into the CustomerID Field of a form that is open.