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

Question

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?

jacouh · Accepted Answer · 2021-05-14T21:21:21.680

1

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.

edited May 14 '21 at 21:21

answered May 14 '21 at 20:15

jacouh

8,473
5
32
43

This did get the form to go from invisible to visible, which is great, I was hoping to have it change state only after both boxes were updated though, and this way does it after either box is updated. I tried stacking the Ifs, to no avail, but at least this way I can make progress, so thank you. – Christopher J. Joubert May 14 '21 at 20:31
I've modified code to cope with your need. – jacouh May 14 '21 at 21:23
Thank you sir, as it comes along I am learning how to do this (you should have seen my first couple questions... but it is a lot different than the python and SQL I do understand. – Christopher J. Joubert May 14 '21 at 21:27

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

1 Answers1