Improve company productivity with a Business Account.Sign Up

x
?
Solved

VBA Access question - Best method to search

Posted on 2013-06-12
2
Medium Priority
?
540 Views
Last Modified: 2013-06-13
Not all clients will  have an system Id associated to their name. System Id is an generated number based off an external web database. User entered the system Id is person is in web database.  I'm working with bound forms.

I have a field that finds all client information based off system ID using following code below:
Dim rs As DAO.Recordset

    If Not IsNull(Me.cbotest) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "[strClientId] = '" & Me.cbotest & "'"
        If rs.NoMatch Then
            MsgBox "Client Not found: filtered?"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If

What would be the best method to search for a client? Drill down search based off Last name to first name?  One combo box that contains full Name? Database is more of a side work project, so I'm not a full developer. Looking for best ideas or approaches.

Thanks
0
Comment
Question by:jbakestull
2 Comments
 
LVL 86

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39244020
I don't know that there is a single "best" method. Your search functions should match the application, and the needs of the application. That's vague, but then there are countless ways a database can be designed and used.

For example, if I have an Invoicing database I'd provide search functionality for Invoices by Date Range, Customer, Shipper, Part Number, and so forth. If I have a Customer Relations database I provide searches by Customer, Email, Phone, Company, and so forth.

The method for searches is normally the same. I build a form where the user can enter one or more of the criteria, and then build a display of their results. For example, in my Invoicing app I might provide the user with two Textboxes for Date Range, a Textbox for Customer, Shipper, etc etc. I'd then build a search routine something like this:

Function Search() As Boolean

Dim sWhere As string

If Nz(Me.txCustomer, "") <> "" Then
  '/ user entered something in Customer
  sWhere = sWhere & " Customer LIKE '*" & Me.txCustomer & "*'"
End If

If Nz(Me.txDateStart, "") <> "" Then
  If Len(sWhere)>0 Then sWhere = sWhere & " AND "
  sWhere = sWhere & "InvoiceDate >#" & Me.txDateStart & "#"
End If

'/ and continue on for all my search boxes
'/ Listbox named lstSearch, with the Columns, ColumnCount, ColumnWidth etc set
Me.lstSearch.RowSource = "SELECT Col1, Col2, Col3 FROM SomeQuery WHERE "& sWhere
End Function

Open in new window


I'd then call that function from every box where I need to search. For example, in txCustomer I'd do this:

Sub txCustomer_AfterUpdate()
  Search
End Sub

Of course you could use a Subform instead of the Listbox if that makes more sense.
0
 

Author Closing Comment

by:jbakestull
ID: 39244971
thanks for your advice
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

607 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