Solved

generating multiple records into a table from a form

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

Expert Comment

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

Accepted Solution

by:
Rey Obrero 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
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.

 
LVL 119

Expert Comment

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

Expert Comment

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

Expert Comment

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

Expert Comment

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

18 Experts available now in Live!

Get 1:1 Help Now