Having issues useing a table from a form.

[PuddinPie]

I would like to have my from read the value of a column of a table I created and then check it against an IF statement.

Here the code:

If

![tblEmpLocation].Column(2) = 1 Then
Me.txttesting1.Visible = True
Me.txtTesting2.Visible = True
Me.lsttesting1.Visible = True
Me.lsttesting2.Visible = True
Else
Me.txttesting1.Visible = False
Me.txtTesting2.Visible = False
Me.lsttesting1.Visible = False
Me.lsttesting2.Visible = False
End If

The value I want is in the second column of the table anf I want it to make something visiable or not based on it's value of 1 or 2. I keep gatting an error saying that it can not find the field 'table'. this is in Access 97.

Thank you in advance.

 

[pbaldy]

You can't reference a table directly like that. You can either open a recordset or use DLookup(), which is probably simplest in this case.

 

[boblarson]

You have tried to use something that is not possible. You cannot use

! for anything (same with queries). You can't refer to them like you can forms or reports.

So, if the value of the current record (second column) is 1 then it would be this (in the form's On Current Event):

If Me!YourFieldNameHere = 1 Then
   Me.txttesting1.Visible = True
   Me.txtTesting2.Visible = True
   Me.lsttesting1.Visible = True
   Me.lsttesting2.Visible = True
Else
   Me.txttesting1.Visible = False
   Me.txtTesting2.Visible = False
   Me.lsttesting1.Visible = False
   Me.lsttesting2.Visible = False
End If

If that isn't what you want, then I think we need some more explanation.

 

[boblarson]

Boy I'm slow today.

 

[PuddinPie]

I tryed what both of you said and still can't seem to get it to work.
Here is what I have now.

Private Sub Form_Current()
Dim emploc As String

emploc = DLookup("LocationNumber", "tblEmpLocation")

If Me.emploc = 1 Then
Me.txttesting1.Visible = True
Me.txtTesting2.Visible = True
Me.lsttesting1.Visible = True
Me.lsttesting2.Visible = True
Else
Me.txttesting1.Visible = False
Me.txtTesting2.Visible = False
Me.lsttesting1.Visible = False
Me.lsttesting2.Visible = False
End If
End Sub

Essentualy what I'm trying to do is to have certian things display depending on the users location. I have a table that is made by a query that set's the users location to either a 1 or a 2. I want to be able to read that value in the table and edit the form accordingly.

I still get an error stating "can't find the field emploc"
As you can tell. Kinda new at this stuff.

 

[boblarson]

Two things:

Me only works if you are referring to an object or field on the form. You are using a variable so you get rid of Me.

Second, you have no criteria on your DLookup so you will always get the same location that is available first in the table. You need criteria based on the user and THEIR location (wherever you store that information).

 

[PuddinPie]

I just fixed it before I refereshed this page. I forgot about having the me in the IF statment. I removed it and not it's working great. Thank's for your help.