DLookup

[MikeS]

When I try to run the following code:

Private Sub ScanSerial_AfterUpdate()
Dim SerialLookup As Variant
SerialLookup = DLookup("[Serial]", "TestData2", "[Serial]= " &
Me.ScanSerial.Text)

If SerialLookup = Empty Then
MsgBox "Serial Number does not exist...would you like to add it?",
vbYesNo
Else
End If

End Sub

I get the following error:

"You cancelled the previous operation"

I have had this issue before and it is usually because of unmatched data
type. However, I have tried changing the data type to everything I can think
of and still can not get it to work.

Can anyone help with this?

Thanks,
MikeS

 

[kc-mass]

Hi Mike

Remove the bracketts from "[Serial]". Make it "Serial"

Regards

Kevin

 

[MikeS]

thanks for the input, but that still doesn't work

any other suggestions?

Hi Mike

Remove the bracketts from "[Serial]". Make it "Serial"

Regards

Kevin

When I try to run the following code:

Private Sub ScanSerial_AfterUpdate()
Dim SerialLookup As Variant
SerialLookup = DLookup("[Serial]", "TestData2", "[Serial]= " &
Me.ScanSerial.Text)

If SerialLookup = Empty Then
MsgBox "Serial Number does not exist...would you like to add it?",
vbYesNo
Else
End If

End Sub

I get the following error:

"You cancelled the previous operation"

I have had this issue before and it is usually because of unmatched data
type. However, I have tried changing the data type to everything I can
think
of and still can not get it to work.

Can anyone help with this?

Thanks,
MikeS

 

[CraigH]

If Serial is Text then you need the single Quote around the ScanSerial

SerialLookup = DLookup("[Serial]", "TestData2", "[Serial]= ' " &
Me.ScanSerial.Text & " ' ")

 

[MikeS]

thanks for the input, but it still doesn't work

any other suggestions?

 

[Dale Fye]

Agree with both of the previous posts, and have a couple of other issues
1. Don't wrap the field name in [] inside a dlookup.
2. If ScanSerial is a text value, it must be wrapped in either single or
double quotes inside the DLOOKUP.
3. Personally, I only use the Text property of a control in the Change
event of the control, otherwise, I just refer to the value of the control
(with or without the .Value)
4. SerialLookup will not be Empty, it will be NULL if the DLOOKUP doesn't
find a value.
5. Not really sure what you are trying to do in the DLOOKUP. It looks like
you are trying to return the value of the [Serial] field in table
[TestData2], where the value in the [Serial] field matches the value of the
control "ScanSerial" on your form. But that makes no sense at all. So my
guess is that you need to change the field name of one of the fields in the
DLookup function.

Try something like:

Private Sub ScanSerial_AfterUpdate()

Dim SerialLookup As Variant
SerialLookup = DLookup("Serial", "TestData2", "Serial= '" &
Me.ScanSerial & "'")

If ISNULL(SerialLookup) Then
MsgBox "Serial Number does not exist...would you like to add it?",
vbYesNo
'I am sure you had more code here
Else
'what are you doing if it finds the serial
End If

End Sub

 

[JimBurke via AccessMonster.com]

I see multiple problems in your code. First, if DLookup doesn't return a
value you'll get an error. You should use the Nz function. Also, you have the
wrong format for your msgbox. And you don't want to use the text property for
ScanSerial. And as somone mentioned, if ScanSerial isn't numeric, you need to
surround the value with quotes. You want something like this (assuming the
value is numeric):

SerialLookup = nz(DLookup("[Serial]", "TestData2", "[Serial]= " & ScanSerial),
0)
if SerialLookup = 0 then
if MsgBox("Serial Number does not exist...would you like to add it?",
vbYesNo) = vbYes then
whatever you do if they answer yes...
else
whatever you do if No
End If
Enf If

I used 0 in Nz and in the 'If' - if the Serial field isn't numeric you'd want
to use vbNullString in place of 0. And if the ScanSerial field is not numeric
you need to change that statement to

SerialLookup = nz(DLookup("[Serial]", "TestData2", "[Serial]= '" & ScanSerial
& "'"),0)

That's a single quote after [Serial = and a single quote inside double quotes
at the end of the DLookup. This assumes you don't have to worry about
ScanSerial eer having a single quote in the value.

If SerialLookup = Empty Then
MsgBox "Serial Number does not exist...would you like to add it?",
vbYesNo
Else
End If

When I try to run the following code:

Private Sub ScanSerial_AfterUpdate()
Dim SerialLookup As Variant
SerialLookup = DLookup("[Serial]", "TestData2", "[Serial]= " &
Me.ScanSerial.Text)

If SerialLookup = Empty Then
MsgBox "Serial Number does not exist...would you like to add it?",
vbYesNo
Else
End If

End Sub

I get the following error:

"You cancelled the previous operation"

I have had this issue before and it is usually because of unmatched data
type. However, I have tried changing the data type to everything I can think
of and still can not get it to work.

Can anyone help with this?

Thanks,
MikeS

 

[MikeS]

Craig...that almost works. I get a value in my variable, however, now I am
getting the error "Invalid use of Null"

any more suggestions?

If Serial is Text then you need the single Quote around the ScanSerial

SerialLookup = DLookup("[Serial]", "TestData2", "[Serial]= ' " &
Me.ScanSerial.Text & " ' ")

When I try to run the following code:

Private Sub ScanSerial_AfterUpdate()
Dim SerialLookup As Variant
SerialLookup = DLookup("[Serial]", "TestData2", "[Serial]= " &
Me.ScanSerial.Text)

If SerialLookup = Empty Then
MsgBox "Serial Number does not exist...would you like to add it?",
vbYesNo
Else
End If

End Sub

I get the following error:

"You cancelled the previous operation"

I have had this issue before and it is usually because of unmatched data
type. However, I have tried changing the data type to everything I can think
of and still can not get it to work.

Can anyone help with this?

Thanks,
MikeS

 

[fredg]

When I try to run the following code:

Private Sub ScanSerial_AfterUpdate()
Dim SerialLookup As Variant
SerialLookup = DLookup("[Serial]", "TestData2", "[Serial]= " &
Me.ScanSerial.Text)

If SerialLookup = Empty Then
MsgBox "Serial Number does not exist...would you like to add it?",
vbYesNo
Else
End If

End Sub

I get the following error:

"You cancelled the previous operation"

I have had this issue before and it is usually because of unmatched data
type. However, I have tried changing the data type to everything I can think
of and still can not get it to work.

Can anyone help with this?

Thanks,
MikeS

In Access, it's the control's Value property you have to use, not it's
Text property.
A Control has a Text property as you are entering the data. As soon as
you save the data (when exiting the control) it becomes the Value
property and the Text property no longer accessible unless that
control has the focus.
Also, since the Value property is the control's default property, you
do not have to actually write it.

If [Serial] is a Number datatype, then, the syntax would be:

SerialLookup = DLookup("[Serial]", "TestData2", "[Serial]= " &
Me.ScanSerial)

However, if [Serial] is a Text datatype then use:
SerialLookup = DLookup("[Serial]", "TestData2", "[Serial]= '" &
Me.ScanSerial & "'")

Actually, your DLookUp doesn't make sense.
You are DLooking up the field named [Serial] when you already know
(from the where clause in the DLookUp) that [Serial] = Me.ScanSerial

All you need here is
SerialLookUp = Me.ScanSerial
Perhaps you miss-wrote one of the field names.