Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003

    Unanswered: variable reference in sql ?

    Hi again

    I am setting up a user login form and have it checking a table to ensure username and password are correct but in this table each user has 1 of 4 access levels referenced by AccessId


    The msgbox displays the select statement and not the value so how do I get it to perform the sql statement rather than treating it as a string ?

    If Me.txtPassword.Value = DLookup("Password", "tblUsers") Then
    Level = "SELECT [AccessId] FROM [tblUsers] WHERE (([Username]) = me.txtUsername);"
    MsgBox Level, , " My value"
    This was my initial idea I am know working on the following but can't get round the syntax

    If Me.txtPassword.Value = DLookup("Password", "tblUsers") Then
    Level = "SELECT AccessId FROM tblUsers WHERE Username = txtUsername.value"
    DoCmd.RunSQL Level
    MsgBox Level, , " My value"

  2. #2
    Join Date
    Mar 2004

    Re: variable reference in sql ?

    Here is an example using ADO (remember to add the Microsoft ActiveX Data Objects to your database)

    Dim cn as ADODB.Connection
    Dim rs as ADODB.Recordset

    Set cn = new ADODB.Connection

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Test.mdb;Persist Security Info=False"

    set rs = new ADODB.recordset

    rs.actiiveconnection = cn "SELECT AccessId FROM tblUsers WHERE Username = '" & txtUsername.value & "'"

    MsgBox rs.fields("AccessId")



    set rs=nothing
    set cn = nothing

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

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