Solved

Coding question

Posted on 2014-01-17
13
219 Views
Last Modified: 2014-02-21
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
0
Comment
Question by:urjudo
  • 7
  • 6
13 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:urjudo
Comment Utility
my app is link to SQL server
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
 

Author Comment

by:urjudo
Comment Utility
I put all the coding on the Search Button and I don't put anything in my query.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Is that a statement or a question?  My suggestion included modifying the query.
0
 

Author Comment

by:urjudo
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
And my directions tell you how to make that happen efficiently.
0
 

Author Comment

by:urjudo
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:urjudo
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 

Author Comment

by:urjudo
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

14 Experts available now in Live!

Get 1:1 Help Now