Solved VBA - Looking up a 'Auto Number' Primary key off a scanned variable.

[Snuggle Walrus]

Good evening all!

I hope this is ok to post, I've gotten a little stuck on my project. Basically, I'm setting up a barcode scanner for goods in and goods out project. I'm working on logging serial numbers for product allocation, so that when I scan a barcode it will run a few checks first just to make sure the product matches what's been scanned at goods in or worst case scenario if its never been booked in.

What I'm currently trying to do revolves around the goods out process where I want to allocate a product. All I'm doing is scanning the serial number and then running some VBA to see if that Serial already exists in the serial table. I have a variable called "SerialScan" that just takes the value of what the barcode scanner puts into the datasheet textbox this variant is stored as a String. Next up I have a Variant called "SerialIDScanSearch" (I know not the best naming convention). Now this is a Dlookup..

SerialScan = Me.SerialNumber_txt.value

SerialIDScanSearch = DLookup("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]=" & SerialScan)

However, I get a type mismatch ...
"Run-Time Error '3464: Data type mismatch in criteria expression"

Criteria in the expression so I've tried a few different things, I've checked my tables to see what serial number is being stored as its short text as can contain numbers and characters. However I cant seem to work out how to get around this error at all. Is it the datatype on my first variable SerialScan? I basically want to use SerialIDScanSearch to pull the primary key which is an auto number so I can use this elsewhere but also see if this returns a Null value so I have the option of adding a line into the serial number table as a last resort (this code is done and appears to be working just fine, but I cant get 'SerialIDScanSearch' to actually store a value at all as I keep getting the RunTime Error.

Any advice would be greatly appreciated, sorry if i missed anything important to assist with this error please let me know and I'll happily share it. Currently just looks like so...

Dim SerialScan As String
Dim SerialIDScanSearch As Variant
SerialScan = Me.SerialNumber_txt.value
SerialIDScanSearch = DLookup("[SerialID]", "[tbl_SerialNumbers]", "[Serial Number]=" & SerialScan)

SerialID in the table itself is an Autonumber (long) and Serial Number is stored as Short Text within the table.

Thankyou everyone have a great evening!

 

[theDBguy]

Hi. If the field and the variables are Strings/Text values, then you'll need to use a delimiter to avoid the type mismatch error. For example:

..."[Serial Number]='" & SerialScan & "'")

Hope that helps...

 

[Solo712]

Hi. If the field and the variables are Strings/Text values, then you'll need to use a delimiter to avoid the type mismatch error. For example:

..."[Serial Number]='" & SerialScan & "'")

Hope that helps...

It looks more like [Serial Number needs to be presented as a number i.e.

..."[Serial Number]=" & Clng(SerialScan))

Cheers,
Jiri

 

[Snuggle Walrus]

Thanks you two! I'm really sorry it took me a few days to reply was unwell so only just had the chance. I admit I feel very silly, my code directly below uses similar but for some reason I failed to apply this method on the Dlookup... Thanks very much! Have a great rest of your day!

 

[theDBguy]

Thanks you two! I'm really sorry it took me a few days to reply was unwell so only just had the chance. I admit I feel very silly, my code directly below uses similar but for some reason I failed to apply this method on the Dlookup... Thanks very much! Have a great rest of your day!

Hi. Glad to hear you got it sorted out and that you're feeling better now. Good luck with your project.