Unanswered: retrieve data from table to a label in a form
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?"
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
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.