Search code examples
sqlvbams-access

SQL Syntax error running action Query through VBA

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!

Solution

  • 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