retrieve data from table to a label in a form

[sunny_mel2001]

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

 

[RainLover]

This is very difficult to follow.

Suggest that before you go any further you rename your Tables Forms Text boxes etc into something more meaningfull.

eg

tblEmployees

EmployeePK
FirstName
LastName.

etc

The use of Numbers will become so confusing that you won't know if you are Marthur or Arthur.

Also reading up on Normalization will be a great help.

 

[sunny_mel2001]

hi Rain

Thanks for replying.
I have taken your advice and done the following changes. I have now renamed my table as "EmpTable"
This table now looks like:

Question Level Tanya Paul
can you use MS Visio? Top 7 0
can you use MS Visio? Middle 0 4
can you use MS Visio? Junior 0 0


I have also changed the name of the form from "Form5" to "EmpForm" and the code has been altered to:




Option Compare Database

Public EmployeeName As String

' ****the above public variable was created to retrieve the particular user profile (i.e. Tanya, Paul, etc) from the LoginForm.******


Private Sub EmpForm_Load()




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

Set MyDB = CurrentDb()

Str = "SELECT EmpTable.*" & _
"FROM (EmpTable)" & _
"WHERE (EmpTable.(" & Forms![LoginForm][EmployeeName] & ")) > 0" & _
"AND (Table1.Question= '" & Me.Label46.Caption & "')"
' *****Me.Label46.Caption is a label on the EmpForm whose caption is "can you use MS Visio?"

Set rst = MyDB.OpenRecordset(Str)
Me.GetEmpRating.Caption = rst![Forms!LoginForm.EmployeeName] '*****the Forms!LoginForm.EmployeeName should be column Tanya /Paul depending on login
Me.GetEmpLevel.Caption = rst![Level]
rst.close

End Sub


I have tried various changes and combinations of punctuations, quotes and the parentheses - but still no luck. got stuck same as before
Can you please advise?

Thank you,
Sunny

 

[RainLover]

I think you can do all this with a Query.

But I think your Table is designed incorrectly.

Can you post a Database that just has:

The empTable (Which should be tblEmployee as a better name.

The form that you mention together with whatever Tables that the Form refers to.

It would be so much easier this way and quicker rather than asking one question after the other to find out what is what.

Please Post in Access 2003 or earlier. Not everyone has 2007.