Solved

VBA Access question - Best method to search

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Filter cbo on datasheet - 11 30
How Do I Select a Record using a Primary Key 3 27
Access query expression 6 19
Access Changing Number to Date with Seperator 5 20
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

815 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

12 Experts available now in Live!

Get 1:1 Help Now