How to synchronize Access combo boxes...stuck

[bishop]

Here is my problem. I am stuck on how to set this up. Here are my tables:

tblCategories
tblExercises


tblSubCategories Back, tblSubCategories Biceps, tblSubCategories Triceps, etc.

tblCategories has one column:
Column name "Categories"
Shoulders
Core
Biceps
Triceps

All tblSubCategories has multiple columns with data underneath. Example:
tblSubCategories Back
Column name DB
Back Row
Column name TRX
TRX Rows
TRX Rear Fly
Column name Band
Band Rows

I need to create a form that has 3 combo box that is dependent on the other. For example:

If I pick Back from combo box Categories , the SubCategory combo box would have column names from that (DB, TRX, Band, etc)

Then the 3rd combo box would allow me to pick the exercises from the SubCategory combo box. So if I chose TRX it would list; TRX Rows, TRX Rear Fly, etc.

Any help on this would be greatly appreciated.

[June7]

Don't try setting RowSource for the two dependent comboboxes in the properties dialog. Use VBA in combobox AfterUpdate events to set the RowSource property. Use a Select Case structure.

The second combobox RowSourceType could be Field List. Then use the VBA code to set the RowSource to the correct table.

Set the two dependent comboboxes Visible property to No then in code make each visible after selection in previous combobox.

[bishop]

Don't try setting RowSource for the two dependent comboboxes in the properties dialog. Use VBA in combobox AfterUpdate events to set the RowSource property. Use a Select Case structure.

How would I go about doing that?

[June7]

Are you not familiar with VBA coding? The Select Case is a structure common to most if not all programming languages.

[bishop]

No sorry. I am new to all this and have researched throughout the web before posting this question. Getting very frustrated.

[June7]

You have two ways to code in Access - macros and VBA. With rare exception, I use only VBA. I don't think macro can accomplish this particular procedure. Here is an example of a Select Case setting properties of a combobox based on selection of first combobox.

Sub cboFirstCombo_AfterUpdate()
    Select Case Me.cboFirstCombo
        Case ""
            Me.cboSecondCombo.Value = Null
            Me.cboSecondCombo.RowSourceType = ""
            Me.cboSecondCombo.RowSource = ""
        Case "State Number"
            Me.cboSecondCombo.RowSourceType = "Table/Query"
            Me.cboSecondCombo.RowSource = "SELECT DISTINCT StateNum FROM Submit;"
        Case "Sample Type"
            Me.cboSecondCombo.RowSourceType = "Value List"
            Me.cboSecondCombo.RowSource = "Acceptance;Independent Assurance;Information;Preconstruction;Quality"
    End Select
End Sub

[nurul]

hye June7,

i need your help to explain to me this.
"SELECT DISTINCT StateNum

what is actually DISTINCT?
is't StateNum is field name?


i already make it like this but it will list all data in the field when i select the dropdown. suppose be it will only show the AA project.

Case "AA"
Me.qproject1.RowSourceType = "Table/Query"
Me.qproject1.RowSource = "SELECT DISTINCT project FROM Table4;"


can you help me to solve this problem..

thank you.

[June7]

DISTINCT is a keyword used in SQL query so that only one occurrence of each value in a field will show in the results. Yes. StateNum is a fieldname in my table.

If you want only AA project to show, then need criteria in the query. Use WHERE clause. However, won't the result be a single record?
Me.qproject1.RowSource = "SELECT project FROM Table4 WHERE project=" & Me.combobox

[nurul]

one more question,

Me.combobox is what..?
is it my combobox name for example qregion1

i want when i select the qregion1 then for qproject1, it will list the project that have in qregion1..

thank you

[nurul]

one more thing, the project will be more than one that will listed

[June7]

Use your combobox name in place of 'combobox'.

Me.qproject1.RowSource = "SELECT project FROM Table4 WHERE region='" & Me.qregion1 & "'"

Note the apostrophe delimiters in the WHERE clause. These are needed if region field is text. Date/Time type would use # and number type use nothing.

Then you need to requery qproject1 combobox:
Me.qproject1.Requery

[nurul]

yeaayyy it workk....

thank you very much June7..