this section cut out...
updated code lower down.
this section cut out...
updated code lower down.
Last edited by DaKetch; 03-15-2019 at 01:33 PM.
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.
As Paul saysJust 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.Using a form bound to the table would be a far easier method.
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.
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.
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.
================================================== ===================================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.
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.
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?
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?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.