Find Record code question

Posted on 2014-03-11
Medium Priority
Last Modified: 2014-03-11
Hi Experts,
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!"
       Exit Sub
    End If
    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!"
end if
end sub

Question by:urjudo
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 39920635
<the "Search" is one of the field in "qryComputer" that I put as "Search: [TagID] & "," &  [HFSID]" >

i will use two fields in the query  >  [TagID],  [HFSID]
and remove the concatenated Search column
and use these codes

if me.findField & ""="" then
     msgbox "enter item to be search"
    exit sub
end if

with me.recordsetclone
        .findfirst "[TagID]='" & me.findField & "'"
        if not .nomatch then
                exit sub      'exit the sub if the record was found
        end if
end with

with me.recordsetclone
        .findfirst "[HFSID]='" & me.findField & "'"
        if not .nomatch then
                exit sub      'exit the sub if the record was found
        end if
end with

'if record was not found on either fields
  msgbox "Sorry record not found

Author Comment

ID: 39920686
Hi Rey Obrero,
The code works what I need, but one more question, where I place the msgbox for no record found?

Author Comment

ID: 39920781
Hi Rey Obrero,
I got it for the no record found message.  Thank you so much for your help!!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question