Help with DLookUp Command

[Guest]

I have an Access 2003 form. I have an unbound text field (TOTAL). I also
have a text field named (SCORE). Whenever the user changes the value in the
field (SCORE) I want to run a function that looks up the value of the
[Points] field in the TblPointScale Table, where the value of the [GPAOver]
field in the table matches the value of the [SCORE] control on the form. I
then want that value to appear in the [TOTAL] field on the form.

This is what I have that isn't working:

DLookup("[Points]", "[TblPointScale]", "[GPAOver] = Forms![SCORE]") = True

Thanks in advance.

 

[George Nicholson]

In txtSCORE_AfterUpdate:

If SCORE and GPAOver are numeric:
Me.txtTOTAL = DLookup("[Points]", "TblPointScale", "[GPAOver] = " &
Forms![SCORE])
If SCORE and GPAOver are text:
Me.txtTOTAL = DLookup("[Points]", "TblPointScale", "[GPAOver] = '" &
Forms![SCORE] & "'")

HTH,

 

[Guest]

George, I tried this and can't get it to work. I keep getting a Type
Mismatch error on [SCORE]. I verified that all of these fields are text and
then tried the line you said was for text and I got the type mismatch. So, I
went back and made them all number fields and used the other expression and
still get the type mismatch error. Score and GPAOVER have decimals and the
points field has single digits. Does this matter?

In txtSCORE_AfterUpdate:

If SCORE and GPAOver are numeric:
Me.txtTOTAL = DLookup("[Points]", "TblPointScale", "[GPAOver] = " &
Forms![SCORE])
If SCORE and GPAOver are text:
Me.txtTOTAL = DLookup("[Points]", "TblPointScale", "[GPAOver] = '" &
Forms![SCORE] & "'")

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I have an Access 2003 form. I have an unbound text field (TOTAL). I also
have a text field named (SCORE). Whenever the user changes the value in
the
field (SCORE) I want to run a function that looks up the value of the
[Points] field in the TblPointScale Table, where the value of the
[GPAOver]
field in the table matches the value of the [SCORE] control on the form. I
then want that value to appear in the [TOTAL] field on the form.

This is what I have that isn't working:

DLookup("[Points]", "[TblPointScale]", "[GPAOver] = Forms![SCORE]") = True

Thanks in advance.

 

[George Nicholson]

Score and GPAOVER have decimals and the points field has single digits.

Does this matter?

It shouldn't. GPAOVER and SCORE should be of the same type because you are
comparing them. POINTS, the return value, doesn't have to match them.

I don't know what to tell you. Tracking down the error now becomes detective
work.
- Try replacing your variable with a "real" value and typing it in the
Immediate pane of the VBE until you get something that you *know* works:
?DLookup("[Points]", "TblPointScale", "[GPAOver] = '25.6'")
?DLookup("[Points]", "TblPointScale", "[GPAOver] = 25.6")
?DLookup("[Points]", "TblPointScale", "Cdbl([GPAOver]) = Cdbl(25.6)")
etc., (i.e., anything else you can think of)
Once you get it working, incorporate your variable. If it "stops" working,
you can focus on "why doesn't my variable contain the value I think it
should?". Try setting a breakpoint on that line in the AfterUpdate event and
see how
?"[GPAOver] = '" & Forms![SCORE] & "'"
evaluates in the Immediate window at that moment.

Strike that! I just saw the problem, I think.
?? Forms![SCORE] ???
unless [SCORE] is the name of a form (which I don't think it is), that's the
wrong syntax.
Maybe
Forms!ThisFORM!SCORE ?
or
Me!SCORE ?
instead.

Me.txtTOTAL = DLookup("[Points]", "TblPointScale", "[GPAOver] = '" &
Me.txtSCORE & "'")
should compare GPAOVER to the value of the SCORE control rather than to a
(non-existent) mismatching form object.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

George, I tried this and can't get it to work. I keep getting a Type
Mismatch error on [SCORE]. I verified that all of these fields are text
and
then tried the line you said was for text and I got the type mismatch.
So, I
went back and made them all number fields and used the other expression
and
still get the type mismatch error. Score and GPAOVER have decimals and
the
points field has single digits. Does this matter?

In txtSCORE_AfterUpdate:

If SCORE and GPAOver are numeric:
Me.txtTOTAL = DLookup("[Points]", "TblPointScale", "[GPAOver] = " &
Forms![SCORE])
If SCORE and GPAOver are text:
Me.txtTOTAL = DLookup("[Points]", "TblPointScale", "[GPAOver] = '" &
Forms![SCORE] & "'")

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I have an Access 2003 form. I have an unbound text field (TOTAL). I
also
have a text field named (SCORE). Whenever the user changes the value
in
the
field (SCORE) I want to run a function that looks up the value of the
[Points] field in the TblPointScale Table, where the value of the
[GPAOver]
field in the table matches the value of the [SCORE] control on the
form. I
then want that value to appear in the [TOTAL] field on the form.

This is what I have that isn't working:

DLookup("[Points]", "[TblPointScale]", "[GPAOver] = Forms![SCORE]") =
True

Thanks in advance.