Cascading combobox using multiple queries based upon selection in first combo box.

[NightWalker]

First of all I am fairly new to access and self taught. Please let me know if there is an easier or more proper way to do what I am trying to do. Here we go.....



I have a form that is asking for information about the results of a test procedure. One of the comboboxes is asking for which tank the item was tested in (cboTankTestedIn) . There are 3 tanks to choose from (HP1,HP2,HP3). The next 2 comboboxes are the equipment used to test the item (MegType, PIused). Some of the test equipment is always at a certain tank. I would like to limit the selections in the equipment comboboxes based on which tank was tested in. I have a table with all the test equipment in it (tbl_TestEquipment) and a field that signifies that the test equipment is active or not (TestEquipmentInstalled). I have another field that tells the location of the test equipment (TestEquipmentInstalledLocation). I wrote 4 queries to break this down to tank level. The first query pulls out the active equipment for all tanks (qry_TestEquipmentActive). then the other 3 separate the equipment to location being used (qry_TestEquipmentHP1,qry_TestEquipmentHP2,qry_Tes tEquipmentHP3) they are rowsourced to the active equipment query.

please help me I think I might have the code in the wrong place but I am not even sure I am approaching this properly. Thank you for your time and advice!

Here is my code

Option Compare Database
Private failtoggle As Boolean

Private Sub Form_Activate()
On Error GoTo Form_Open_Err
Me.ProductionItemPartNumber.SetFocus
Exit Sub
Form_Open_Err:
    MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
    
End Sub
Private Sub Form_Current()
    failtoggle = True
    
    If PassFail Then
        tglPassFail.Caption = "Pass"
        tglPassFail.Value = 0
    Else
        tglPassFail.Caption = "Fail"
        tglPassFail.Value = -1
        
    End If
    failtoggle = False
    
    Select Case cboTankTestedIn
        Case Is = "HP1"
            Me.MegType.RowSource = qry_TestEquipmentHP1
            Me.PIUsed.RowSource = qry_TestEquipmentHP1
        Case Is = "HP2"
            Me.MegType.RowSource = qry_TestEquipmentHP2
            Me.PIUsed.RowSource = qry_TestEquipmentHP2
        Case Is = "HP3"
            Me.MegType.RowSource = qry_TestEquipmentHP3
            Me.PIUsed.RowSource = qry_TestEquipmentHP3
        Case Is = ""
            MsgBox ("Select Tank Tested in")
        Case Is = Null
            MsgBox ("Select Tank Tested in")
            
        End Select
     
End Sub
Private Sub Form_Load()
    failtoggle = True
    
    If PassFail Then
        tglPassFail.Caption = "Pass"
        tglPassFail.Value = 0
    Else
        tglPassFail.Caption = "Fail"
        tglPassFail.Value = -1
        
    End If
    failtoggle = False
    
End Sub
Private Sub NewItem_Click()
On Error GoTo EH
Dim strPartNumber As String
Dim strTech As String
Dim strTestedDate As Date
Dim intTankTestedIn As Integer
Dim intMegger As Integer
Dim intPressure As Integer
strPartNumber = Me.ProductionItemPartNumber
strTech = Me.Technician
strTestedDate = Me.TestedDate
intTankTestedIn = Me.TankTestedIn
intMegger = Me.MegType
intPressure = Me.PIUsed
DoCmd.RunCommand acCmdSaveRecord
'Displays previous records into text box
DispInfo = SerialNumber & "--" & TestedDate & "--" & ProductionItemPartNumber & "--" & tglPassFail.Caption & vbCrLf & vbCrLf & Me.DispInfo.Value
DoCmd.GoToRecord , , acNewRec
        
ProductionItemPartNumber.SetFocus
Exit Sub
EH:
    MsgBox Err.Number & vbCrLf & Err.Description
    
'frm_DailyTested!SerialNumber.SetFocus
End Sub
Private Sub btnSame_Click()
On Error GoTo EH
Dim strPartNumber As String
Dim strTech As String
Dim strTestedDate As Date
Dim intTankTestedIn As Integer
Dim intMegger As Integer
Dim intPressure As Integer
strPartNumber = Me.ProductionItemPartNumber
strTech = Me.Technician
strTestedDate = Me.TestedDate
intTankTestedIn = Me.TankTestedIn
intMegger = Me.MegType
intPressure = Me.PIUsed
DoCmd.RunCommand acCmdSaveRecord
'Displays previous records into text box
DispInfo = SerialNumber & "--" & TestedDate & "--" & ProductionItemPartNumber & "--" & tglPassFail.Caption & vbCrLf & vbCrLf & Me.DispInfo.Value
DoCmd.GoToRecord acDataForm, "frm_DailyTested", acNewRec
Me.ProductionItemPartNumber = strPartNumber
Me.Technician = strTech
Me.TestedDate = strTestedDate
Me.TankTestedIn = intTankTestedIn
Me.MegType = intMegger
Me.PIUsed = intPressure
SerialNumber.SetFocus
    
Exit Sub

EH:
    MsgBox Err.Number & vbCrLf & Err.Description
    
'frm_DailyTested!SerialNumber.SetFocus
End Sub
Private Sub tglPassFail_AfterUpdate()
    If failtoggle Then Exit Sub
    
    If PassFail Then
        tglPassFail.Caption = "Fail"
        PassFail = False
    Else
        tglPassFail.Caption = "Pass"
        PassFail = True
    End If
End Sub

[NightWalker]

I moved the following code to an after update event but I am still not getting it to limit the selections in the second and third comboboxes

Private Sub cboTankTestedIn_AfterUpdate()
    Select Case cboTankTestedIn
        Case Is = "HP1"
            Me.MegType.RowSource = qry_TestEquipmentHP1
            Me.PIUsed.RowSource = qry_TestEquipmentHP1
            Me.Requery
            MsgBox ("Im HP1")
        Case Is = "HP2"
            Me.MegType.RowSource = qry_TestEquipmentHP2
            Me.PIUsed.RowSource = qry_TestEquipmentHP2
            Me.Requery
            MsgBox ("Im HP2")
        Case Is = "HP3"
            Me.MegType.RowSource = qry_TestEquipmentHP3
            Me.PIUsed.RowSource = qry_TestEquipmentHP3
            Me.Requery
            MsgBox ("Im HP3")
        Case Is = ""
            MsgBox ("Select Tank Tested in")
        Case Is = Null
            MsgBox ("Select Tank Tested in")
            
    End Select
End Sub

Any help would be appreciated. If I am completely off on what I am doing please let me know and I will throw out what I have and start over. Thank you for your time.

[ssanfu]

I don't set up my cascading combo boxes the way you have. I don't like changing the row source "on the fly".

Soooo, attached is an example dB I created "way back then". It was an early attempt - the naming of the tables is very wrong. I didn't know that an object name should never begin with a number.

You can use a saved query or a SQL string as the combo box row source. I did both..... again it was an early attempt.

About the two forms: one form is using combo boxes and the other is using list boxes (a different kind if combo box ). I used the list box form to see if all the data was entered/linked correctly and if the controls cascaded correctly.


Tear it apart and have fun. The hardest thing about this dB was understanding how to set up the tables.



Try selecting the first option in each of the controls (combo or list), then go back and select the 2nd option in the first control.

[NightWalker]

Why do you have so little information in each table?

[ssanfu]

The dB was to demonstrate cascading combo boxes (and list boxes). So I cut down the forms to focus on the cascading effect.

The tables have all the info necessary. If you look at the relationship window, you will see a table (tblOfImpacts) that is not bound to a form. This is the "main" table that holds the data to identify the cause of the impact and equipment failure..

The list box form was something I came up with to check the linking of the tables/data was correct. I could see the data (unlike a combo box) at once.

The combo box form was the form used to enter the info. You would start at the UNIT level and work your way down to the lowest level - the equipment that failed. The form had controls to enter comments, the amount of impact (lost production), the root cause, the duration, the next step and the resolution (if any).

[NightWalker]

Steve,

I sent you a message.

Thank you,
Walker