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? 11 2184
"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.
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.
>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. 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.
"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.
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
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.
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
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
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. | |