Solved

generating multiple records into a table from a form

Posted on 2014-01-07
17
38 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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 1

Author Comment

by:RandyTippetts
Comment Utility
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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
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
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
upload a copy of your db
0
 
LVL 1

Author Comment

by:RandyTippetts
Comment Utility
ProductionMetricsW is the frontend I am working on. ProductMetrics_be is the backend.
ProductionMetricsW.mdb
ProductionMetrics-be.mdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
did you change the name of your combo boxes ?
0
 
LVL 1

Author Comment

by:RandyTippetts
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now