SQL Syntax error running action Query through VBA-CodePudding

I am trying to build this query through VBA instead of building it in Access and running a docmd.openquery. That seemed to me like the easier route, but I wanted to work on my SQL. Obviously that didn't work as intended if I am here lol. So, I am trying to take the Date values of 14 text boxes on our JobTicket form and insert them into another table, Tbl_Schedule. This table is not a part of the Query that is the record source for the JobTicket form. I am worried that attempting to add this table in will overload the Query, as it is already very full. When I try to quickly navigate to the last field in that Query the text writes on top of itself, and then Access goes not responding while it clears up the text and loads the last couple fields. Adding another 56 fields to that seems like a recipe for disaster. I will post the SQL I have written below.

DoCmd.RunSQL "INSERT INTO Tbl_Schedule (Date_Scheduled1, Date_Scheduled2, Date_Scheduled3, Date_Scheduled4, Date_Scheduled5, Date_Scheduled6, Date_Scheduled7, " & _
                                      "(Date_Scheduled8, Date_Scheduled9, Date_Scheduled10, Date_Scheduled11, Date_Scheduled12, Date_Scheduled13, Date_Scheduled14)" & _
             "VALUES (#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled1_JobTicket] & "#,#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled2_JobTicket] & "#, " & _
                    "(#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled3_JobTicket] & "#,#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled4_JobTicket] & "#, " & _
                    "(#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled5_JobTicket] & "#,#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled6_JobTicket] & "#, " & _
                    "(#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled7_JobTicket] & "#,#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled8_JobTicket] & "#, " & _
                    "(#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled9_JobTicket] & "#,#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled10_JobTicket] & "#, " & _
                    "(#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled11_JobTicket] & "#,#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled12_JobTicket] & "#, " & _
                    "(#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled13_JobTicket] & "#,#" & [Forms]![Frm_JobTicket]![Txt_DateScheduled14_JobTicket] & "#)"

Table being inserted into: Tbl_Schedule

Fields being inserted into: Date_Scheduled1 -to- Date_Scheduled14

Getting data from text boxes: Txt_DateScheduled1_JobTicket -to- Txt_DateScheduled14_JobTicket on Frm_JobTicket

Any other questions that would assist you in assisting me please feel free to ask! Thanks in advance!

CodePudding user response：

Dynamic SQL has its uses, but this is not one of them.

Using DAO methods makes your code so much simpler and easier to read and debug.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frm As Access.Form

' for readability
Set frm = Forms!Frm_JobTicket

' open table for adding record(s)
Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_Schedule", dbOpenDynaset, dbAppendOnly)

rs.AddNew
rs!Date_Scheduled1 = frm!Txt_DateScheduled1_JobTicket
rs!Date_Scheduled2 = frm!Txt_DateScheduled2_JobTicket
' etc.
rs.Update
rs.Close

With enumerated field names like these, you can also use a loop:

Dim i As Long

rs.AddNew
For i = 1 To 14
    rs("Date_Scheduled" & i) = frm("Txt_DateScheduled" & i & "_JobTicket")
Next i
rs.Update

CodePudding user response：

This is a good opportunity to consider normalizing your data so that part of your problem is removed entirely. Instead of having DateScheduled1_JobTicket, DateScheduled2_JobTicket etc., it might be better to have another table which fills vertically instead of horizontally, perhaps with fields like ID, Item, JobTicketNumber, ScheduledDate.

Then, fill this table with a row for each item/sku/product, and date. You'll have 14 rows for scheduled tickets for each item/sku/product instead of 14 columns, and this will also solve your future problem of adding 56 fields. The benefit is that you can present the job ticket schedule rows by using continuous forms (in a list). Even better, you can put this continuous form with dates as a subform on your item/sku/product main form, which will then show as a neat list of scheduled tickets that will automatically change as you scroll through item/sku/products.

If you don't use continuous forms, you can still use an unbound approach as you're using now. One benefit is that it will be much easier when you need to add future JobTicket numbers, since you can just add more rows instead of adding fields and having to do additional design work.

If you want to view data in the flattened way that you built your table, you can use a Crosstab query to present it as you have in your table, but the underlying data will be much better stored in a normalized format.

Note that you don't need to concatenate a string as you did above; just leave the Forms!Form!Control reference expression directly in the query and you have a nice parameterized query that will execute just fine, so long as there are dates in those controls (text box, drop down etc).

ex.

Insert Into (MyDateField) Values (Forms!MyForm!MyDateControl);

No dynamic SQL needed.