Solved

VBA Access question - Best method to search

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 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