CODEWITHSUNDEEP
vbams-accessms-access-forms
Christopher J. Joubert
Christopher J. Joubert

Reputation: 144

How do I hide/unhide a subform based on the input from two comboboxes?

I have a mainform, called TrainingsSU In it I am calling subform qry_TrainingSU (built from the query of the same name) I have it set up so that the records auto populate the subform based on the two comboboxes in the main form.

But what I need to do is hide the subform, and have it display only when the two comboboxes are populated and records are loaded.

Here's the current VBA (of which I am not really even a novice at)

Private Sub cbo_EmployeeLookup_AfterUpdate()

    Me!qry_TrainingsSU.Requery
    
    If Me.cbo_EmployeeLookup.Value = "" Then
        Forms!qry_TrainingsSU.Visible = False
    Else
        Forms!qry_TrainingsSU = True
    End If
        
End Sub

Private Sub cbo_TrainingName_AfterUpdate()
     
     Me!qry_TrainingsSU.Requery
     
     If Me.cbo_TrainingName.Value = "" Then
        Forms!qry_TrainingsSU = False
    Else
        Forms!qry_TrainingsSU.Visible = True
    End If
    
End Sub

I found the general form of this code in another answer, here: MS Access: Hide and Unhide subform based on drop down in Main Form However the code doesn't seem to be working for me.

Currently the subform is set to Visible:No So nothing shows up at all. If I change that, it doesn't disappear and the empty subform is still visible. This wouldn't be a problem, except I need to use this form for another query and want to layer them over eachother when the second subform is ready to be used.

Later this form will be used to push an UPDATE SET to a table based on the different subforms.

Is there something that is obviously wrong with the code, or did I miss a setting somewhere?

Upvotes: 1

Views: 426

Answers (1)

jacouh
jacouh

Reputation: 8741

You can try this:

Private Sub updateStates()

    Me!qry_TrainingsSU.Form.Requery    

    If (Me.cbo_EmployeeLookup.Value <> "" AND Me.cbo_TrainingName.Value <> "") Then
      Me!qry_TrainingsSU.Visible = True
    Else
      Me!qry_TrainingsSU.Visible = False
    End If
       
End Sub

Private Sub cbo_EmployeeLookup_AfterUpdate()
    updateStates
End Sub

Private Sub cbo_TrainingName_AfterUpdate()    
    updateStates    
End Sub

Forms!qry_TrainingsSU searches form opened as main form, not a subform.

Upvotes: 1

Related Questions