By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

Create rows in a subform based on a date range entered by user in the Main

P: 12
I have a list of items to be fabricated, each item is unique, each one has a stardate and enddate and quantity. I created 2 tables: Tbl-1 fields are ItemCode, StartDate, EndDate and PlanQuantity. Tbl-2 fields are ItemCode, PerDay (date), ActualQTY. I created a form with Tbl-1 fields as main form, and Tbl-2 as subform. ItemCode links the parent and child. I need a code that will automatically create rows in subform with daily dates based on the main form date range of startdate and end date, so I can input the ActualQty produced per day.

Appreciate your help, Elizabeth
Aug 15 '12 #1
Share this Question
Share on Google+
10 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,284
Elizabeth,

I am not quite sure I understand your question. Is this what you want to do:

Let's say you have in Tbl-1 ItemCode: 123, StartDate: 14-Aug-2012, EndDate: 03-Sep-2012, PlanQuantity: 300

Do you want Tbl-2 to have the following records:

ItemCode: 123, PerDay: 14-Aug-2012, ActualQty: 15
ItemCode: 123, PerDay: 15-Aug-2012, ActualQty: 12
ItemCode: 123, PerDay: 16-Aug-2012, ActualQty: 18
ItemCode: 123, PerDay: 17-Aug-2012, ActualQty: 21
ItemCode: 123, PerDay: 18-Aug-2012, ActualQty: 7

If this is the case, I would recommend that whenever you add a new ItemCode to your Tbl-1, you append all the necessary records to Tbl-2. You could do this by either building an append query in query builder, that looks to the values on your form for ItemCode, StartDate and EndDate, or you could run a SQL statement that is built with VBA behind the form, using the same data.

Please let me know if this is the direction you are trying to go. If not, I can continue to assist.

Also, please post any attempts you may try that don't work and we can try to troubleshoot your code.

Thanks!
Aug 15 '12 #2

zmbd
Expert Mod 5K+
P: 5,397
Elizabeth - Welcome to Bytes.

Of the links below, you should start with the ""Tutorial for Access"
I have found that most text books and other references do (IMHO) a very poor job helping to set up a database; however, this site does a really good job.

--Z's BOILER PLATE -- just a few links I tend to post quite often:

This is a must read: Posting Guidelines

How to ask good questions

FAQ

A Tutorial for Access

Database Normalization and Table Structures.
Aug 15 '12 #3

P: 12
Thank you for your immediate reply, what I actually needs is the code that will automatically generate the rows with date series in the subform based on the main form startdate and enddate for each ItemCodes, so I can enter in one go ALL daily ActualQTY without missing a day. Each ItemCodes will have different startdate and enddate. One item could be for a period of 3 months, others could be 6 to 12 month.

I tried searching the internet but could not find the code that is suitable for my requirement.

Again, thank you for your help.
Elizabeth

@twinnyfo
Aug 16 '12 #4

zmbd
Expert Mod 5K+
P: 5,397
Elizabeth,
Without seeing your database, this will be difficult to do...
in that there are several ways depending on how you have structured your database.

So if you have structured the tables so that there is a table with items codes and then tables with other information then the code would be written in one manner; whereas, if the database isn't normalized then the code would be completely different.

SO

Using a DB the way I would create it, there would be a table that had just the basic item information and then a separate table that handled say "inventory on hand" with the item id from the item table as 1:M-FK in the inventory table... I would create my Parent and Child forms as per: http://bytes.com/topic/access/insigh...filtering-form based on queries against the two tables then add a command button that added the records to the correct table that the subform is based on and force a requery of the subform once that was done.

Hopefully you can use that as an outline to get started...

-z
Aug 16 '12 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
Elizabeth,

I would start with something as simple as this:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdSetProduction_Click()
  5. On Error GoTo EH
  6.     Dim dbProd As Database
  7.     Dim rstDays As Recordset
  8.     Dim strSQL As String
  9.     Dim dtProdDay As Date
  10.     Set dbProd = CurrentDb()
  11.     strSQL = "SELECT * from Tbl-2;"
  12.     Set rstDays = dbProd.OpenRecordset(strSQL, dbOpenDynaset)
  13.     dtProdDay = Me.txtStartDate
  14.     Do While Not dtProdDay > Me.txtEndDate
  15.         With rstProd
  16.             .AddNew
  17.             !ItemCode = Me.txtItemCode
  18.             !PerDay = dtProdDay
  19.             .Update
  20.         End With
  21.         dtProdDay = dtProdDay + 1
  22.     Loop
  23.     rstDays.Close
  24.     dbProd.Close
  25.     Me.fsubProductionSchedule.Refresh
  26.     Exit Sub
  27. EH:
  28.     MsgBox Err.Number & " " & Err.Description
  29.     Exit Sub
  30. End Sub
  31.  
This assumes there is a command button named cmdSetProduction, and this code is fired with the On Click event. It also assumes a name for your subform.....

This should get you started.
Aug 16 '12 #6

P: 12
Thank you Zmbd & TwinnyFo for your responses. Apologies for responding only now as I was on leave. I tried the code send by TwinnyFo, but i KEEP GETTING THE FOLLOWING ERROR MESSAGE "424 Object Required". I am not quite good with Access codes, any help will be appreciated. Thanks again for your help. Maybe I was not very clear with my query. My Tbl-1 contains the ItemCode (unique for each item), item descirption, TQty (total production quantity for the whole duration), StartDate & EndDate (each ItemCode will have different Startdate and Enddate). Tbl-2 will contain rows of ItemCode,ProdDate (the daily date betweetn StartDate & EndDate for each item), daily production quantity for every item. My parent form will contain all fields from Tble-1, and subform will contain all fields from Tbl-2. ItemCode links the Parent & Child form. I badly need the code that will auto generate new records with daily dates in the subform based on the startdate and enddate of each ItemCode in the Main form. Each itemcode will have different duration of production. I hope this will make it more clear. Once again thank you for your help.
Aug 21 '12 #7

twinnyfo
Expert Mod 2.5K+
P: 3,284
Elizabeth,

I just noticed that I have a typo in my code:

Expand|Select|Wrap|Line Numbers
  1. With rstProd 
  2.  
should actually read:

Expand|Select|Wrap|Line Numbers
  1. With rstDays 
  2.  
I hope this solves the error.....
Aug 21 '12 #8

Rabbit
Expert Mod 10K+
P: 12,366
You can do it without VBA by creating a date table and joining to that for an append query.
Aug 21 '12 #9

P: 12
Thank you ALL for your help. TwinnyFo, the code above is perfecrt, it works. I just change the field names as per my needs. Once again thank you all.

Can I ask you guys for more help. Can you please advise on how to filter and modify the data in subform. I need to filter records in subform by date range to change the ProdQTY. Example I want to filter records from Aug 25 to Sept 24 to edit ProdQTY from 100 to 200. Can you please advise best way to do this?

Thank you and regards, Elizabeth
Aug 22 '12 #10

twinnyfo
Expert Mod 2.5K+
P: 3,284
Elizabeth,

Assuming you have two text fields on your main form, you could add a command button to filter the records on your subform like such:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2. On Error Goto EH
  3.     Me.SubFormName.Form.Filter = "ProdDate >= #" & Me.StartDate & "#" & _
  4.         " AND ProdDate >= #" & Me.StopDate & "#"
  5.     Me.SubFormName.Form.FilterOn = True
  6.     Exit Sub
  7. EH:
  8.     MsgBox Err.Number & " " & Err.Description
  9.     Exit Sub
  10. End Sub
  11.  
I think that should work for you, or get you pointed in the right direction.
Aug 22 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.