Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Display records based on combo Box

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.
0
skull52
Asked:
skull52
  • 9
  • 5
2 Solutions
 
Surone1Commented:
Private Sub comboboxeBox_AfterUpdate()

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

end sub
0
 
Surone1Commented:
do make sure the combobox's event handler is on for afterupdate in the properties
0
 
Surone1Commented:
Private Sub combobox_AfterUpdate()

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

end sub
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Surone1Commented:
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
0
 
skull52Author 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
0
 
Surone1Commented:
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
0
 
skull52Author Commented:
The CUSTNMBR field is populated and is part of the table that populates the form.
0
 
Surone1Commented:
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
0
 
Surone1Commented:
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
0
 
skull52Author Commented:
Would I put that code on the  on Click () event property?
0
 
Surone1Commented:
afterupdate, on click might work too
0
 
skull52Author 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?
0
 
Surone1Commented:
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..
0
 
skull52Author 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
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now