Choosing record using cascading Combo Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a form with two combo boxes, the choices available in the second box
being dependent upon the choice in the first. Now I'd like to trigger the
proper record to come up (in Form view of course) once that second choice is
made.

I've been told the following:

----------

You need a way to tell Access to find/load the record that corresponds to
the selection(s) you've made. One way is to filter the recordset the form
is bound to. Another is to based the form on the single record represented
by the choices.

To do the latter, change the query on which you base the form to include, as
a selection criterion for the ID field, a reference pointing to the ID of
the value selected in the combo box (the last one in the "cascade".

Then, in the AfterUpdate event of that last combobox in the cascade, use
something like:

Me.Requery

to force the form to rerun the query on which it is based.
 
Hi Eric,

first, lets deal with limiting the combobox(es) and drilling
down to the list you want to pick from. Since you didn't
supply any specific data, hre is some code you can draw an
analogy from using Brand, Make, and Model. Each combo
filters the records that can be chosen in the final (4th)
combo, which uses RecordID for the unique field.

On the AfterUpdate event of comboboxes to pick Brand, Make,
and Model -->
=BuildSQL()

then put this code behind your form

'~~~~~~~~~~~~~~~~~~~
private function BuildSQL()

on error goto Err_proc

dim s as string, mWhere as string

mWhere = ""

if not isnull(me.Brand_controlname) then
mWhere = " And Brand_fieldname = '" " _
& me.Brand_controlname & "'"
endif

s = "SELECT Make " _
& " RROM Tablename " _
& " WHERE Make Is Not Null " & mWHERE " _
& " ORDER BY Make;"
me.MakeCombo_controlname.RowSource = s
me.MakeCombo_controlname.Requery

if not isnull(me.Make_controlname) then
mWhere = mWhere _
& " And Make_fieldname = '" " _
& me.Make_controlname & "'"
endif

s = "SELECT Model " _
& " FROM Tablename " _
& " WHERE Model Is Not Null " & mWHERE " _
& " & " ORDER BY Model;"
me.ModelCombo_controlname.RowSource = s
me.ModelCombo_controlname.Requery

if not isnull(me.Model_controlname) then
mWhere = mWhere _
& " And Model_fieldname = '" " _
& me.Model_controlname & "'"
endif

s = "SELECT RecordID, Field2, Field3 " _
& " FROM Tablename " _
& " WHERE true = true " & mWHERE " _
& " & " ORDER BY Field2;"
me.RecordSelectCombo_controlname.RowSource = s
me.RecordSelectCombo_controlname.Requery

Exit_proc:
exit function

Err_proc:
msgbox err.description,,"ERROR " & err.number & " BuildSQL"
'press F8 to step thru code and fix problem
'comment next line after debugged
stop : resume
resume Exit_proc
end function

'~~~~~~~~~~~

Now, for the code to find a record based based on what is
picked in RecordSelectCombo. You can use this code on the
AfterUpdate event:
=FindRecord()

'~~~~~~~~~~~~~~~~~~~

Private Function FindRecord()

'thanks for ideas, freakazeud

If IsNull(Me.ActiveControl) Then Exit Function

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

Dim mRecordID As Long
mRecordID = Me.ActiveControl
Me.ActiveControl = Null
Me.RecordsetClone.FindFirst "RecordID = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
End If

End Function

'~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)
 
Back
Top