how to: comboboxes steer each other and datasheet sub form

[advinci]

Dear all,

here's asking for your help please.

I'm back to programming VBA in Access after a 10 years break. I'm doing well, but can't get this one to work:

I got a form with a subform ([Datenpaare Abfrage Unterformular2] - and I think this is the name of it's container, too, but am not too sure if I got that right - where may I look that up to be sure?). The subform displays rows from a query "Datenpaare Abfrage" (which is based on an SQL query that integrates three tables with INNER JOIN).

The main form [IT-System Wenn-Dann Analyse Kosten] with two combo boxes, Kombinationsfeld26 and Kombinationsfeld34.

The first combo box displays all values of a certain column (i.e. column_1) in one of the mentioned three tables (i.e. table_1), using a SELECT DISTINCT SQL query.

When a user changes the first combo box, I'd like two things to take place:
* the subform displays only rows whose column_1 entry matches the value selected in the first combo box (I had that one working using a SearchForRecord macro)
* the second combobox shows all values available in a second column (i.e. table_1, column_2) from those rows whose column_1 value matches the selection in the first combo box (in SQL: SELECT DISTINCT column_2 from table_1 where column_1 = value of first combo box).

When a user then changes the second combo box, I'd like that:
* the subform displays only rows whose column_1 and column_2 entries matches the values selected in the first and second combo boxes respectively

Have been trying to get this working for eight hours now.

Your help, or a pointer into the right direction, heartly welcome.

Thanks in advance for your time!

Sincerely,
RK

 

[TheAceMan1]

How are ya advinci . . .

Some insight: faq702-4289

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[advinci]

Thanks AceMan, good. Yourself?

Turns out my comboboxes were all right (similar to syntax recommended in FAQ AceMan mentioned). The error was in the VBA code that changed the rowsource of the listbox subform. After doing that, the listbox was empty always. Turns out (see this post:

http://bytes.com/topic/access/answers/753264-change-subforms-record-source-combo-box#post3002595)

that I has to remove the entries for master and child dependencies (which Access had written there when I created the listbox).
Following that, assigning a nwe rowsource to the subform using

Forms("IT-System Wenn-Dann Analyse Kosten")("Datenpaare Abfrage Unterformular2").Form.RecordSource = StrgSQL
worked fine.

Good luck everyone out there!
RK