DLookup("[PVIN]", "tblPreliminary VINs", "[PVIN]=Forms!frmVINOD O!SerialNum")

MLH

DLookup("[PVIN]", "tblPreliminary VINs",
"[PVIN]=Forms!frmVINOD O!SerialNum")

is giving me a Type Mismatch error. That's confusing to me
and I don't know how to circumvent it. The [PVIN] field in
tblPreliminaryV INs is a 17-char text field. Forms!frmVINODO !SerialNum
is just an unbound textbox on a form (frmVINODO).

I run the DLookup during the textbox's BeforeUpdate event code. Some
VIN values I type in there do NOT give rise to the error. Some do. By
nature, the VIN values are alphanumeric. Should I be wrapping the
Forms!frmVINODO !SerialNum in CStr( ) or something?

Jan 11 '06 #1

Subscribe Reply

2230

Randy Harris

"MLH" <CR**@NorthStat e.net> wrote in message
news:6q******** *************** *********@4ax.c om...

DLookup("[PVIN]", "tblPreliminary VINs",
"[PVIN]=Forms!frmVINOD O!SerialNum")

is giving me a Type Mismatch error. That's confusing to me
and I don't know how to circumvent it. The [PVIN] field in
tblPreliminaryV INs is a 17-char text field. Forms!frmVINODO !SerialNum
is just an unbound textbox on a form (frmVINODO).

I run the DLookup during the textbox's BeforeUpdate event code. Some
VIN values I type in there do NOT give rise to the error. Some do. By
nature, the VIN values are alphanumeric. Should I be wrapping the
Forms!frmVINODO !SerialNum in CStr( ) or something?

Not CStr, but you need to wrap it in quotes.

DLookup("[PVIN]", "tblPreliminary VINs", _
"[PVIN]=""" & Forms!frmVINODO !SerialNum & """")
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Jan 11 '06 #2

MLH

On Wed, 11 Jan 2006 19:39:40 GMT, "Randy Harris" <ra***@SpamFree .com>
wrote:

Not CStr, but you need to wrap it in quotes.

DLookup("[PVIN]", "tblPreliminary VINs", _
"[PVIN]=""" & Forms!frmVINODO !SerialNum & """")

Hmmm? Hadn't thought about the quotes. I got sidelined trying
to determine whether Forms!frmVINODO !SerialNum even has
a readable value in the BeforeUpdate event code. I dunno if
that is contributing to my problem.

Jan 11 '06 #3

MLH

>Not CStr, but you need to wrap it in quotes.

DLookup("[PVIN]", "tblPreliminary VINs", _
"[PVIN]=""" & Forms!frmVINODO !SerialNum & """")

Further testing proves that I get the error if I enter
JT4RN81A0M00777 77 into Forms!frmVINODO !SerialNum
#AND# there is a record in tblPreliminaryV INs with a [PVIN]
field value of JT4RN81A0M00777 77. But if there is NOT a
record in tblPreliminaryV INs when with a [PVIN] field value
of JT4RN81A0M00777 77the code line runs, no error is
triggered. That stumps me.

Jan 11 '06 #4

MLH

Not CStr, but you need to wrap it in quotes.

DLookup("[PVIN]", "tblPreliminary VINs", _
"[PVIN]=""" & Forms!frmVINODO !SerialNum & """")

I cut 'n pasted your code. Am getting the same error
telling me there's type mismatch. Really has me under
a barrel.

Jan 11 '06 #5

Randy Harris

"MLH" <CR**@NorthStat e.net> wrote in message
news:tu******** *************** *********@4ax.c om...

Not CStr, but you need to wrap it in quotes.

DLookup("[PVIN]", "tblPreliminary VINs", _
"[PVIN]=""" & Forms!frmVINODO !SerialNum & """")

Further testing proves that I get the error if I enter
JT4RN81A0M00777 77 into Forms!frmVINODO !SerialNum
#AND# there is a record in tblPreliminaryV INs with a [PVIN]
field value of JT4RN81A0M00777 77. But if there is NOT a
record in tblPreliminaryV INs when with a [PVIN] field value
of JT4RN81A0M00777 77the code line runs, no error is
triggered. That stumps me.

What are you doing with the DLookup returned value?

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Jan 11 '06 #6

MLH

On Wed, 11 Jan 2006 23:17:50 GMT, "Randy Harris" <ra***@SpamFree .com>
wrote:

What are you doing with the DLookup returned value?

Nothing really. Just trying to see if its already in the table to
prevent the user from inadvertently reprocessing the same VIN.
Here's the snippet. The first three 300's that are rem'd out all
failed in a similar fashion running the DLookUp.

'300: If DLookup("[PVIN]", "tblPreliminary VINs",
"[PVIN]=CStr(Forms!frm VINODOtesting!S erialNum)") Then 'Somehow,
JT4RN81A0M00777 77 causes a type mismatch in this line
'300: If DLookup("[PVIN]", "tblPreliminary VINs",
"[PVIN]=Forms!frmVINOD Otesting!Serial Num") Then 'Somehow,
JT4RN81A0M00777 77 causes a type mismatch in this line
'300: If DLookup("[PVIN]", "tblPreliminary VINs",
"[PVIN]='JT4RN81A0M007 7777'") Then 'Somehow, JT4RN81A0M00777 77
causes a type mismatch in this line
300: If DLookup("[PVIN]", "tblPreliminary VINs", "[PVIN]=""" &
Forms!frmVINODO testing!SerialN um & """") Then 'Randy Harris
suggestion off NG.
310: DoCmd.CancelEve nt
320: SendKeys "{Esc}"
330: MyString = "You have already begun processing on this
VIN. You are awaiting NC DMV faxed reply."
340: MsgBox MyString, 48, "Be Careful - " & MyApp$ & ", rev. "
& MY_VERSION$
350: Exit Sub
360: End If

Jan 11 '06 #7

Lyle Fairfield

IF must have something it can coerce into a Boolean. VBA can coerce a
Null into a Boolean. So IF has no problem when DLookup finds nothing
and returns a Null. But when DLookup finds something and returns a
string, IF does have a problem; VBA cannot coerce a string into a
Boolean (unless the string is recognizable as a Boolean, eg "True").

It is your IF that is causing the problem, not your DLookup.
Possibly
IF Nz(DLookup("[PVIN]", "tblPreliminary VINs", "[PVIN]='" &
Forms!frmVINODO testing!SerialN um & "'"), "") <> "" Then

or

IF Not IsNull(DLookup( "[PVIN]", "tblPreliminary VINs", "[PVIN]='" &
Forms!frmVINODO testing!SerialN um & "'")) Then

may help.

(My suggestions have not been tested and may have syntax errors.)

I may say that this is not to be construed I endorse the use of
DLookup. I do not.

Jan 12 '06 #8

Randy Harris

Oh boy... Examine the code you have there. In your If statement, you are
using the DLookup. If the DLookup finds one or more matching records it will
return the PVIN values for those records. So if there is a match, your
statement is effectively:

If "JT4RN81A0M0077 777" Then
execute some code
End If

The argument for your If needs to evaluate to either True or False. Yours
does neither. You could use

If Len(DLookup.... ) & "" > 0
or
If DLookup.... & "" <> ""

Those would each return a True or False value.

In this case, however, you probably should be using DCount to determine if
there is a matching record already in the table.

If DCount("[PVIN]", "tblPreliminary VINs", "[PVIN]=""" & _
Forms!frmVINODO testing!SerialN um & """") > 0 Then

BTW - I would strongly recommend that you get out of the habit of using
GetKeys. There is almost always a better alternative.

HTH
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
"MLH" <CR**@NorthStat e.net> wrote in message
news:is******** *************** *********@4ax.c om...

On Wed, 11 Jan 2006 23:17:50 GMT, "Randy Harris" <ra***@SpamFree .com>
wrote:
What are you doing with the DLookup returned value?

Nothing really. Just trying to see if its already in the table to
prevent the user from inadvertently reprocessing the same VIN.
Here's the snippet. The first three 300's that are rem'd out all
failed in a similar fashion running the DLookUp.

'300: If DLookup("[PVIN]", "tblPreliminary VINs",
"[PVIN]=CStr(Forms!frm VINODOtesting!S erialNum)") Then 'Somehow,
JT4RN81A0M00777 77 causes a type mismatch in this line
'300: If DLookup("[PVIN]", "tblPreliminary VINs",
"[PVIN]=Forms!frmVINOD Otesting!Serial Num") Then 'Somehow,
JT4RN81A0M00777 77 causes a type mismatch in this line
'300: If DLookup("[PVIN]", "tblPreliminary VINs",
"[PVIN]='JT4RN81A0M007 7777'") Then 'Somehow, JT4RN81A0M00777 77
causes a type mismatch in this line
300: If DLookup("[PVIN]", "tblPreliminary VINs", "[PVIN]=""" &
Forms!frmVINODO testing!SerialN um & """") Then 'Randy Harris
suggestion off NG.
310: DoCmd.CancelEve nt
320: SendKeys "{Esc}"
330: MyString = "You have already begun processing on this
VIN. You are awaiting NC DMV faxed reply."
340: MsgBox MyString, 48, "Be Careful - " & MyApp$ & ", rev. "
& MY_VERSION$
350: Exit Sub
360: End If

Jan 12 '06 #9

MLH

On Thu, 12 Jan 2006 00:18:07 GMT, "Randy Harris" <ra***@SpamFree .com>
wrote:

Oh boy... Examine the code you have there. In your If statement, you are
using the DLookup. If the DLookup finds one or more matching records it will
return the PVIN values for those records. So if there is a match, your
statement is effectively:

If "JT4RN81A0M0077 777" Then
execute some code
End If

The argument for your If needs to evaluate to either True or False. Yours
does neither. You could use

If Len(DLookup.... ) & "" > 0
or
If DLookup.... & "" <> ""

Those would each return a True or False value.

In this case, however, you probably should be using DCount to determine if
there is a matching record already in the table.

If DCount("[PVIN]", "tblPreliminary VINs", "[PVIN]=""" & _
Forms!frmVINODO testing!SerialN um & """") > 0 Then Sorry it took so long to get back with you. I was looking for my head.
After peeking into my butt, sure enough, there it was - the last place
I looked, of course. Thanks for setting me straight. Couldn't see the
forest for the trees, or in this case, the light for the sphincter.

BTW - I would strongly recommend that you get out of the habit of using
GetKeys. There is almost always a better alternative. Yeah, the sendkeys thing - I'm using to clear
Forms!frmVINODO testing!SerialN um. Seems
like an easy way. Its an unbound textbox. Whats
your best recommendation - I hate sendkeys too.

HTH

Jan 12 '06 #10