By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 920 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]=Forms!frmVINODO!SerialNum")

P: n/a
MLH
DLookup("[PVIN]", "tblPreliminaryVINs",
"[PVIN]=Forms!frmVINODO!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
tblPreliminaryVINs 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
Share this Question
Share on Google+
11 Replies


P: n/a

"MLH" <CR**@NorthState.net> wrote in message
news:6q********************************@4ax.com...
DLookup("[PVIN]", "tblPreliminaryVINs",
"[PVIN]=Forms!frmVINODO!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
tblPreliminaryVINs 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]", "tblPreliminaryVINs", _
"[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

P: n/a
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]", "tblPreliminaryVINs", _
"[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

P: n/a
MLH
>Not CStr, but you need to wrap it in quotes.

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


Further testing proves that I get the error if I enter
JT4RN81A0M0077777 into Forms!frmVINODO!SerialNum
#AND# there is a record in tblPreliminaryVINs with a [PVIN]
field value of JT4RN81A0M0077777. But if there is NOT a
record in tblPreliminaryVINs when with a [PVIN] field value
of JT4RN81A0M0077777the code line runs, no error is
triggered. That stumps me.
Jan 11 '06 #4

P: n/a
MLH
Not CStr, but you need to wrap it in quotes.

DLookup("[PVIN]", "tblPreliminaryVINs", _
"[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

P: n/a

"MLH" <CR**@NorthState.net> wrote in message
news:tu********************************@4ax.com...
Not CStr, but you need to wrap it in quotes.

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


Further testing proves that I get the error if I enter
JT4RN81A0M0077777 into Forms!frmVINODO!SerialNum
#AND# there is a record in tblPreliminaryVINs with a [PVIN]
field value of JT4RN81A0M0077777. But if there is NOT a
record in tblPreliminaryVINs when with a [PVIN] field value
of JT4RN81A0M0077777the 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

P: n/a
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]", "tblPreliminaryVINs",
"[PVIN]=CStr(Forms!frmVINODOtesting!SerialNum)") Then 'Somehow,
JT4RN81A0M0077777 causes a type mismatch in this line
'300: If DLookup("[PVIN]", "tblPreliminaryVINs",
"[PVIN]=Forms!frmVINODOtesting!SerialNum") Then 'Somehow,
JT4RN81A0M0077777 causes a type mismatch in this line
'300: If DLookup("[PVIN]", "tblPreliminaryVINs",
"[PVIN]='JT4RN81A0M0077777'") Then 'Somehow, JT4RN81A0M0077777
causes a type mismatch in this line
300: If DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]=""" &
Forms!frmVINODOtesting!SerialNum & """") Then 'Randy Harris
suggestion off NG.
310: DoCmd.CancelEvent
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

P: n/a
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]", "tblPreliminaryVINs", "[PVIN]='" &
Forms!frmVINODOtesting!SerialNum & "'"), "") <> "" Then

or

IF Not IsNull(DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]='" &
Forms!frmVINODOtesting!SerialNum & "'")) 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

P: n/a
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 "JT4RN81A0M0077777" 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]", "tblPreliminaryVINs", "[PVIN]=""" & _
Forms!frmVINODOtesting!SerialNum & """") > 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**@NorthState.net> wrote in message
news:is********************************@4ax.com...
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]", "tblPreliminaryVINs",
"[PVIN]=CStr(Forms!frmVINODOtesting!SerialNum)") Then 'Somehow,
JT4RN81A0M0077777 causes a type mismatch in this line
'300: If DLookup("[PVIN]", "tblPreliminaryVINs",
"[PVIN]=Forms!frmVINODOtesting!SerialNum") Then 'Somehow,
JT4RN81A0M0077777 causes a type mismatch in this line
'300: If DLookup("[PVIN]", "tblPreliminaryVINs",
"[PVIN]='JT4RN81A0M0077777'") Then 'Somehow, JT4RN81A0M0077777
causes a type mismatch in this line
300: If DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]=""" &
Forms!frmVINODOtesting!SerialNum & """") Then 'Randy Harris
suggestion off NG.
310: DoCmd.CancelEvent
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

P: n/a
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 "JT4RN81A0M0077777" 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]", "tblPreliminaryVINs", "[PVIN]=""" & _
Forms!frmVINODOtesting!SerialNum & """") > 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!frmVINODOtesting!SerialNum. Seems
like an easy way. Its an unbound textbox. Whats
your best recommendation - I hate sendkeys too.

HTH


Jan 12 '06 #10

P: n/a
MLH
On 11 Jan 2006 16:09:43 -0800, "Lyle Fairfield"
<ly***********@aim.com> wrote:
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]", "tblPreliminaryVINs", "[PVIN]='" &
Forms!frmVINODOtesting!SerialNum & "'"), "") <> "" Then

or

IF Not IsNull(DLookup("[PVIN]", "tblPreliminaryVINs", "[PVIN]='" &
Forms!frmVINODOtesting!SerialNum & "'")) Then

<snip>
Both good suggestions. Dunno what was wrong with me, but I
had a bad case of it. Thx for setting me straight.
Jan 12 '06 #11

P: n/a
MLH <CR**@NorthState.net> wrote in
news:7d********************************@4ax.com:
On Thu, 12 Jan 2006 00:18:07 GMT, "Randy Harris"
<ra***@SpamFree.com> wrote:
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!frmVINODOtesting!SerialNum. Seems
like an easy way. Its an unbound textbox. Whats
your best recommendation - I hate sendkeys too.

me.undo

--
Bob Quintal

PA is y I've altered my email address.
Jan 12 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.