syntax check?

[krc777]

I have the following statement:
PIHP_CMHSP_MHP.Value = DLookup("PIHP-CMHSP-MHP", "PIHP", "countycd = " & Combo2.Value & " and PFcd = " & Combo8.Value)

The combo2 and combo8 values are both correct but I get an error "Runtime 2001. You canceled the previous operation." when I reach this line.

Thanks.

 

[CraigDolphin]

Couple of things to try:

If Combo2 or Combo8 contain text strings you may need to include single quotes similar to....

PIHP_CMHSP_MHP.Value = DLookup("PIHP-CMHSP-MHP", "PIHP", "[countycd] = '" & Combo2.Value & "' And [PFcd]='" & Combo8.Value & "'")

Also, are you sure that the Dlookup is actually returning a value? Try enclosing your dlookup in an Nz function e.g., Nz(Dlookup(a,b,c),"Value/Text to use if null") to trap for that situation.

 

[DrSnuggles]

You should highlight DLookup and press F1. This will show you the correct syntax for DLookup function.
Plus, are the two values you are using in the WHERE clause numeric?

Anyway, try this:
PIHP_CMHSP_MHP.Value = DLookup("[PIHP-CMHSP-MHP]", "PIHP", "[countycd] = " & Combo2.Value & " and [PFcd] = " & Combo8.Value)

What is control type is PIHP_CMHSP_MHP?

 

[krc777]

The two values in the combo box are numeric. Thanks for the check on that. And there are no null values in my text db. I just have 3 results that should work. I will try your suggestions too. Thanks.

 

[krc777]

Thank you!! The:
PIHP_CMHSP_MHP.Value = DLookup("[PIHP-CMHSP-MHP]", "PIHP", "[countycd] = " & Combo2.Value & " and [PFcd] = " & Combo8.Value)
worked great!

 

[boblarson]

Thank you!! The:
PIHP_CMHSP_MHP.Value = DLookup("[PIHP-CMHSP-MHP]", "PIHP", "[countycd] = " & Combo2.Value & " and [PFcd] = " & Combo8.Value)
worked great!

And the reason it did, is that the square brackets are telling Access (especially if your field names and control names are the same) that it wants to look at the FIELD and not the control.