The use of If, Then and End If

[JL Wright]

Lets say I have a table for products named tblProduct. Every product has a
different set of fields that I would like to enter data through.

Now I have a form called frmProduct. I have several sub forms named
frmSubProductOrange, frmSubProductApple, frmSubProductGrape and so on.

Now I have a field in frmProduct that I have named tbProduct.

When I enter that field have a drop down box with a laundry list of
products.

I have all of my sub form visible = False on Open in frm Product.

This is what I want to do.

When I enter tbProduct and select Orange, I want frmSubProductOrange to be
visible = True.

Get the picture.

I have tried the following but it doesn’t work.

Private Sub tbProduct_AfterUpdate()
If (tbProduct) = "Orange" Then
frmSubProductOrange.Visible = True
frmSubProductApple.Visible = False
frmSubProductGrape.Visible = False
End If
End Sub

I hope all of this is clear. I need help.

 

[Duane Hookom]

"but it doesn’t work" doesn't tell us much. What happens? As a rule, I prefix
all of my references to controls on forms with "Me."

Does your code compile?

 

[Ken Sheridan]

Jerry:

One possibility is that the combo box from whose list you are selecting the
product is bound to a numeric foreign key column which references another
table of product names with a numeric (e.g. autonumber) primary key. This
will be the case for instance if you've used the 'lookup field wizard' when
designing the table. You see the product name but the underlying value is in
fact the hidden number.

If this is the case one solution is to reference the value you see in the
combo box by means of its Text property. You can also assign the result of
an expression which evaluates to True or False to each subform control's
Visible property rather than using an If…End If construct. Its also
advisable to test for Null in case a user deletes an existing selection in
the combo box:

Private Sub tbProduct_AfterUpdate()

On Error Goto Err_Handler

Dim ctrl As Control

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
Me.frmSubProductOrange.Visible = (ctrl.Text = "Orange")
Me.frmSubProductApple.Visible = (ctrl.Text = "Apple")
Me.frmSubProductGrape.Visible = (ctrl.Text = "Grape")
Else
Me.frmSubProductOrange.Visible = True
Me.frmSubProductApple.Visible = True
Me.frmSubProductGrape.Visible = True
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub

Note that frmSubProductGrape etc is the name of the subform *control*, i.e.
the control in the parent form which houses the subform, not the name of its
underlying form object, unless of course both have the same name.

If you have used the 'lookup field wizard' you should be aware that most
developers consider this something of a blunder by Microsoft and best
avoided. For the gory details see:


http://www.mvps.org/access/lookupfields.htm


Ken Sheridan
Stafford, England

 

[JL Wright]

Thanks to Duane and Ken. It is now working. "Working" meaning the correct
subform becomes visible with specific criteria in the field. I guess I
didn't understand the importance of Me. I have seen it before but never used
it because I didn't understand it. Thanks again the correction adds a very
important function for the database.