Combo boxes-Limiting content of one from another

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a common problem of trying to get the values of one
combobox to restrict the values in another. I've looked at
the Northwind examples. the MVPS site and a couple of
books but they all seem to do something else as well and I
can't fathom out which bits of code I need from all the
examples I've seen. Basically I have a combobox called
cmbPSubject that is based on a table tblPSubject and a
combobox called cmbSSubject based on a table tblSSubject.
The tables are linked by a field PSubjectindex. I want
whatever is chosen in cmbPSubject to dictate what is then
shown in cmbSSubject based on the PSubjectindex in both
tables matching.
As I said I've tried following all the usual source
examples but get lost with added functions that I don't
need. Can someone give me ABC instructions for this?
TIA
Tony
 
You need to rewrite the query for the RowSource of the 2nd combo box using the value of
the first combo box in the WHERE part of the query in the AfterUpdate event of the 1st
combo box. It is probably easiest to do this by placing the SQL text of the query right
into the RowSource.

cmbSSubject.RowSource = "SELECT.... WHERE [PSubjectindex]=" & Me.cmbPSubject

This assumes the value of PSubjectIndex to be numeric, if it is text:

cmbSSubject.RowSource = "SELECT.... WHERE [PSubjectindex]='" & Me.cmbPSubject & "'"
 
Thanks Wayne worked fine!
Tony
Wayne Morgan said:
You need to rewrite the query for the RowSource of the 2nd combo box using the value of
the first combo box in the WHERE part of the query in the AfterUpdate event of the 1st
combo box. It is probably easiest to do this by placing the SQL text of the query right
into the RowSource.

cmbSSubject.RowSource = "SELECT.... WHERE [PSubjectindex]=" & Me.cmbPSubject

This assumes the value of PSubjectIndex to be numeric, if it is text:

cmbSSubject.RowSource = "SELECT.... WHERE [PSubjectindex]='" & Me.cmbPSubject & "'"

--
Wayne Morgan
Microsoft Access MVP


I have a common problem of trying to get the values of one
combobox to restrict the values in another. I've looked at
the Northwind examples. the MVPS site and a couple of
books but they all seem to do something else as well and I
can't fathom out which bits of code I need from all the
examples I've seen. Basically I have a combobox called
cmbPSubject that is based on a table tblPSubject and a
combobox called cmbSSubject based on a table tblSSubject.
The tables are linked by a field PSubjectindex. I want
whatever is chosen in cmbPSubject to dictate what is then
shown in cmbSSubject based on the PSubjectindex in both
tables matching.
As I said I've tried following all the usual source
examples but get lost with added functions that I don't
need. Can someone give me ABC instructions for this?
TIA
Tony
Click to expand...
Click to expand...
 
Back
Top