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

Sunny,

As new member her at UtterAccess, we know that you are working hard to grasp how it works and get it to work for you. For all new-comers to Access, we like to point out the following reading . . . (reading these articles will help you get to know Access better and get from Access as you go)

• Crystal's ACCESS Basics for Programming
• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry's FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Database Answers (free models)
• MSKB 283878: Description of the database normalization basics
• The Evils of Lookup Fields in Tables
• Sharing/Splitting a Database
• Access Tutorial
• Access VBA Tutorial

You've begun a learning journey that will take some time. And reading is one of the better ways to learn a new thing when a tutor is not at your side to assist. THis is just a starter and you can always ask more wuestions. We just like to ensure that we provide great resources to help get you started.

hope this helps

Hi, can you post a mdb version of your database? It would allow us to grasp what you are looking for much easier