DLookup

  • Thread starter Thread starter MikeS
  • Start date Start date
M

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
 
thanks for the input, but that still doesn't work

any other suggestions?

kc-mass said:
Hi Mike

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

Regards

Kevin


MikeS said:
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
Click to expand...
Click to expand...
 
If Serial is Text then you need the single Quote around the ScanSerial

SerialLookup = DLookup("[Serial]", "TestData2", "[Serial]= ' " &
Me.ScanSerial.Text & " ' ")
 
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
 
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?

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

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

MikeS said:
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
Click to expand...
Click to expand...
 
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
Click to expand...

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.
 
Back
Top