Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    1

    Unanswered: retrieve data from table to a label in a form

    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

  2. #2
    Join Date
    Apr 2012
    Posts
    28
    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

    ...

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •