Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Unhappy Unanswered: Database connectivity in VBA Excel 2007

    HI I ma trying to fetch employee id with the below code but getting error "Run time Error 91, Object variable or with block variable is not set "

    Here is my code


    Private Sub UserForm_Initialize()
    user_name = VBA.Environ("USERNAME")
    txt_User_Name.Text = UCase(user_name)
    Sql_String = "Select * from Employee_Detail where Emp_login_ID = " & user_name
    'Set cmd = New ADODB.Command
    Set rs = New ADODB.Recordset
    'rs.Open (sql_string,con,adOpenDynamic ,adLockBatchOptimistic ,-1)
    Call con_open_close
    rs.Open Sql_string, con
    emp_id = rs.Fields(0)
    user_name = txt_User_Name.Text
    txt_emp_id.Text = emp_id
    End Sub

    Sub con_open_close()
    Set con = New ADODB.Connection
    'Set rs = New ADODB.Recordset
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mruhullah\Documents\Excel Project\Leave Tracker\AU_leave_tracker.accdb;Jet OLEDBatabase ;"


    End Sub


    Can anyone help me to resolve this issue????????????

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Which line results in an error? You realize that your UserForm_Initialize knows nothing about the "con" object that you declare in con_open_close routine?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jan 2011
    Posts
    2

    Red face Getting error in record set line

    Thanks for your reply.

    I checked and found that the error is in record set line

    Private Sub UserForm_Initialize()
    user_name = VBA.Environ("USERNAME")
    txt_User_Name.Text = UCase(user_name)
    txt_User_Name.Enabled = False
    txt_emp_id.Enabled = False
    Sql_String = "Select * from Employee_Detail where Emp_login_ID = " & user_name
    Set cmd = New ADODB.Command
    rs.Open "select * from Employee_Detail where Emp_login_ID = " & user_name, con ' GETTING ERROR IN THIS LINE

    emp_id = rs.Fields(0)
    user_name = txt_User_Name.Text
    txt_emp_id.Text = emp_id
    lbl_welcome_msg.Caption = "Welcome " & rs.Fields(2) & ". Please click on GO button to begin......"
    lbl_welcome_msg.TextAlign = fmTextAlignCenter
    lbl_welcome_msg.AutoSize = False
    Frame_Leave_Option.Visible = False
    End Sub



    Sub con_open_close()
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mruhullah\Documents\Excel Project\Leave Tracker\AU_leave_tracker.accdb;Jet OLEDBatabase Password=goodfriday;"
    Set rs = New ADODB.Recordset

    End Sub

Posting Permissions

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