Solved

generating multiple records into a table from a form

Posted on 2014-01-07
17
44 Views
Last Modified: 2016-05-16
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
Next
 
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....
0
Comment
Question by:RandyTippetts
  • 8
  • 7
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39763815
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
Next


to complete your codes, post here the names of your Days checkboxes...
0
 
LVL 1

Author Comment

by:RandyTippetts
ID: 39763898
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39763966
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.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 1

Author Comment

by:RandyTippetts
ID: 39764002
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 skipped.as 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.
0
 
LVL 1

Author Comment

by:RandyTippetts
ID: 39765171
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
Next

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

Exit_AddSched_Click:
    Exit Sub

Err_AddSched_Click:
    MsgBox Err.Description
    Resume Exit_AddSched_Click
    
End Sub

Open in new window


Can you see what I'm missing?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39765198
you have two closing parentheses here

DateAdd('d',1,Me.[StrtDate]))

you can also use this

#" & me.StrtDate + 1 & "#
0
 
LVL 1

Author Comment

by:RandyTippetts
ID: 39765323
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?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39765403
go to the design view of the form and check the names of your combo box for "Operation"
0
 
LVL 1

Author Comment

by:RandyTippetts
ID: 39765429
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39765487
upload a copy of your db
0
 
LVL 1

Author Comment

by:RandyTippetts
ID: 39765508
ProductionMetricsW is the frontend I am working on. ProductMetrics_be is the backend.
ProductionMetricsW.mdb
ProductionMetrics-be.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39765595
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
0
 
LVL 1

Author Comment

by:RandyTippetts
ID: 39766672
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39766818
did you change the name of your combo boxes ?
0
 
LVL 1

Author Comment

by:RandyTippetts
ID: 39779896
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.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question