Solved

Find Record code question

Posted on 2014-03-11
3
447 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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now