generating multiple records into a table from a form

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")
Component: same
Operation: same
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:
Form for requesting data
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"

Open in new window

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?

Thank you....
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
what are the rowsource of you combo boxes

if the type of data is text for [Product] the syntax is  '" &  Me.Product & "'
if the type of data is text for [Component] the syntax is '" &  Me.Component & "'
if the type of data is text for [Operation] the syntax is '" &  Me.Operation & "'

For intCounter = 0 To numDays
  strSQL = "INSERT INTO Schedule ([Product],[Component],[Operation],[Date],[Qty]) VALUES ('" &  Me.Product & "','" &  Me.Component & "','" &  Me.Operation & "', #" & DateAdd('d',1,Me.[StrtDate])) & "#, " &  Me.Qty & ")"
    CurrentDb.Execute strSQL, dbFailOnError

to complete your codes, post here the names of your Days checkboxes...
RandyTippettsAuthor Commented:
The names of my day check boxes are "Mon", "Tue", "Wed", etc...  The row source for Product references a table called product. The row source for components references a query that only shows components related to that product. Called "ComponentActiveOnly". operation references the table called operation. All of those variables show up in the form. They are all texts. thank you for your input. I will take a closer look at your code when I get to a computer.
Rey Obrero (Capricorn1)Commented:
also explain why the dates 1/7, 1/8, 1/9, 1/13, and 1/14 will be used as the dates for records added.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

RandyTippettsAuthor Commented:
Based on the start date of 1/7, a Tuesday. Tuesday is checked so a record is created. Same with Wednesday and Thursday. Friday through Sunday are not checked so the date increments up to the ending date looking at each day to see of marked. If marked a record is written, if not marked the day is it cycles through based on that date range, it should have produced records for just those dates. But as I said my brain is a little scrambled it might not be accurate.
RandyTippettsAuthor Commented:
Hi Capricorn1;

I can't see a single thing wrong with the code you posted above but I am getting a syntax error on the "INSERT INTO" line. I don't fully understand all that the line produces but the quotes, commas, and parens balance out.
Private Sub AddSched_Click()
On Error GoTo Err_AddSched_Click

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 & "', #" & DateAdd('d',1,Me.[StrtDate])) & "#, " &  Me.Qty & ")"
    CurrentDb.Execute strSQL, dbFailOnError

MsgBox (numDays + 1) & " Records have been added to the Table [Schedule]", _
       vbInformation, "Append Data"

    Exit Sub

    MsgBox Err.Description
    Resume Exit_AddSched_Click
End Sub

Open in new window

Can you see what I'm missing?
Rey Obrero (Capricorn1)Commented:
you have two closing parentheses here


you can also use this

#" & me.StrtDate + 1 & "#

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RandyTippettsAuthor Commented:
Removing that paren created another syntax error but using your alternative seemed to work.

But now I have a new and unfamiliar error. "Method or data member not found". It is listing it in the me.operation section. I verified the control and used intellisense to select it but still receive the same error.

Chasing my tail maybe....
I can a compact and repair hoping to clear the error but no joy.

Am I taking the wrong approach on what I am trying to accomplish?
Rey Obrero (Capricorn1)Commented:
go to the design view of the form and check the names of your combo box for "Operation"
RandyTippettsAuthor Commented:
That's the first thing I checked. That's why none of this makes sense. I know Access can be tempramental about sequence and syntax but I just don't see the problem.

Operation Combo box name.
Rey Obrero (Capricorn1)Commented:
upload a copy of your db
RandyTippettsAuthor Commented:
ProductionMetricsW is the frontend I am working on. ProductMetrics_be is the backend.
Rey Obrero (Capricorn1)Commented:
there are a lot of errors in your db,  correct them first by going to the VBA window

Debug > Compile
correct any errors raised.

then do  a compact and repair

Note: it will be better if you will name your controls using the  Leszynski naming convention
look for it in google
RandyTippettsAuthor Commented:
There was a hidden form "~" that was apparently hanging out there. It looked like a system generated temp form. I deleted it. I am able to Debug/Compile fine if I comment out the INSERT INTO line but attempting to do so with the line active reports the same error.
Rey Obrero (Capricorn1)Commented:
did you change the name of your combo boxes ?
RandyTippettsAuthor Commented:
Sorry for the delay. We had the brass in for a few days which kept me hopping on other issues. I am renaming the combo boxes now and will report the results. I am curious as to why a name could make a difference though.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.