From one dropbox of tables to another Dropbox of that choice...

[Guest]

How do you set up a form with a dropbox or listbox of choices that
automatically switches to another form with a dropbox or listbox with choices
of that original selection. For example: I would like to choose from a list
of sports, the sport of baseball. After that selection I would like for a
dropbox or listbox to offer selections of baseball related records to be
available, i.e., equipment, rules, etc. Afterwards, if I choose equipment, I
would like to get a dropbox for choices of bats, gloves, etc. After choosing
bats I would like to open a form to get data from a bat table.

I am using Access 2000.

Thanks for your help!

 

[Jeff Boyce]

Take a look at mvps.org/access, or at Google.com, under the topic "Cascading
ComboBoxes".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I think I finally learned how to cascade 2 ComboBoxes as follows:

ROW SOURCE under the SchoolComboBox:

SELECT DISTINCTROW tblSchoolsPlusDRMs2.OrgCode,
tblSchoolsPlusDRMs2.SchoolName FROM tblSchoolsPlusDRMs2 WHERE
(((tblSchoolsPlusDRMs2.DrmID) Like
Forms!frmComboBoxFormDRMandSchool!DRMComboBox)) ORDER BY
tblSchoolsPlusDRMs2.SchoolName;

For DRMComboBox this Event Procedure is under After Update…The ROW SOURCE
has the following:

SELECT DISTINCT tblDRMs.DrmID, tblDRMs.DrmName FROM tblDRMs ORDER BY
tblDRMs.DrmName;

I have the following in the Codes listed:

Option Compare Database

Private Sub DRMcomboBox_AfterUpdate()
Me.SchoolComboBox = Null
Me.SchoolComboBox.Requery
Me.SchoolComboBox = Me.SchoolComboBox.ItemData(0)
End Sub

Private Sub Form_Current()
Me.SchoolComboBox.Requery
End Sub

Private Sub Form_Load()
Me.DRMComboBox = Me.DRMComboBox.ItemData(0)
Call DRMcomboBox_AfterUpdate
End Sub

Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAssessmentDRMandSchoolCombo"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command12_Click:
Exit Sub

Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub

I’m not sure I have everything listed correctly…

I’m using Access 2000.

Have I done it correctly?

Thanks for your help!

 

[Jeff Boyce]

I suspect you don't really want someone else to try to duplicate what you
describe.

What happens when YOU run it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

No not really, Jeff... Don't want anyone to go through the trouble to
duplicate it.

It's working fine in just a few tests, but I wanted to know if there was
something apparent that is wrong in the codes that would keep it from being
stable in the long run.

The only problem I've had is when I tried to use words Assessment and
Confirmation in the SELECT DISTINCT or SELECT DISTINCTROW phrases they seem
to develop an internal conflict. However, it is not a major problem.

Thanks for your help!