Results 1 to 3 of 3
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RunSQL not running (2002)

    RunSQL is meant for SQL strings that correspond to an action query, i.e. that modify a table. Your SQL string corresponds to a select query; RunSQL doesn't work with such a string.

    You can use DLookup instead:

    strLevel = DLookup("NameType", "tblSupportStaff", "StaffName=" & Chr(34) & strUserName & Chr(34))
    If strLevel = "Admin" Then
    ...

    Because StaffName is a text field, you must put quotes around the value strUserName; that's what the Chr(34) is for.

  2. #2
    Lounger
    Join Date
    Apr 2002
    Location
    Salem, New Hampshire, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunSQL not running (2002)

    Thanks HansV.

    It works exactly as you suggested.
    And it explains why I could not find a way to assign the SQL result to strLevel. I had found many samples of RunSQL but never saw any comments that SELECT was not an acceptable command. Mystery solved.

    And this would not have happened without your earlier explanation of Environ("UserName").
    Thanks again for your quick and correct support.


    Here is the final code snippet that worked in case anyone else needs an example:
    -----------------------------------------------------------------------------
    Private Sub Form_Load()

    Dim strUserName As String
    Dim strLevel As String

    'Purpose: To change visibility of menu buttons based on User
    'Get system UserName value
    strUserName = Environ("UserName")

    'Get User Security value from tblSupportStaff
    strLevel = DLookup("NameType", "tblSupportStaff", "StaffName=" & Chr(34) & strUserName & Chr(34))

    If strLevel = "admin" Then
    cmdDisplayDatabaseWindow.Visible = True
    Else
    cmdDisplayDatabaseWindow.Visible = False
    End If

    End Sub
    -------------------------------------------------------------------------

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    Salem, New Hampshire, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RunSQL not running SELECT (2003) - SOLVED

    I am trying to allow actions based on a security level stored in tblSupportStaff.
    I am getting the system User Name and want to use that value to get security value from "NameType"field in the table.
    If NameType = "admin" then do some actions, if NameType = "user" do other actions, etc.

    The code is triggered with a form OnLoad.
    The following message comes up:
    "Run Time Error 2342 - A RunSQL action requires an argument consisting of an SQL statement."

    I am working with the following code, but am stuck, I am sure I am leaving out an obvious piece of the puzzle.
    -------------------------------------------------
    Dim strUserName As String
    Dim strLevel As String
    Dim strSQL As String

    strUserName = "'" & Environ("UserName") & "'"

    'strLevel = Get value from tblSupportStaff using next section, expecting "admin" or "user"
    strSQL = "SELECT NameType " & _
    "FROM tblSupportStaff " & _
    "WHERE StaffName = " & strUserName

    DoCmd.RunSQL strSQL

    If strLevel = "admin" Then
    'Do stuff as admin
    End If
    -------------------------------------------
    1. How do I get the result of the RunSQL command assigned to strLevel?
    2. With Debug, I can see that the value of strSQL does translate so that strSQL = "SELECT NameType FROM tblSupportStaff WHERE StaffName = 'Bob'"
    3. Running this now in Debug highlights and stops at DoCmd.RunSQL strSQL

Posting Permissions

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