Solved

generating multiple records into a table from a form

Posted on 2014-01-07
17
48 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

821 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