dlookup problem

[Karen]

i have a form that i use as a dialog box. when the user enters a code in
the unbound control on the dialog form i want to search the table to see if
the code is found in there. IF the code is found, i want to open another
form and display some information about the reagent. If the code is not
found i want to display a message. I have the following code attached to
the 'OK' button on the dialog form. the problem with it is that
alphanumeric codes return an error message of Runtime Error '13': Type
Mismatch. in the reagents table, fldcode is text. [frm shelflife dialog
box]!Text0 has nothing in the format property. Numeric codes work
correctly.

'check to see if the code is in tblreagents
'if the code is found, display a message
If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm shelflife
dialog box]!Text0") Then
'open the shelflife form
DoCmd.Minimize
DoCmd.OpenForm "frmshelflife", acNormal

'if not found
Else
'display a message
MsgBox "The code you entered is not valid", vbExclamation, "Code Not
Found"


Does anyone have any suggestions on how i can do this without the runtime
error?

Karen

 

[Wayne Morgan]

If FldCode is text, then values passed reference it need to be text. Text
values are delimited with quotes.

DLookup("[FldCode]", "tblReagents", "[FldCode] = '" & Forms![frm SelfLife
Dialog Box]!Text0 & "'")
or
DLookup("[FldCode]", "tblReagents", "[FldCode] = """ & Forms![frm SelfLife
Dialog Box]!Text0 & """")

This assumes that [frm SelfLife Dialog Box] is available to the code. If
this form is the dialog form on which the code is running, you could shorten
it to:

DLookup("[FldCode]", "tblReagents", "[FldCode] = """ & Me!Text0 & """")

The way you had it originally, the reference to the form was being passed to
DLookup(), so you needed the full path to the form so DLookup() could find
it. This method simply passes the value from the form to DLookup() by
concatenating the value into the WHERE string. Staying with your original
option, you may also be able to use:

DLookup("[fldcode]", "tblReagents", "[fldcode] = CStr(Forms![frm shelflife
dialog box]!Text0)")

Looking further, I don't think your type mismatch is in the DLookup
statement. I'm leaving the information above in case it is there also. I
believe the problem to be the If...Then clause.

If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm shelflife
dialog box]!Text0") Then

You're not setting the DLookup equal to anything. Therefore, the DLookup
itself must equate to True or False, which is what an If...Then statement
expects. Access will treat any non zero numeric value as True. When the
lookup is a text value, you are getting a text result. This doesn't equate
to True or False and so you have a type mismatch.

If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm shelflife
dialog box]!Text0") = Forms![frm SelfLife Dialog Box]!Text0 Then

or

If Not IsNull(DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm
shelflife dialog box]!Text0")) Then

This one works because DLookup will return Null if the value isn't found. If
it doesn't return Null, then the value is found. IsNull() will return True
or False which is what is needed by the If...Then statement.

--
Wayne Morgan
MS Access MVP

i have a form that i use as a dialog box. when the user enters a code in
the unbound control on the dialog form i want to search the table to see
if
the code is found in there. IF the code is found, i want to open another
form and display some information about the reagent. If the code is not
found i want to display a message. I have the following code attached to
the 'OK' button on the dialog form. the problem with it is that
alphanumeric codes return an error message of Runtime Error '13': Type
Mismatch. in the reagents table, fldcode is text. [frm shelflife dialog
box]!Text0 has nothing in the format property. Numeric codes work
correctly.

'check to see if the code is in tblreagents
'if the code is found, display a message
If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm shelflife
dialog box]!Text0") Then
'open the shelflife form
DoCmd.Minimize
DoCmd.OpenForm "frmshelflife", acNormal

'if not found
Else
'display a message
MsgBox "The code you entered is not valid", vbExclamation, "Code Not
Found"


Does anyone have any suggestions on how i can do this without the runtime
error?

Karen

 

[Karen]

I see what you mean in the If...Then statement. I've added the = forms![frm
shelflife dialog box]!text0 into the If...Then statement and it works now.
My confusion was that numeric codes were working and alphanumerics were not
so I focused on the text versus numeric as being the problem.

Thanks for helping me with this. These newsgroups have really helped me get
my project completed and helped me learn more about Access.

Karen

If FldCode is text, then values passed reference it need to be text. Text
values are delimited with quotes.

DLookup("[FldCode]", "tblReagents", "[FldCode] = '" & Forms![frm SelfLife
Dialog Box]!Text0 & "'")
or
DLookup("[FldCode]", "tblReagents", "[FldCode] = """ & Forms![frm SelfLife
Dialog Box]!Text0 & """")

This assumes that [frm SelfLife Dialog Box] is available to the code. If
this form is the dialog form on which the code is running, you could shorten
it to:

DLookup("[FldCode]", "tblReagents", "[FldCode] = """ & Me!Text0 & """")

The way you had it originally, the reference to the form was being passed to
DLookup(), so you needed the full path to the form so DLookup() could find
it. This method simply passes the value from the form to DLookup() by
concatenating the value into the WHERE string. Staying with your original
option, you may also be able to use:

DLookup("[fldcode]", "tblReagents", "[fldcode] = CStr(Forms![frm shelflife
dialog box]!Text0)")

Looking further, I don't think your type mismatch is in the DLookup
statement. I'm leaving the information above in case it is there also. I
believe the problem to be the If...Then clause.

If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm shelflife
dialog box]!Text0") Then

You're not setting the DLookup equal to anything. Therefore, the DLookup
itself must equate to True or False, which is what an If...Then statement
expects. Access will treat any non zero numeric value as True. When the
lookup is a text value, you are getting a text result. This doesn't equate
to True or False and so you have a type mismatch.

If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm shelflife
dialog box]!Text0") = Forms![frm SelfLife Dialog Box]!Text0 Then

or

If Not IsNull(DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm
shelflife dialog box]!Text0")) Then

This one works because DLookup will return Null if the value isn't found. If
it doesn't return Null, then the value is found. IsNull() will return True
or False which is what is needed by the If...Then statement.

--
Wayne Morgan
MS Access MVP

i have a form that i use as a dialog box. when the user enters a code in
the unbound control on the dialog form i want to search the table to see
if
the code is found in there. IF the code is found, i want to open another
form and display some information about the reagent. If the code is not
found i want to display a message. I have the following code attached to
the 'OK' button on the dialog form. the problem with it is that
alphanumeric codes return an error message of Runtime Error '13': Type
Mismatch. in the reagents table, fldcode is text. [frm shelflife dialog
box]!Text0 has nothing in the format property. Numeric codes work
correctly.

'check to see if the code is in tblreagents
'if the code is found, display a message
If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm shelflife
dialog box]!Text0") Then
'open the shelflife form
DoCmd.Minimize
DoCmd.OpenForm "frmshelflife", acNormal

'if not found
Else
'display a message
MsgBox "The code you entered is not valid", vbExclamation, "Code Not
Found"


Does anyone have any suggestions on how i can do this without the runtime
error?

Karen