Automation - Create billing periods for project duration

[leontyukv]

Hi,

I have created several Access database and even wrote some basic VBA code for form functionality. But I have a VBA automation requirement that is beyond my knowledge.

I developed a finance tracker database for tracking project revenues and costs (forecast and actual amounts). I have a form where we enter a new Work Order in the DB. This Work Order form has a continous subform where we create all the billing periods needed for the life of the Work Order. Once the billing periods are created (opened) we can then add our revenue and cost forecasts for each period. We have a Billing Period lookup table that has our billing periods with their respective start and end dates (which usually begin around the last week of a month and end about 3 weeks into the next month).

For the purpose of this question, lets say we only enter the required WO_Number and WBS_Code (Composite key) and the Work Order Start_Date and End_Date. I want to click a button to runs some code to automatically create all the billing periods for which their start and end dates fall into the Work Order start and end dates.

For example, say a Work Order starts on 5/1/2013 and ends on 7/31/2013

The billing period dates in the Billing Periods lookup table are as follows:
May-13 ---> 4/22/2013 - 5/19/2013
Jun-13 ---> 5/20/2013 - 6/23/2013
Jul-13 ---> 6/24/2013 - 7/21/2013
Aug-13 ---> 7/22/2013 - 8/18/2013

Then we would need the following billing periods created in the Work Order subform:
May-13
Jun-13
Jul-13
Aug-13

I don't even know where to start on this. Is it possible to automate this process with the setup I have? If so, how would I structure the VBA code/logic to use the billing period lookup table and create entries in the subform for all the billing periods that fall into the duration of the Work Order.

The attached DB is a stripped down version with only the tables and forms needed for this problem.

Any help, pointers or examples would be greatly appreciated.

Please and thanks in advance!

Viktor

 

[TJPoorman]

You could add a button to your form and place the following code in the On_Click Event:

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO WO_FORECAST (Bill_Period, WO_Number, WBS_Code)" & _
        " SELECT BILLING_PERIODS.Bill_Period, " & Me.WO_Number & ", '" & Me.WBS_Code & "'" & _
        " FROM BILLING_PERIODS" & _
        " WHERE (((#" & Me.Start_Date & "#)<[Period_Start]) AND ((#" & Me.End_Date & "#)<[Period_End]));"
DoCmd.SetWarnings True

 

[leontyukv]

TJPoorman,

Thank you so much for that VBA/SQL syntax. I would not have figured it out without your help. I ran do some test queries to get correct billing periods for my date range. So I change the WHERE clause to get exactly what I needed. Here is the final bit of code I used to get the job done. Works very well.

Private Sub Create_BPs_Click()
[INDENT]DoCmd.SetWarnings False
 
DoCmd.RunSQL "INSERT INTO WO_FORECAST (Bill_Period, WO_Number, WBS_Code)" & _
        " SELECT BILLING_PERIODS.Bill_Period, " & Me.WO_Number & ", '" & Me.WBS_Code & "'" & _
        " FROM BILLING_PERIODS" & _
        " WHERE (((#" & Me.End_Date & "#)>=[Period_Start]) AND ((#" & Me.Start_Date & "#)<=[Period_End]));"
 
DoCmd.SetWarnings True
 
Me.sfrmWO_ForecastNew.Form.Requery
 
[/INDENT]End Sub

Thanks again

Viktor