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

[ElizabethLOng]

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

[twinnyfo]

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!

[zmbd]

Welcome

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.

[ElizabethLOng]

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

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!

[zmbd]

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

[twinnyfo]

Elizabeth,

I would start with something as simple as this:

Option Compare Database
Option Explicit

Private Sub cmdSetProduction_Click()
On Error GoTo EH
    Dim dbProd As Database
    Dim rstDays As Recordset
    Dim strSQL As String
    Dim dtProdDay As Date
    Set dbProd = CurrentDb()
    strSQL = "SELECT * from Tbl-2;"
    Set rstDays = dbProd.OpenRecordset(strSQL, dbOpenDynaset)
    dtProdDay = Me.txtStartDate
    Do While Not dtProdDay > Me.txtEndDate
        With rstProd
            .AddNew
            !ItemCode = Me.txtItemCode
            !PerDay = dtProdDay
            .Update
        End With
        dtProdDay = dtProdDay + 1
    Loop
    rstDays.Close
    dbProd.Close
    Me.fsubProductionSchedule.Refresh
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub

This assumes there is a command button named cmdSetProductio n, and this code is fired with the On Click event. It also assumes a name for your subform.....

This should get you started.

[ElizabethLOng]

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,ProdDa te (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.

[twinnyfo]

Elizabeth,

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

With rstProd

should actually read:

With rstDays

I hope this solves the error.....

[Rabbit]

You can do it without VBA by creating a date table and joining to that for an append query.

[ElizabethLOng]

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

[twinnyfo]

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:

Private Sub cmdFilter_Click()
On Error Goto EH
    Me.SubFormName.Form.Filter = "ProdDate >= #" & Me.StartDate & "#" & _
        " AND ProdDate >= #" & Me.StopDate & "#"
    Me.SubFormName.Form.FilterOn = True
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub

I think that should work for you, or get you pointed in the right direction.