Find Record code question
Posted on 2014-03-11
I have a form for users to look up a computer inventory, the primary key is the TagID which is the barcode number, bu we also have a second ID which is the old barcode#, some time we have the TageID to replace the old barcode# (we called HFSID), but some time , user might only have the HFSID not the Tag ID, I would like users able to be enter either ID on the "Find Field" to search and show them the result. For example, I have a Tag# is "cs00534" also this record has a HFSID is "!C55810" so if user enter either of these number, the record should be showing..
On the form I show both TagID and HFS ID, I have a unbound text called "FindField" and "Find"Button, below is the code on click on the "Find" Button, but I got the error message :Run - time error "94", invalid use of null
the "Search" is one of the field in "qryComputer" that I put as "Search: [TagID] & "," & [HFSID]"
Dim db As Database
Dim rs As Recordset
Dim Criteria As String
Dim X As Integer
Dim strSearch As Variant
strSearch = Me.FindField
strSearch = DLookup("Search", "qryComputer", "Search = '" & strSearch & "'")
Tag = DLookup("Search", "qryComputer", "Search = '" & strSearch & "'")
If Me.FindField = "" Then
MsgBox "Please enter a value.", vbOKOnly, "Invalid Search Criterion!"
Set db = CurrentDb
stCriteria = "Select * from qryComputer Where Search = '" & Me.Tag & " '"
Set rs = db.OpenRecordset(stCriteria, dbOpenDynaset, dbSeeChanges)
If rs.EOF Then
MsgBox "Tag#/HFS# " & "" & Me.FindField & " is NOT found.", vbOKOnly, "Invalid Search Criterion!"
MsgBox "Tag#/HFS# " & "" & rs!Search & " is found.", vbOKOnly, "Invalid Search Criterion!"