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

    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

    Problem

    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 ?

    Code:
    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

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

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    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"

    cn.open

    set rs = new ADODB.recordset

    rs.actiiveconnection = cn

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

    MsgBox rs.fields("AccessId")

    rs.close

    cn.close

    set rs=nothing
    set cn = nothing
    http://www.scirocco.ca/images/banner...occobanner.gif

    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
  •