The syntax on this has me completely stumped.
I have created a form that collects the following information.
Beginning date: (Calendar date selection called "begin")
Ending Date: (Calendar date selection called "Ending")
Product: (Combo box to select called "product")
Daily Quantity: (unbound text box called "Qty")
In addition I have seven check boxes labelled Mon, Tue, Wed....etc.
What I am trying to do is create a number of records to a table called "schedule" that is based on the datediff between start and end dates, also checking to see if the day of the week is checked and skipping that record if not but incrementing the date regardless.
All the the data being written to the table is static for each record with the exception of the date increment. EXAMPLE:
This should create a total of five records in the "Schedule" table, dated 1/7, 1/8, 1/9, 1/13, and 1/14. All with the product and quantity as listed.
I have queried the form and the data is correct and accessible. I can create the variables the incrementing the date and the conditionals for determining if the record should be created for a date.
I am using INSERT INTO with a a for/next loop the write the records but I can't get the syntax right. Here is an example without the day of week check:
Dim numDays As Integer
Dim intCounter As Integer
Dim strSQL As String
numDays = DateDiff("d",[Forms]![ScheduleEntry]![begin],[Forms]![ScheduleEntry]![ending])
For intCounter = 0 To numDays
strSQL = "INSERT INTO Schedule ([Product],[Component],[Operation],[Date],[Qty]) VALUES (" & Me![Product] & "," & Me![Component] & "," & Me![Operation] & ", #" & CDate(Me![StrtDate] & intCounter)) & "#, " & Me![Qty] & ")"
CurrentDb.Execute strSQL, dbFailOnError
MsgBox (numDays + 1) & " Records have been added to the Table [TmpSchedule]", _
vbInformation, "Append Data"
At this point my brain has gone to scrambled eggs and I have lost track of the flow.
Can anyone put me on track or show me a better way to achieve the result I need?