Solved

Find Record code question

Posted on 2014-03-11
3
453 Views
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!"
       Me.FindField.SetFocus
       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!"
       
    Else
       MsgBox "Tag#/HFS# " & "" & rs!Search & " is found.", vbOKOnly, "Invalid Search Criterion!"
end if
rs.close
end sub

Thanks
0
Comment
Question by:urjudo
  • 2
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 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
                me.bookmark=.bookmark
                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
                me.bookmark=.bookmark
                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
0
 

Author Comment

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

Author Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

789 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