Solved

Find Record code question

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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