Coding question

Hi Experts,
I have a access coding question for search problem.  currently I have a form for user to search, on the table, I have two different fields, one is called "TagNo" and other called "old TagID".  The "TagNo" fields has all the Numbers, for ex: cs00001, cs00002 etc, but the "old TagID" is the old TagNo that the TagNo already replaced the old one, for example, the TagNo is cs00534 but the oldTagID is c55810, what I want to do is user able can search both TagNo or OldTagID, but it seems m coding only abe to search for the TagNo not the OldTagID.  The TagNo is primary key but the OldTagID is not so most of the OldTagID is empty.  Below is my coding for the search on click.  Thanks,

Private Sub cmdSearchTagNo_Click()
    Dim TagNoRef As String
    Dim strSearchTagNo As String
   
'Check txtSearch for Null value or Null Entry first.

    If IsNull(Me![txtSearchTagNo]) Or (Me![txtSearchTagNo]) = "" Then
        MsgBox "Please Enter a Tag#!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearchTagNo].SetFocus
    Exit Sub
    End If
'---------------------------------------------------------------
 
'Performs the search using value entered into txtSearch
'and evaluates this against values in BoxNo
       
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("TagNo")
    DoCmd.FindRecord Me!txtSearchTagNo
       
    TagNo.SetFocus
    TagNoRef = TagNo.Text
    txtSearchTagNo.SetFocus
    strSearchTagNo = txtSearchTagNo.Text
   
   
   'If matching record found sets focus in BoxtNo and shows msgbox
'and clears search control

    If TagNoRef = strSearchTagNo Then
       TagNo.SetFocus
        txtSearchTagNo = ""
       
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
           MsgBox "Sorry, Tag#: " & "" & strSearchTagNo & " is NOT found.", _
            , "Invalid Search Criterion!"
            txtSearchTagNo.SetFocus
    End If
End Sub
urjudoAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
If it is linked to SQL Server then I wouldn't use a filter.  I would add criteria to the select clause.

Change the RecordSource query of the form to:

Where TagNo = Forms!yourform!FindTagNo OR oldTagNo = Forms!yourform!FindTagNo

Then in the click event of your "search" button requery the form.

Me.Requery

Once you do this, the form will open empty since the FindTagNo control will be empty.  After the user enters a value and presses "search", the record will be found or not.  You could check after the requery to see if the TagNo is populated and if it is not, display an error message.
0
 
PatHartmanCommented:
1. You would only use the .text property if you were to write code for the Change event to analyze each keystroke as it happens.  In all other cases you would use the .Value property which is the default and which is always available so you don't need to set focus to a control before referencing it.  So:
Me.TagNoRef = Me.TagNo
Me.strSearchTagNo = Me.txtSearchTagNo

Open in new window

2. Use the Me. or Me! qualifier for fields in a recordset and controls on a form.  This tells Access immediately where to find the definition of the variable you are referencing.  Additionally, the Me. syntax will give you intellisense so you get help as you type.

For this purpose, you can use the form's Filter property (I don't ever do this since it is not appropriate in apps linked to SQL Server but as long as your app is linked to Jet/ACE, it will be fine)

Dim sFilter as String
sFilter = "[TagNo] = " & Me.TagNo & " OR [oldTagNo] = " & Me.TagNo
Me.Filter = sFilter
Me.FilterOn = True

Open in new window

0
 
urjudoAuthor Commented:
my app is link to SQL server
0
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.

 
urjudoAuthor Commented:
I put all the coding on the Search Button and I don't put anything in my query.
0
 
PatHartmanCommented:
Is that a statement or a question?  My suggestion included modifying the query.
0
 
urjudoAuthor Commented:
my form is a single form with show the first record, if I modify my query then the form will be show the empty record, that't why I have the coding on the search button.
attach is a screen shot of my form
Doc5.docx
0
 
PatHartmanCommented:
And my directions tell you how to make that happen efficiently.
0
 
urjudoAuthor Commented:
it's still not working, I used your suggestion but only works when I search for the TagNo, if I search to search the oldTagID, this is not working, I knew it must be the coding but just don't how to put them together.

I knew there is a problem on
  DoCmd.GoToControl ("TagNo")
and
TagNo.setfocus

because I have TagNo and OldTagID two different fields, so if I put   DoCmd.GoToControl ("TagNo")  then the oldTagID would never can be search.
0
 
PatHartmanCommented:
There is NO reason to GoTo any control.  The ONLY reason your original code needed that was because you were using the .text property.  You WILL NOT have the problem if you use the .value property.

Go back and look at my suggested code again?  Do you see any .SetFocus or GoTo commands or use of the .text property?

I looked at your screen shot again.  You only have one search box in the header.  Where is the other one?  You CANNOT use bound fields for searching.  When you type into a bound field, you are changing the record.
0
 
urjudoAuthor Commented:
on my screen shot, the search box is an unbound field.  so you mean I can not user one unbound search box to search two different fields?  I was thinking when user enter a value , if this value is match either TagNo or OldTagID then show it (there is no possible for the same value in both TagNo and oldTagID, but there can be TagNo and OldTanID in the same record.
0
 
PatHartmanCommented:
I'm sorry, I lost my mind with all the GoTo control stuff.  Yes.  You can use a single text box and use it to search multiple columns.  Please post the code that isn't working and tell us what is happenings.
0
 
urjudoAuthor Commented:
The codng I past with my question is working perfectly if I just search for the TagNo, but it's not working for search OldTagNo.

Private Sub cmdSearchTagNo_Click()
    Dim TagNoRef As String
    Dim strSearchTagNo As String
   
'Check txtSearch for Null value or Null Entry first.

    If IsNull(Me![txtSearchTagNo]) Or (Me![txtSearchTagNo]) = "" Then
        MsgBox "Please Enter a Tag#!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearchTagNo].SetFocus
    Exit Sub
    End If
'---------------------------------------------------------------
 
'Performs the search using value entered into txtSearch
'and evaluates this against values in BoxNo
       
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("TagNo")
    DoCmd.FindRecord Me!txtSearchTagNo
       
    TagNo.SetFocus
    TagNoRef = TagNo.Text
    txtSearchTagNo.SetFocus
    strSearchTagNo = txtSearchTagNo.Text
   
   
   'If matching record found sets focus in BoxtNo and shows msgbox
'and clears search control

    If TagNoRef = strSearchTagNo Then
       TagNo.SetFocus
        txtSearchTagNo = ""
       
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
           MsgBox "Sorry, Tag#: " & "" & strSearchTagNo & " is NOT found.", _
            , "Invalid Search Criterion!"
            txtSearchTagNo.SetFocus
    End If
End Sub
0
 
PatHartmanCommented:
I guess you didn't listen to anything I said.  What exactly was wrong with the four lines of code I suggested?  I know you are linked to SQL Server and so I suggested a better method for use with ODBC data sources which you didn't understand or didn't want to use.  But, the filter code I suggested will work as well as the FindRecord you are attempting.  They are BOTH equally inefficient for use with SQL Server.

I'll be a little more explicit - DO NOT USE the .text property UNLESS you have to put code into the on Change event.  Is that statement clear?  Since this code would not go in the on Change event, you should NOT be using the .text property.  Once you stop using the .text property, you can get rid of all the .SetFocus and GoTo control code.  Using the .text property REQUIRES that the focus be in the control you are referencing.  Using the .value property does not.  Some people who have used VB in the past are confused by this since in VB the .text property is the default.  It's not my fault.  I didn't write VBA.  I didn't make it different but it is.  In VBA, the .value property is the default and as such can be omitted so Me.SomeField and Me.SomeField.Value are the same except the latter takes more typing.  Using Me.SomeField.Text REQUIRES that you first use Me.SomeField.SetFocus to put the focus in the control since only when focus is in the control can you reference its .Text property.
0
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.

All Courses

From novice to tech pro — start learning today.