Error using textbox value in dlookup criteria

[TheKillerMonkey]

The problem I'm having is that Access doesn't like me comparing the value of a textbox to the value in one of my tables. I'll list everything because its small and I can explain things easier.

3 Tables:
1) "Properties". Headers "ID", "Property". Indexed values of "Mascara", "Foundation", "Nails"
2) "ICs". Headers "IC", "Property_FK". Indexed values for "IC" of the numbers 1-5 as numbers. 1 and 2 are mascara, 3 and 4 are foundation, 5 is nails. Property_FK is in a relationship with property from "Properties" (combobox dlookup for values). [IC = Item Code]
3) "pH Test". Headers "Property_FK", "pH". Again property is pulled directly from the properties table. pH column is a yes/no box. values can be whatever, for this test I had mascara and foundation as yes's.


1 Form:


1) "Login".
"cbIC". Combobox that pulls values from "ICs" to list the Item Codes.
"tbProp". Textbox that pulls Property from "ICs" using the IC as a lookup.
"tbPH". Textbox that will display an "X" if the box is checked in pH test.

The combobox and textbox work fine. As I change selections in the combobox the property box updates accordingly. The problem is getting the tbPH box to work.

 If(DLookUp("[pH]", "pH Test", "[Property_FK]="& "[tbPH].[Value]","X","")

is the closest thing that I have gotten to work. The problem is that it is not updating as new ICs are selected, it is just sticking with the initial value and not changing. I've made a test textbox and [tbPH].[Value] works and updates as necessary. The problem is that in the DLookUp function if I drop the quotes around tb.value I get an error. So I'm not entirely sure what syntax I need to get it right or if I need to store my data in a different format to make the comparison work like I want it to.

Any help would be appreciated!
Thanks,
-Matt

[Perceptus]

dont forget the qualifiers around pH test. so its
If(DLookUp("[pH]", "[pH Test]", "[Property_FK]="& "[tbPH].[Value]","X","")

I also dont think the Inline If has an override in Access for using it like you have. If you want to use Inline IF use
IIf(DLookUp("[pH]", "[pH Test]", "[Property_FK]="& "[tbPH].[Value]","X","")

[John_G]

Your Dlookup is incorrect. You want to put a value INTO tbPH, not use it in the criteria. What text box on your form contains the search value? I'm going to assume it is tbProp, and that it is numeric.

So, your DLookup becomes: DLookUp("[pH]", "[pH Test]", "[Property_FK]=" & [tbProp].[Value])

Note the addition of the required square brackets around PH Test (because PH Test contains a blank)

Adding the IIF gives you : iif(DLookUp("[pH]", "[pH Test]", "[Property_FK]=" & [tbProp].[Value]), "X", "")

You woud use that expression as the source for the form control tbPH:

=iif(DLookUp("[pH]", "[pH Test]", "[Property_FK]=" & [tbProp].[Value]), "X", "")

[TheKillerMonkey]

=iif(DLookUp("[pH]", "[pH Test]", "[Property_FK]=" & [tbProp].[Value]), "X", "")

^evaluates into an error. Which is essentially the problem. If I change [tbProp].[Value] to "[tbProp].[Value]",it evaluates but does not update with a new selection of the combobox.

"You want to put a value INTO tbPH, not use it in the criteria. What text box on your form contains the search value? I'm going to assume it is tbProp, and that it is numeric."
Yea, small typo on my part. It is tbProp, however the values in it are going to be text. Changing it to [tbProp].[Value]&"" evaluates to an error.

[John_G]

What is the error you get?

If tbProp is text, try this (changes in red)

=iif(DLookUp("[pH]", "[pH Test]", "[Property_FK]='" & [tbProp].[Value] & "'" ), "X", "")
Note this leading single quote.......................................^

(for future reference, when you post a question, you should always show the text of any error messages you are getting)

[TheKillerMonkey]

Adding the single quotes seems to have fixed it. Thanks!

Is there more of a detailed error description other than #Error?

[John_G]

Is there more of a detailed error description other than #Error?

Unfortunately, when you get that situation, no.

I was thinking of the error message box you get in the case of a syntax error or other run-time error.

That #Error "value" in a form or query is one of the more frustrating problems to solve.

Glad you got it working.