Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2013
    Posts
    4

    Unanswered: VBA Insert with variables from Form

    I'm in the process of included soem basic auditing on a form in which each time someone clicks a checked box to indicate in a location is in a low line area (flood risk) a record will be inserted into an audit table indicated who clicked the button, the time and date it was clicked and say whether the button was true or false. I am however getting a prompt asking to enter the values for LowLine_no and LowLine_Yes. Any Ideas?



    Private Sub LowLine_AfterUpdate()

    Dim LowLine_No As String
    Dim LowLine_Yes As String


    LowLine_Yes = "Selected :" & Me.Location.Value
    LowLine_No = "Deselected :" & Me.Location.Value


    DoCmd.OpenQuery "Update Deactivated Landlord"
    DoCmd.Requery


    If Me.LowLine = False Then
    DoCmd****nSQL "INSERT INTO Audit (Username, Activity_Date, Task) Values(Environ('username'), now(), LowLine_Yes );"
    Else
    If Me.LowLine = True Then
    DoCmd****nSQL "INSERT INTO Audit (Username, Activity_Date, Task) Values(Environ('username'), now(), LowLine_No);"
    End If
    End If

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off don't use environ, looks like it does the job, but it is meaningless. instead use the API call (do a google for dev ashish API or look in the code bank) to get the network user logon, you may also care to use another APi call to get the computer ID

    you are gettign th emessage because the SQL engine can not find any reference to the those items and so it assumes is a request for user input. the probel is that you need to drop out of SQL to insert a variable

    Code:
    dim strLowLIne as string
    If Me.LowLine = False Then
      strLowline =  LowLine_Yes
    else
      strLowline =  LowLine_No)
    endif
    
    DoCmd****nSQL "INSERT INTO Audit (Username, Activity_Date, Task) Values(Environ('username'), now(), '" & strLowLine & "';")
    as strLowloine contains alhpanumeric and presumably task is of type string you need to identify where the text starts and stops. Access doens't care which quote method you use you cna use " or ' but they must be in matched pairs. if you need to include a " or ' in your text literal then lookup escape characters
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2013
    Posts
    4
    I assumed it had to with that so I did try ' to identify where the string ended but it wrote the name of variable in the table to oppose to the contents of the variable. In another attempt using

    DoCmd****nSQL "INSERT INTO Audit (Username, Activity_Date, Task) Values(Environ('username'), now()," & LowLine_Yes & ");"

    I got a runtime error '3075' stating a syntax error (missing operator) in query expression. It goes on to give the string of text that should be written in the table 'selected D_CampsOceanGarden23_AptB' in the error message

    I tried your suggestion and it didn't compile. Expected: end of statement compile error prompted.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well there is an uneccesary closing bracket on lowline_no

    or modyfying your current code
    Code:
    DoCmd****nSQL "INSERT INTO Audit (Username, Activity_Date, Task) Values(Environ('username'), now(),'" & LowLine_Yes & "');"
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2013
    Posts
    4
    Thanks I tried that but received a compile error as well. I have already started looking at other ways to get this done. The last thing I tried was

    DoCmd****nSQL "INSERT INTO Audit (Username, Activity_Date, Task) Values(" & User_name & ", now()," & "'Selected :'" & Me.Location & ");"

    but still getting the following error..****n-time error '3075': Syntax error (missing operator) in query expression "Selected:'SD_CampusOcenGardens23_AptB'.

    I also tried writing everything to a string then run the query from there but same error. I just can figure out what part of the syntax is wrong.

    strSQL = "INSERT INTO Audit (Username, Activity_Date, Task) Values(" & User_name & ", now()," & "'Selected :'" & Me.Location & ");"
    DoCmd****nSQL strSQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that doesn't surprise me
    you MUST quote text values to be inserted into the db
    that includes the username AND the selected......

    so instead of
    Code:
    DoCmd****nSQL "INSERT INTO Audit (Username, Activity_Date, Task) Values(" & User_name & ", now()," & "'Selected :'" & Me.Location & ");"
    Code:
    DoCmd****nSQL "INSERT INTO Audit (Username, Activity_Date, Task) Values('" & User_name & "', now() & ", 'Selected :'" & Me.Location & "');"
    you may need to resent the date in # marks

    Code:
    DoCmd****nSQL "INSERT INTO Audit (Username, Activity_Date, Task) Values('" & User_name & "', format(now(),"#yyyy/mm/dd#") & ", 'Selected :" & Me.Location & "');"
    you will find these sort of errors much much easier to diagnose if you examine the actual SQL by assigning the value to a variable then using the variable in the docmd, and set a breakpoint on the variable assignment. what you are doing is showing the VBA that creates the SQL, not the actual SQL. trudst me its a heck of alot easier to debug SQL when you see the actual SQL
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2013
    Posts
    4
    That did it!!!!!!! Thanks you very much. I had " ' instead of ' " after values(. I knew it was a simple syntax error I just couldn't tell the difference as they all look the same with no spaces.

    Thanks again

Tags for this Thread

Posting Permissions

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