Linking Combo Boxes

[lepricon]

Alright so I haven't used access in a while and I forgot how to link combo
boxes so that when you select something in one it displays a certain list in
the other. Anyone out there give me a hand?

 

[Ken Snell [MVP]]

See The ACCESS Web:
http://www.mvps.org/access/forms/frm0028.htm

 

[lepricon]

Ken,

Thanks for the short cut. I went there already but am still having some
trouble getting it to work. The stored query looks like it might be what I
am looking for. The problem is that I don't know where it implement that
string.
Also the call in the first code, "strSQL = strSQL & "from Categories" was
giving me an error as it couldn't find the reference.
Need a little more help please.

Tim

 

[Ken Snell [MVP]]

If you need help, you'll have to give us some information about your
form.... such as the names of controls, the SQL string that you're using as
the Row Source queries, etc.

 

[lepricon]

I have two tables. One has a list of programs that my clients work on and
the other has the different charge codes available for each program. Table 1
just has the programs names. Tabel 2 has the program names and all the
charge codes that are related to it. What I want to do is have one combo box
select the program by name from table 1 and then the second combo box will
select the available charge codes off of table 2. Right now I don't have a
SQL string for the row sources (as this is my problem) I don't know what to
do so that I can make what I want to happen, happen. Is that enough
information?

 

[Ken Snell [MVP]]

You tried to use the code at http://www.mvps.org/access/forms/frm0028.htm
without changing it to your actual setup? If yes, then it's no surprise that
it won't work.

Let's assume that your "program name" combo box is named cboPrograms. Its
Row Source should be a query similar to this:

SELECT DISTINCT Table1.ProgramID, Table1.ProgramNames FROM Table1;

Its Bound Column should be column 1.

Then let's assume that your second combo box, the one that will show the
charge codes, is named cboCharges. Its RowSource should be empty initially,
as the code that runs on the cboPrograms combo box's AfterUpdate event will
handle that:

Private Sub cboPrograms_AfterUpdate()
Dim strSQL As String
strSQL = "Select ChargeCodes FROM Table2 "
strSQL = strSQL & "WHERE ProgramID=" & Me.cboPrograms
Me!cbxCombo2.RowSourceType = "Table/Query"
Me!cbxCombo2.RowSource = strSQL
End Sub