Hi

I am need some dire help with the following error - I've never used VBA or access before, but i am doing for a project for work and I am teaching myself using google and your forums

I have a table called "Table1" which contains various employee infromation some of of the columns are:

Question Level Emp1 Emp2
can you use MS Visio? Top 7 0
can you use MS Visio? Middle 0 4
can you use MS Visio? Junior 0 0


So basically when the user signs in, they have a particular id assigned to their username (i.e. Emp1, Emp2, etc). When the user opens a form called "Form5", i am trying to build a SQL query to retrieve the above rating which is greater than zero and the corresponding Level from the above table to display in Form5 for that particular user.

The code i have is:



Option Compare Database

Public EveryoneCanSeeMe1 As String

' ****the above public variable was created to retrieve the particular user profile (i.e. Emp1, Emp2, etc) from the Login Form.******

Private Sub Form_Load()

Me.Label90.Caption = Forms!Form1.EveryoneCanSeeMe1
'******assigning the particular user profile to a label on this form

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim Str As String

Set MyDB = CurrentDb()

Str = "SELECT Table1.*" & _
"FROM (Table1)" & _
"WHERE (Table1.(" & Me.Label90.Caption & ")) > 0" & _
"AND (Table1.Question= '" & Me.Label46.Caption & "')"
' *****Me.Label46.Caption is a label whose caption is "can you use MS Visio?"

Set rst = MyDB.OpenRecordset(Str)
Me.Label65.Caption = rst![Me.Label90.Caption]
Me.Label67.Caption = rst![Level]
rst.close

End Sub


If i ran this code (and assuming the user who signed in has a profile of Emp1), the code would need to retrieve
Me.Label65.Caption = 7
Me.Label67.Caption = Top

But I've got almost every error possible from syntax error, field not found, etc. I've tried various combination of using parentheses/brackets, punctuations, etc - but it doesnt seem to work.
If I were to replace Me.Label90.Caption in the above code to the actual Column name in the table, eg. Emp1, then the code works perfectly

can you please help

thanks in advance
Sunny

Store it first into a variable.

...

Dim sLabel90 As String
sLabel90 = Me.Label90.Caption

Set rst = MyDB.OpenRecordset(Str)
Me.Label65.Caption = rst![sLabel90]
Me.Label67.Caption = rst![Level]
rst.close

...

Please rename your form controls (including any labels that you refer to in your code)!

Imagine you or another developer comes back to this code in a week/month/years time; they're going to have to work out what Label90 is! If you gave it a more useful name such as lblUserName then your code becomes far easier to maintain.