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]
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