We help IT Professionals succeed at work.

Display records based on combo Box

skull52
skull52 asked
on
Medium Priority
259 Views
Last Modified: 2012-05-06
I have a table that consists of CUSTNMBR, Name, Address, Etc and a simple form based on the table. I would like is to have the records on the form displayed by selecting CUSTNMBR from a combo box. This should be simple but the solution eludes me. Any assistance would be greatly appreciated.
Comment
Watch Question

Commented:
Private Sub comboboxeBox_AfterUpdate()

me.filter = "CUSTNMBR = " & combo.text

end sub

Commented:
do make sure the combobox's event handler is on for afterupdate in the properties

Commented:
Private Sub combobox_AfterUpdate()

me.filter = "CUSTNMBR = " & combobox.text

end sub

Commented:
not sure if that is what you want, since if the combo is bound you would need to change it to unbound and add a procedure to fill it
skull52IT director

Author

Commented:
I want to be able to click the dropdown on the combo box, select the customer number and go the record associated with that customer number
Commented:
try:
   Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CUSTNMBR] = " combobox.text
    If Not rs.EOF Then
        Me.Bookmark = rs.Bookmark
     End If
set rs = nothing

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
skull52IT director

Author

Commented:
The CUSTNMBR field is populated and is part of the table that populates the form.

Commented:
if the CUSTNMBR is a textfield it's
  Set rs = Me.Recordset.Clone
    rs.FindFirst "[CUSTNMBR] = '" & combobox.text & "'"
    If Not rs.EOF Then
        Me.Bookmark = rs.Bookmark
     End If
set rs = nothing

Commented:
if numeric
   Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CUSTNMBR] = " & combobox.text
    If Not rs.EOF Then
        Me.Bookmark = rs.Bookmark
     End If
set rs = nothing
skull52IT director

Author

Commented:
Would I put that code on the  on Click () event property?

Commented:
afterupdate, on click might work too
skull52IT director

Author

Commented:
I think I am missing something, the CUSTNMBR is a text field and currently a text box on the form, would I convert that field to a Combo Box, or create a completely new Combo Box and if so what would be the best way to populate it?

Commented:
the easiest way is drag a new combobox on the form and use the wizard i guess.. after that you can modify properties/code to your liking..
IT director
Commented:
Thanks for all your Help but I found a Solution that works for what I needed.

Private Sub cmdSearch_Click()
    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
     
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
       
    Else
   
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
       
        'Filter frmCustomers based on search criteria
        Form_frmCustomers.RecordSource = "select * from Customers where " & GCriteria
        Form_frmCustomers.Caption = "Customers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
       
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"
       
        MsgBox "Results have been filtered."
       
    End If
   
End Sub
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.