Hide/Show Subform if Value from form field not present in another table

[iseman22]

I have a main form called frm_Pickups tied to a table called Pickups and a subform called DODAAC subform tied to a table named DODAAC.

On frm-Pickups, I have four fields named DODAAC1, DODAAC2, DODAAC3, and DODAAC4.

What I would like is to have the DODAAC subform appear whenever the user enters a DODAAC that isn't present in the DODAAC table. They could then add the necessary data to the DODAAC table (via the subfrom) and continue adding data to the main form.

To achieve this goal, I tried a few options. On frm_Pickups, I also have unbound, text fields which show the city and state for the DODAAC entered. I tried to use the following code to check if those fields are blank (should only occur if the DODAAC doesn't exist in the DODAAC table).

Private Sub DODAACCS_AfterUpdate()
Select Case
Case Me.City1 = Null
Me.DODAAC_subform.Visible = True
Case Me.City2 = Null
Me.DODAAC_subform.Visible = True
Case Me.City3 = Null
Me.DODAAC_subform.Visible = True
Case Me.City4 = Null
Me.DODAAC_subform.Visible = True
End Select
End Sub

I keep getting a syntax error.

Probably a newb mistake; but, I can't seem to figure it out. Any help is greatly appreciated.

Paul.

 

[mistera]

Using the Select statement should be used when you are querying a field and want to do different things depending on the field's value. In your case, you are querying 4 different fields and yes, the syntax for the statement was not typed properly.

You'd be better off using an embedded macro in the AfterUpdate event for each of the 4 city fields on the frm_Pickups form.

In the Action column, select SetProperty. In the control name field, enter the subform's name (DODAAC_subform). In the Property field, select Visible. In the Value field, enter -1.

I think that should do it!

 

[iseman22]

Thanks mistera, your suggestion made the subform appear. Unfortunately, I only want it to appear if the user enters data in the fields DODAAC1, DODAAC2, DODAAC3 or DODAAC4 that doesn't already exist in the DODAAC table.

Also, I'd like to hide the subform once the user enters the missing data on the subform (i.e. on exit). Tried your suggestion on the on exit event of the subform; but, it's still there.

Thanks again,
Paul.

 

[mistera]

In that case, you probably want to move the embedded macro to the Current event of the main form and include conditions for setting the Visible property for the subform either on or off based on the city field values.

If you additionally want the subform to immediately be hidden when the user enters a value on the subform, you might want to also add the macro to change the form's visible property to false in the AfterUpdate event of the field where the user enters the data.