Second Combo made visible based on Associations Table and Query

[padlocked17]

I have a form that is used for recording test scores. The first selection that is made is a type of test. After the test is selected, some tests have a second sub-type of test that needs to be recorded. My quetion lies in how to hide or grey out this second combo box until a test is selected that requires it.

I don't want to "hard-code" it in the sense that I create an If Else statement that requires looking at values from the 1st test type combo box. I want to make sure that the DB is scalable and when additional tests are added, additional sub tests can be added if necessary.

If this makes sense and anyone has any ideas, I would appreciate it.

 

[asather]

You just need a bit of VBA. In your AfterUpdate event for the testType combo box, slap in this code:

Private Sub testType_AfterUpdate()
'12/30/2007
'

    If Me.testType.Value = "" Then
        Me.testSubType.Enabled = False
    Else
        Me.testSubType.Enabled = True
    End If

End Sub

You will also need to set your testSubType combo box's enabled property to no.

 

[padlocked17]

Right, I know that I have that option, but I'm looking for a non "hard-coded" solution. One where I could incorporate a query into the If Then statement so that I'm not set to particular values and I won't have to constantly update the VBA if a new option is added via the forms and tables.

That and I'm wanting the second combo box to only show up for certain test types if selected in the first combo box. I'd have to write a If Me.cboSelect.Value = 1 Then Me.cboSelect2.Visible = True which is what I'm trying to avoid and reference a query is determine if the second combo box should be made visible.

 

[asather]

Mkay.

If you haven't already, your cboSelect combo should be populated by a table. I'm going to call the table tblSelect for now.

Here's the structure:

tblSelect(ID, type, requiresSelect2)
PK: ID

requiresSelect2 is a Yes/No (boolean) type.

Make a query. We'll call it qryRequiresSelect2.

SELECT tblSelect.type
FROM tblSelect
WHERE (((tblSelect.requiresSelect2)=True));

That SQL is rusty at best, but you get the idea, no? You're creating a query of all those test types that will require the additional cboSelect2 information.

Now my VBA changes a bit. Instead of looking to see if cboSelect is filled in, we're going to see if its value is listed in qryRequiresSelect2.

'12/30/2007
'
    Dim requiresSelect As Boolean
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("qryRequiresSelect2")
    requiresSelect = False
    rst.MoveFirst
    While Not rst.EOF
        If Me.cboSelect.Value = rst!Type Then
            requiresSelect = True
        End If
        rst.MoveNext
    Wend

   
    If requiresSelect = False Then
        Me.cboSelect2.Enabled = False
    Else
        Me.cboSelect2.Enabled = True
    End If

That code, of course, goes in the AfterUpdate property of the cboSelect field.

 

[padlocked17]

Awesome, I'm going to start playing around with this idea.

Could the cboSelect combo use a query that contains all info necessary or does it need to be populated from a table?

 

[asather]

Could the cboSelect combo use a query that contains all info necessary or does it need to be populated from a table?

It certainly could be populated by a query. Just make certain that that query is the source for qryRequiresSelect2.

 

[padlocked17]

Awesome, I'll give it a whirl tomorrow when I have more brain cells I can devote.

Thanks a ton for the sample code! I'll post up if I have any more questions or to let you know how it turned out.

Thanks again.

 

[padlocked17]

Alright, more importantly here, I am trying to figure out how to make this scalable.

Let me re-hit the scenario, and maybe get some suggestions on how to implement this:

I have a table that lists all of the tests, evals, test/eval associations and then two tables for test results and eval results. I have several tests that have a secondary test that's score should be recorded as part of the same row of results in either the eval results or test results table.

I need to figure out how to setup all of my tables for this. My proposed idea is to simple create another two tables that would associate tests with a subtest.

Would I need to create another table that has subTest's and subEvals and then another table yet to associate Primary Tests with Secondary tests and so on or could I use another association table to take the TestID of one row and make it a subtest for another TestID. Then in the tblTestTypes table, I could throw in a column with a Yes/No boolean called "SubTest" to use when filtering on queries and the such.

To ask another question, how would I design a form that would allow me to add tests, add evals, make them sub tests/evals and then assocation them with each other.

If that makes sense, I'd appreciate anyone's opinion.