Solved

Find Record code question

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

770 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