Results 1 to 7 of 7
  1. #1
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    117

    Query Format

    Hey guys,

    My co worker set up this query for me in code to append, I really just need help in the format.



    Here is the query that's gets appended to a table after clicking a button.

    q = "INSERT INTO ApproverQueue (TR_id, clock) VALUES (" & TR_ID & ",'" & Approver_Clock & "')"

    There were only 2 fields in this approverqueue table and my boss wants me to add more so I think I got the first part down of adding the fields to the table in the code:


    q = "INSERT INTO ApproverQueue (TR_id, clock, model, Last_Name, First_Name, Test_Title, GTSR_ID) VALUES (" & TR_ID & ",'" & Approver_Clock & "')"

    Now after the values from the form that will get added to table go in the second part but I don't know how to format is with all the quotation marks and everything because I am not really familiar and I know I won't get it right. If somebody can just help me format the second part after values in the code would be really helpful. Thanks!

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The first thing to do is create a query using query design. For each of the fields add something, 1 for numeric fields and "A" for text fields (for instance). Then copy the SQL to your VBA.

    Once in VBA, change the double quotes to single quotes, so now for all the text fields it will say 'A' and the numeric fields will be plain 1. Then replace the values with values from your form, so that each of the A's and each of the 1's become double-quote & field_from_form & double-quote

  3. #3
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    117
    Don't know how to really do that, can somebody just format it for me on this forum by plugging the fields after the value with all the quotation marks and everything in place?

    Would really appreciate that!

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    We can't do what you ask given what you posted. What's missing is, where do all the extra values come from that you're trying to add? You need to understand and learn how to concatenate, which is what your coworker did. Consider everything in what you were given and take it in chunks between the double quotation marks:

    "INSERT INTO ApproverQueue (TR_id, clock) VALUES (" & TR_ID & ",'" & Approver_Clock & "')"

    The ampersand (&) is used to concatenate text (like + sign for numbers) but it doesn't become part of the string. I will use the + sign only to show where the concatenation takes place:

    INSERT INTO ApproverQueue (TR_id, clock) VALUES ( + TR_ID + ,' + Approver_Clock + ')
    The beginning and ending double quotes serve to 'wrap' the entire string within double quotes. Properly written, there should always be related pairs. So Access receives the above as

    "INSERT INTO ApproverQueue (TR_id, clock) VALUES (TR_ID ,'Approver_Clock')"

    IF a value being passed is text data type (which your statement tells me Approver_Clock is text), the value must be in quotes. Simply adding a single double quote character (") at that point will cause an error since it will look like the construct is terminated somewhere that you don't want it to be. While there are other ways of handling this, nesting single quote marks within double quote marks is the easiest to handle and read.

    Lastly, where you intend to use this matters greatly. If in a vba procedure, you might get away with referring to TR_ID like that, although I consider it to be risky and somewhat sloppy as it could refer to a field or control or variable. Hopefully Access gets it right. If this is being constructed to use in a query, or a code procedure that creates a query for the query collection, then it will never work. The query side of Access and the side that contains vba code, forms and reports aren't fully exposed to each other. A reference like Forms!frmYourFormName.YourControlName might be needed.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    banpreet is offline Competent Performer
    Windows 8 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    117
    Hey guys, I put this and I get this message, "Sntax error in InESERT INTO statement.
    Heres the code:


    Public Function UpdateApproversForTRID(Model_Name As String, TR_ID As Integer)
    Dim Email As String
    Dim rst As New ADODB.Recordset

    strSQL = "SELECT clock FROM tblApproversByModel WHERE model_Name='" & Model_Name & "'"
    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

    If rst.RecordCount > 0 Then

    Do Until rst.EOF
    Approver_Clock = rst.Fields("clock")

    ' We have the ID from the Approvers for which model it was
    ' Insert this into the table "ApproverQueue"

    DoCmd.SetWarnings False

    q = "INSERT INTO ApproverQueue (TR_id, clock, GTSR_ID, Requestor_First, Requestor_Last, Test_Title, Date, Model) VALUES (" & TR_ID & ",'" & Approver_Clock & ",'" & GTSR_ID & ",'" & Requestor_FName & ",'" & Requestor_LName & ",'" & Test_Title & ",'" & Date & ",'" & cboACModel & "')"
    'Debug.Print q
    DoCmd.RunSQL q THIS IS WHERE THE CODE GETS HIGHLIGHTED, I Don't understand why


    DoCmd.SetWarnings True

    rst.MoveNext
    Loop

    End If


    rst.Close
    Set rst = Nothing


    End Function


    Would be glad if somebody would help me figure this out now.

    Thanks!

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Immediately before the DoCmd.Runsql q, add this: Debug.Print q
    Put a breakpoint on the DoCmd.Runsql so that the execution stops there (click on the margin to the left of the line)
    The SQL statement as it is created will be displayed in the immediate window. Copy and paste that SQL into a new query design and try and run it. You will be able to see any errors there, fix them, get the query working, then make the same changes in your code.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You will find that a double quote is on the wrong side of the ampersand, plus missing a single quote.
    ...'" & Approver_Clock & "
    ...'" & Approver_Clock'" &

    The problem is repeated several times.
    The suggestion is a good one.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-30-2016, 10:27 AM
  2. Replies: 0
    Last Post: 11-19-2014, 05:47 AM
  3. Replies: 3
    Last Post: 09-07-2013, 04:59 PM
  4. Replies: 4
    Last Post: 03-12-2013, 06:49 PM
  5. Conditional Format Query
    By Schwagr in forum Queries
    Replies: 3
    Last Post: 03-20-2006, 02:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums