Referencing a field in VBA (?)

[Guest]

I'm trying to have a set of fields appear or not appear in a form based on a
value set by the user and stored in a table. The table is called
tblApplicationConstants and the field within that is PhaseNumber. Here's the
code that I have for the form:

Private Sub Form_Load()
If tblApplicationConstants.PhaseNumber <= 7 Then
Me.Phase8.Visible = False
Me.Phase82.Visible = False
Me.btnPh8.Visible = False
Me.btnPh82.Visible = False
End If
End Sub

Here, Phase8 and Phase82 are text boxes in the form, and btnPh8 and btnPh82
are command buttons. When I attempt to open the form, I get an error:

Compile error: Variable not defined

Then it opens Visual Basic and highlights "Private Sub Form_Load()" in
yellow, and "tblApplicationConstants" in gray.

I assume the period between "tblApplicationConstants" and "PhaseNumber" is
throwing it off somehow. Am I not referencing the table/field properly? Any
help would be greatly appreciated.

Thanks,
Tim

 

[fredg]

I'm trying to have a set of fields appear or not appear in a form based on a
value set by the user and stored in a table. The table is called
tblApplicationConstants and the field within that is PhaseNumber. Here's the
code that I have for the form:

Private Sub Form_Load()
If tblApplicationConstants.PhaseNumber <= 7 Then
Me.Phase8.Visible = False
Me.Phase82.Visible = False
Me.btnPh8.Visible = False
Me.btnPh82.Visible = False
End If
End Sub

Here, Phase8 and Phase82 are text boxes in the form, and btnPh8 and btnPh82
are command buttons. When I attempt to open the form, I get an error:

Compile error: Variable not defined

Then it opens Visual Basic and highlights "Private Sub Form_Load()" in
yellow, and "tblApplicationConstants" in gray.

I assume the period between "tblApplicationConstants" and "PhaseNumber" is
throwing it off somehow. Am I not referencing the table/field properly? Any
help would be greatly appreciated.

Thanks,
Tim

You can't reference a field in a different table that way.
Look up DLookUp in VBA help.

If DLookUp("[PhaseNumber]","tblApplicationConstants") <= 7 Then
Me.Phase8.Visible = False
etc.
End If

The above will return the value of the first record in the table.
If the table contains more than one record, you will have to add a
where clause to the above to find the value you want. See VBA help:
Where Clause + Restrict data to a subset of records

 

[Guest]

That did the trick, thanks a lot, Fred!

Tim

I'm trying to have a set of fields appear or not appear in a form based on a
value set by the user and stored in a table. The table is called
tblApplicationConstants and the field within that is PhaseNumber. Here's the
code that I have for the form:

Private Sub Form_Load()
If tblApplicationConstants.PhaseNumber <= 7 Then
Me.Phase8.Visible = False
Me.Phase82.Visible = False
Me.btnPh8.Visible = False
Me.btnPh82.Visible = False
End If
End Sub

Here, Phase8 and Phase82 are text boxes in the form, and btnPh8 and btnPh82
are command buttons. When I attempt to open the form, I get an error:

Compile error: Variable not defined

Then it opens Visual Basic and highlights "Private Sub Form_Load()" in
yellow, and "tblApplicationConstants" in gray.

I assume the period between "tblApplicationConstants" and "PhaseNumber" is
throwing it off somehow. Am I not referencing the table/field properly? Any
help would be greatly appreciated.

Thanks,
Tim

You can't reference a field in a different table that way.
Look up DLookUp in VBA help.

If DLookUp("[PhaseNumber]","tblApplicationConstants") <= 7 Then
Me.Phase8.Visible = False
etc.
End If

The above will return the value of the first record in the table.
If the table contains more than one record, you will have to add a
where clause to the above to find the value you want. See VBA help:
Where Clause + Restrict data to a subset of records