Solved

VBA Access question - Best method to search

Posted on 2013-06-12
2
523 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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
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…

867 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

15 Experts available now in Live!

Get 1:1 Help Now