DLookUp Statement Help

[d9pierce1]

  CboContactPhoneType = DLookup("ContactPhoneID", "ContactPhoneByLocationQry", "CompanyLocationID=" & [CboLocationID] & "  AND Preferred=True"

I need to add to the above statement "CompanyContactID=" & [TxtCompanyContactID] & " and I cant figure it out?
Thanks

[Bob Fitz]

What data type are fields "CompanyLocationID" and "Preferred" in the table

[d9pierce1]

This is used on the OnCurrent of form. CompanyLocationID is a # and Perferred is a chkBox.
What this does is limit the current phone numbers to a single company by the LocationID. I want to add so that it also limits to the CompanyContactID also.
Example:\
Company can have multi locations, and Locations can have multi Contacts so i want to limit my phone numbers to the company location and by the company contact.

[Bob Fitz]

Try:
CboContactPhoneType = DLookup("ContactPhoneID", "ContactPhoneByLocationQry", "CompanyLocationID=" & [CboLocationID] & " AND [Preferred]= -1")

[d9pierce1]

Hi, I dont think you understood or i didnt make myself clear enough. The origional code works well, i just need to add a second part to it "CompanyContactID=" & [TxtCompanyContactID] & " along with the other code
I just dont know how to nest this in a statement.

[Bob Fitz]

Try:
CboContactPhoneType = DLookup("ContactPhoneID", "ContactPhoneByLocationQry", "CompanyContactID=" & [TxtCompanyContactID] & " AND CompanyLocationID=" & [CboLocationID] & " AND Preferred=True"

[d9pierce1]

I get a syntax error on open
Compile error

[Bob Fitz]

What do you get if you open the forms code module and type:

? DLookup("ContactPhoneID", "ContactPhoneByLocationQry", "CompanyContactID=" & [TxtCompanyContactID] & " AND CompanyLocationID=" & [CboLocationID] & " AND Preferred=True"

in the immediate window (followed by "Return" key of course

[d9pierce1]

Expected list separator or )
Compile Error

[Bob Fitz]

Expected list separator or )
Compile Error

Sorry. Try

? DLookup("ContactPhoneID", "ContactPhoneByLocationQry", "CompanyContactID=" & [TxtCompanyContactID] & " AND CompanyLocationID=" & [CboLocationID] & " AND Preferred=True")

[d9pierce1]

External name not defined

[d9pierce1]

I also tried the one i am currently using and it says the same thing but it works for setting the form on current. Not sure what is going on here.

What i am doing with this is a combo on my company contacts form. I want that combo only to show my phone numbers for that contact by that location.
Example:
Company = Parkway
Location = Corporate Office
Contact - David Pierce
Below should be in combo
Phone Line Moile Type 2145429664
Phone Line Office Type 7690337878

Contact = Rob Sanders
Below should be in combo
Phone Office Direct Line Type 9198989999
and so on...

When my contact form opens it opens up to the correct location.

I must need to do some testing and such.

Maybe a where clause in the combo?

Thanks

[Bob Fitz]

Maybe a where clause in the combo?

I think so

[d9pierce1]

I fixed it, just stupid me.... One day i am going to get this correct! ONE DAY
Thanks
Dave