Re: search by 1 of 3 combos

Simplifying Search Comboboxes
---

You have made this more difficult than it needs to be... if you set the bound column to be the record's primary key, you can use the same code for each of your search combos.


Make one or more unbound combos on your form. Let the first column be invisible and be the primary key ID of the recordsource of your form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

~~~~~~~~~

if you want to be able to do wildcard searches on string controls -- presumeably, you will want to set a filter, not find records. One thing I like to do is have a listbox (or combo)for records matching the search criteria -- this is where the FindRecord code would run.

As criteria is selected, the SQL for the search list is rewritten so the user only sees the records to pick that match what they have specified.

In the same way that FindRecord is written for any control, you would have one procedure to rewrite the search list SQL. If you want more information, set up your search combo (just list everything for now) and post the SQL -- we can help you with code to construct the SQL based on criteria.


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



miss031 wrote:
I don't know what I am missing here. I have a form for viewing and editing contact information, and I would like the user to be able to search by either bid number (contact ID), phone number or name, then have the record details displayed on the form.

I have 3 combo boxes, one for each search criteria, and the code for them is based on a working search combo I have on another form, but they will not work on this form. I'msure it is in the syntax, but I think I've made such a mess now that I don't know where to start.


bidder_bidnumber is Text
contactprimphone is Integer
contactlastname is Text


source code:

Option Compare Database
Option Explicit

Private Sub cbo_find_by_bid_number_AfterUpdate()
Dim content
content = Trim(cbo_find_by_bid_number) & "*"
content = "[bidder_bidnumber] like '" & content & "'"
If cbo_find_by_bid_number <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and purchases on the form
End If
End Sub

Private Sub cbo_find_by_bid_number_GotFocus()
Me.cbo_find_by_bid_number.Value = ""
End Sub

Private Sub cbo_find_by_name_AfterUpdate()
Dim content
content = Trim(cbo_find_by_name) & "*"
content = "contactlastname like '" & content & "'"
If cbo_find_by_name <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and purchases on the form
End If
End Sub

Private Sub cbo_find_by_name_GotFocus()
Me.cbo_find_by_name.Value = ""

End Sub

Private Sub cbo_find_by_phone_AfterUpdate() ' "Find by Phone #" combo box
Dim content
content = Trim(cbo_find_by_phone) & "*"
content = "[contactprimphone] like '" & content & "'"
If cbo_find_by_phone <> "" Then
Me.Recordset.FindFirst content
' finds a particular bidder, and displays their information and purchases on the form
End If
End Sub

Private Sub cbo_find_by_phone_GotFocus()
Me.cbo_find_by_phone.Value = ""

End Sub
.

Relevant Pages

  • Re: Simple Insert Into...
    ... it is in a control on the form; and is unbound (it's actually a combo ... box that is used as a dropdown list, values taken from a different table; SQL ... I need this value to assign a unique ID to the entire recordset being ... In your form's Sub, you'll wind up with something like the following: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Simple Insert Into...
    ... You can store unbound control data in a variable, ... in the sql. ... Dim strSQL As String ... In your form's Sub, you'll wind up with something like the following: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Make Table Query
    ... Sub GetExcelFiles(strAccess as string, strDir as string) ... ' strDir - name of the directory to get Excel files. ... Looking at your reply I think I can insert the SQL piece into code ...
    (microsoft.public.access.queries)
  • Re: Requery and stay on current record
    ... matches the data type of your primary key. ... If StudentID is an integer, ... KeyCurrent As Integer"; if StudentID is a string, ... Private Sub Command25_Click ...
    (microsoft.public.access.formscoding)
  • RE: Primary Keys w/ Multiple Columns: Dual Combo Boxes
    ... Make it the primary key. ... Keycode and Promo fields, make them required fields, and don't allow NULL's. ... Private Sub cboKeyCode_BeforeUpdate ... On Error GoTo ErrHandler ...
    (microsoft.public.access.formscoding)
Loading