Solved

VBA Access question - Best method to search

Posted on 2013-06-12
2
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 85

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

622 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