Results 1 to 10 of 10
  1. #1
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22

    Trouble running a Currentdb.Execute Command


    this section cut out...

    updated code lower down.
    Attached Thumbnails Attached Thumbnails Annotation 2019-03-14 170652.png  
    Last edited by DaKetch; 03-15-2019 at 01:33 PM.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Use a variable and this method to see the final SQL:

    http://www.baldyweb.com/ImmediateWindow.htm

    For starters, you have to concatenate each variable, some with delimiters based on data type:

    http://www.baldyweb.com/BuildSQL.htm

    Using a form bound to the table would be a far easier method.

    Edit: you also have an issue with spaces, but using the first link should show that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    As Paul says
    Using a form bound to the table would be a far easier method.
    Just have WAH_Shipping_Detail as the recordsource for the form, with the fields in the table bound to the textbox controls. None of that code would be necessary.

  4. #4
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22
    Okay, I've rewritten my code. Here is the full story. I have a parameter query that runs when the Form opens. That query works fine and populates the info circled in red in the below image. Then the user populates the data circled by blue.

    Click image for larger version. 

Name:	Form.png 
Views:	21 
Size:	46.9 KB 
ID:	37857

    Then once the actual shipping info is populated, the user clicks the Add button and the following code runs:

    Public Sub Btn_Add_Record_Click()

    Dim str_query As String

    str_query = "INSERT INTO WAH_Shipping_Detail"
    str_query = str_query & " (WAH1_Roster_ID, WAH1_EID_Network_Login, WAH1_Last_Name,WAH1_First_Name, WAH1_Street_Address"
    str_query = str_query & ", WAH1_City, WAH1_State, WAH1_Zip, WAH1_Time_Zone, WAH1_Cell_Phone, WAH1_Home_Phone, WAH1_Supervisor"
    str_query = str_query & ", WAH1_Outgoing_Tracking_Number, WAH1_Date_Outgoing_Sent, WAH1_Date_Outgoing_Rcvd, WAH1_Return_Shipping_Labels_Required"
    str_query = str_query & ", WAH1_Number_Return_Labels_Required, WAH1_Outbound_Carrier_Service_Type, WAH1_Return Label_Type, WAH1_Signature_Option"
    str_query = str_query & ", WAH1_Sender_eMail, WAH1_O365_eMail_Address, WAH1_Insurance_Required, WAH1_Declared_Value, WAH1_Cost_Center"
    str_query = str_query & ", WAH1_Items_Shipped, WAH1_Items_Shipped, WAH1_Return_Tracking_Number_1, WAH1_Return_Tracking_Number_2"
    str_query = str_query & ", WAH1_Return_Tracking_Number_3, WAH1_Return_Tracking_Number_4, WAH1_Return_Tracking_Number_5"
    str_query = str_query & ", WAH1_Date_Return_Rcvd, WAH1_Issue_Closed, WAH1_mileage)"
    str_query = str_query & " VALUES (Me.EID_Network_Login.Value, Me.Last_Name.Value, Me.First_Name.Value, Me.Street_Address.Value"
    str_query = str_query & ", Me.City.Value, Me.Stte.Value, Me.Zip.Value, Me.Time_Zone.Value, Me.Cell_Phone.Value, Me.Home_Phone.Value"
    str_query = str_query & ", Me.Supervisor.Value, Me.WAH_Outgoing_Tracking_Number.Value, Me.WAH_Date_Outgoing_Sent.Value, Me.WAH_Date_Outgoing_Rcvd.Value"
    str_query = str_query & ", Me.WAH_Return_Shipping_Labels_Required.Value, Me.WAH_Number_Return_Labels_Required.Value, Me.WAH_Return_Carrier_Service_Type.Value"
    str_query = str_query & ", Me.WAH_Return_Label_Type.Value, Me.Combo49.Value, Me.Sender_Mail.Value, Me.O365_eMail_Address.Value, Me.Insurance_Required.Value"
    str_query = str_query & ", Me.WAH_Declared_Value.Value, Me.Cost_Center.Value, Me.WAH_Items_Shipped.Value, Me.WAH_Outbound_Carrier_Service_Type.Value"
    str_query = str_query & ", Me.WAH_Return_Tracking_Number_1.Value, Me.WAH_Return_Tracking_Number_2.Value, Me.WAH_Return_Tracking_Number_3.Value"
    str_query = str_query & ", Me.WAH_Return_Tracking_Number_4.Value, Me.WAH_Return_Tracking_Number_5.Value, Me.WAH_Date_Return_Rcvd.Value"
    str_query = str_query & ", Me.WAH_Issue_Closed.Value, Me.Mileage.Value);"


    Debug.Print str_query


    CurrentDb.Execute str_query


    End Sub

    The Debug.Print statement produces the str_query text (word wrapped due to length):


    INSERT INTO WAH_Shipping_Detail (WAH1_Roster_ID, WAH1_EID_Network_Login, WAH1_Last_Name,WAH1_First_Name, WAH1_Street_Address, WAH1_City, WAH1_State, WAH1_Zip, WAH1_Time_Zone, WAH1_Cell_Phone, WAH1_Home_Phone, WAH1_Supervisor, WAH1_Outgoing_Tracking_Number, WAH1_Date_Outgoing_Sent, WAH1_Date_Outgoing_Rcvd, WAH1_Return_Shipping_Labels_Required, WAH1_Number_Return_Labels_Required, WAH1_Outbound_Carrier_Service_Type, WAH1_Return Label_Type, WAH1_Signature_Option, WAH1_Sender_eMail, WAH1_O365_eMail_Address, WAH1_Insurance_Required, WAH1_Declared_Value, WAH1_Cost_Center, WAH1_Items_Shipped, WAH1_Items_Shipped, WAH1_Return_Tracking_Number_1, WAH1_Return_Tracking_Number_2, WAH1_Return_Tracking_Number_3, WAH1_Return_Tracking_Number_4, WAH1_Return_Tracking_Number_5, WAH1_Date_Return_Rcvd, WAH1_Issue_Closed, WAH1_mileage) VALUES (Me.EID_Network_Login.Value, Me.Last_Name.Value, Me.First_Name.Value, Me.Street_Address.Value, Me.City.Value, Me.Stte.Value, Me.Zip.Value, Me.Time_Zone.Value, Me.Cell_Phone.Value, Me.Home_Phon
    e.Value, Me.Supervisor.Value, Me.WAH_Outgoing_Tracking_Number.Value, Me.WAH_Date_Outgoing_Sent.Value, Me.WAH_Date_Outgoing_Rcvd.Value, Me.WAH_Return_Shipping_Labels_Required.Value, Me.WAH_Number_Return_Labels_Required.Value, Me.WAH_Return_Carrier_Service_Type.Value, Me.WAH_Return_Label_Type.Value, Me.Combo49.Value, Me.Sender_Mail.Value, Me.O365_eMail_Address.Value, Me.Insurance_Required.Value, Me.WAH_Declared_Value.Value, Me.Cost_Center.Value, Me.WAH_Items_Shipped.Value, Me.WAH_Outbound_Carrier_Service_Type.Value, Me.WAH_Return_Tracking_Number_1.Value, Me.WAH_Return_Tracking_Number_2.Value, Me.WAH_Return_Tracking_Number_3.Value, Me.WAH_Return_Tracking_Number_4.Value, Me.WAH_Return_Tracking_Number_5.Value, Me.WAH_Date_Return_Rcvd.Value, Me.WAH_Issue_Closed.Value, Me.Mileage.Value);


    When the execute command is issued, I get the dreaded "Runtime Error '3134' Syntax Error in INSERT INTO statement" popup.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You haven't concatenated the form values into the string, as described in the FAQ I posted. A tiny bit would look like:

    "...VALUES (" & Me.EID_Network_Login.Value & ", " & ...

    that presumes a numeric value, see link for other data types.

    And I'd still use a bound form. You're making things much harder than they need to be.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22

    Bound Form

    Quote Originally Posted by pbaldy View Post
    You haven't concatenated the form values into the string, as described in the FAQ I posted. A tiny bit would look like:

    "...VALUES (" & Me.EID_Network_Login.Value & ", " & ...

    that presumes a numeric value, see link for other data types.

    And I'd still use a bound form. You're making things much harder than they need to be.
    ================================================== ===================================

    The form is bound to the Parameter Query that populates the top half of the form. Is their a way to attach both the Parameter and Append Queries to the same form? I've tried creating a single query using both the the Select table and the Output table, but when I do that and enter the Employee ID to select with, I get all of the matching records from the Output table.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The bottom half could be a subform bound to the output table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22
    Quote Originally Posted by pbaldy View Post
    The bottom half could be a subform bound to the output table.
    Thank you. I have created the subform, attached the fields. I am assuming that I will need to create a button to copy the data in the parent form to the subform and then write to the Detail table. When I enter my Parameter, it will bring up the correct person in the top half of the parent, but if there are any records in the Detail table, it brings them all up. In the subform, there isn't a property setting showing that will make it be for Data Entry. Is there an easy way to do that without making the entire form Data Entry. I need the data in the parent to be there and just enter into the subform.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a graphic of your tables and relationships --make sure tables are extended to show all fields?
    For clarity can you describe the rationale for 5 return labels?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by DaKetch View Post
    Thank you. I have created the subform, attached the fields. I am assuming that I will need to create a button to copy the data in the parent form to the subform and then write to the Detail table. When I enter my Parameter, it will bring up the correct person in the top half of the parent, but if there are any records in the Detail table, it brings them all up. In the subform, there isn't a property setting showing that will make it be for Data Entry. Is there an easy way to do that without making the entire form Data Entry. I need the data in the parent to be there and just enter into the subform.
    Have you tried setting the Data Entry property of the subform to Yes? Or basing it on a query that returns no records, so it's on a new record when it opens?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 10-23-2018, 04:59 AM
  2. RunSQL vs. QueryDef.Execute vs. CurrentDB.Execute
    By caubetiep1337 in forum Queries
    Replies: 8
    Last Post: 12-16-2015, 05:35 PM
  3. Error in CurrentDb.Execute
    By Stefan Moser in forum Access
    Replies: 5
    Last Post: 04-17-2015, 01:38 PM
  4. vba code help - Currentdb.execute
    By joycesolomon in forum Programming
    Replies: 5
    Last Post: 08-11-2014, 11:25 AM
  5. Update sql using currentdb.execute
    By laavista in forum Access
    Replies: 13
    Last Post: 08-15-2011, 03:51 PM

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
  •  
Other Forums: Microsoft Office Forums