Discussion Locked

Access DB - Query on Value of Combo Box

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a form with a combo box and I would like to open another form with only the records that match the selection in the combo box. What is the best way to do this?
Additional Info: I have a main table with all fields and then a 2nd table with the 16 different names that might appear in that field on the main form. From the 2nd table, I created the form with the combo box.

3 replies

by alysondunsford

try using an sql statement querying the value in the combo box and then open the form based on that query! You can either use a standard access query combined with a macro or you can write the sql statement in vba and the command to open the relevant form

by akwok

Try this. Add / Change code to suit your situation

AfterUpdate event handle of 1stForm
Sub combobox_afterupdate()
'do validity test
op=combobox
docmd.close
docmd.openform "2ndForm",,,,,,op
End Sub
------
Form Open event of 2ndForm
sub Form_Open ()
op=Me.OpenArgs
If Not IsNull(op) Then
Dim r As DAO.Recordset
Set r = Me.RecordsetClone
r.FindFirst "ThatField='" & op & "'"
If Not r.NoMatch Then
Me.Bookmark = r.Bookmark
End If
End If
End Sub

by haris

Access provides you the facility to open subforms for records matching to particular fields in the main form. Click on add subform button on the tool box. the wizard will guide you through the process of creating the sub-form. On the screen where it asks you to select the matching values from the two tables select the field that is ppopulated in the combo box.

This discussion is closed to new replies.