A challaging issue with unbound fields

[bdog2g]

I need some help/advice on how to go about this last hurdle in my database. I'm trying to create a budget forecast array in MS Access. It is based on different tasks TASKID, each task has a start date and a finish date, along with a budget. This is and idea of the array I want to set up.

Date Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02
Task
Task1 $400 $400 $400
Task2 $200 $200 $200 $200
Task3 $300 $300 $300

The monthly break down is obviously aquired from dividing the Task budget by the monthly difference in the start date and the end date. I'm having trouble writing the loop or query that would be need for such a task. Any suggestions would be much appreciated.

 

[MichaelRed]

Why a loop?


StDt = #2/14/02#
EndDt = #4/23/02#
Budget = 900

MnthlyBudget = Budget / (MOnth(EndDt) - Month(StDt) + 1)

? MnthlyBudget
300

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JimHorton]

bdog2g,
Here is what you'd need to do:
Take the calculated monthly avg and add records as shown:

dim db as database, rst as recordset,NumOfMonths as integer,dtLoop as date
Set db = currentdb
'here we assume your destination table is tblForecast
set rstDest = db.OPenRecordset("tblForcast",dbopenDynaset)
'copying from mred's example:
'...although you'd pass these in as arguments, along with Task name
StDt = #2/14/02#
EndDt = #4/23/02#
Budget = 900
MnthlyBudget = Budget / (MOnth(EndDt) - Month(StDt) + 1)
NumOfMonths = datediff("m",stdt,enddt)
dtLoop = stDt
for i = 1 to NumOfMonths
rstd.addnew
rstd!Task = strTaskName 'here, you'd have to pass the taskname in
rstd!Month = dtLoop 'here, we're assuming you only have one entry for month
'.....and we put in the full date, so each month will have the same Day
rstd!Budget = MonthlyBudget
rstd.Update
dtLoop = dateadd("m",1,dtloop) 'increment by one month
next i 'Loop for # of months

Now, a crosstab query will display the results you want
If you need help on passing in the rest of the args, let me know,
--Jim

 

[bdog2g]

Jim

Thank you very much for lookin at my problem, Ima give it a shot and see what happens, I appreciate the help and time

Brian

 

[bdog2g]

Jim

I tried your code however I'm having trouble passing the values for StartDate, EndDate, and MonthlyBudget from another subform on the page. Also I forgot to add that the dates on this table are generated from an overall project date. What I wanted this form to do was fill in the text boxes with the monthly budget where the task date and the project date overlapped. I'm not sure if you can do this, but if it is possible some suggestions are welcomed.


Date Feb-02 Mar-02 Apr-02 May-02 Jun-02 Jul-02
Task
Task1 $400 $400 $400
Task2 $200 $200 $200 $200
Task3 $300 $300 $300

Brian

 

[JimHorton]

bdog2g,
If you can hold off a while, I'll post something tonight, I've got to run to a different client for a while...
--Jim

 

[bdog2g]

Jim

Sure thing. Thank you for takin the time to help

 

[JimHorton]

bdog2g,
So in this table, you have fields with names such as Feb-02, Mar-02, etc, and then a 'Task' field? If this is the case the above code will need to be changed to:

Sub Budget(strTask As String, StDt As Date, EndDt As Date, sTotBud As Single)
'Pass in args from form or whererever
Dim db As Database, rstD As Recordset, iNumOfMonths As Integer, dtLoop As Date
Dim sMnthlyBud As Single
On Error GoTo errBud
Set db = CurrentDb

'here we assume your destination table is tblForecast
Set rstD = db.OpenRecordset("tblForecast", dbOpenDynaset)

'copying from mred's example:
sMnthlyBud = sTotBud / (month(EndDt) - month(StDt) + 1)
iNumOfMonths = DateDiff("m", StDt, EndDt)
dtLoop = StDt
'Each call of the Sub adds one record, it *could* be called from a loop in...
'...a form, say looping a forms recordsetclone, holding stDt, EndDt, Task, Budget
rstD.AddNew
rstD!Task = strTask 'Primary Key?
'loop all Month fields--they MUST exist, we trap error below
For i = 1 To iNumOfMonths
'Here we assume Field Names are UNIFORM, ie "Feb-02" ,etc
rstD(Format(dtLoop, "MMM-YY&quot) = sMnthlyBud
'increment by one month
dtLoop = DateAdd("m", 1, dtLoop)
Next i
rstD.Update

exBud:
Exit Sub
errBud:
If Err.Number = 3265 Then 'item not found
MsgBox "Invalid field at " & dtLoop & " check the table and make sure all fields are defined "
Resume exBud
Else
MsgBox Err.Number & " " & Err.Description
Resume exBud
End If
End Sub

Let me know if this works for you...
--Jim

 

[bdog2g]

Jim

I used a variation of your first code and it gives me the information I want to use in a CrossTab query however, if I cycle through the projects I get an error because the Sub is trying to add the same data again, is there a way to code it so that once the record is written the database doesnt try to write over it with the same data each time you give the Task subform focus. thanks

 

[bdog2g]

Jim

Thanks for the help, I got it working yesterday...come to find out I had the code in the wrong form event...funny how you can waste two days on one thing and fix it 15 min before you get off work