Create multiple yearly records using single date criteria

[fitzgeraldwm]

I have a form (frmNewProject)that requests the entry of a starting and an ending date for a project. Within this form is a subform (subfrmBudgetInfo) that allows the user to enter budgetary information for the project by entering the data year by year. Each budgetary information line is stored on a table (tblBudget) that is keyed off by an auto-generated unique number tied to the project table (tblProject). When the user comes to the subform, the data table shows the standard new record entry line.

My question is this:

1. Can the form automatically generate the necessary "n" number of records in tblBudget, where "end date" - "start date" = "n" number of years; and

2. Will this show on the screen immediately for the user to enter/edit the information for each of the generated records?

ex. Project starts 01/01/2000, ends 12/31/2002. I would like the form to show automaticallysubfrmBudgetInfo with Year 1 and Year 2 record lines for editing.

Appreciate any thoughts on this query...

p.s. thanks to all the contributors, the responses have really helped!

wally

 

[scking]

Example Project starts 01/01/2000, ends 12/31/2002.

Public Sub CreateRecords(StartDate As Date, EndDate As Date)

Dim lngSYear As Long
Dim lngEYear As Long
Dim lngYearDiff As Long
Dim lngIter As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String

If EndDate > StartDate Then
Exit Sub
End If

lngSYear = Year(StartDate)
lngEYear = Year(EndDate)

lngYearDiff = (lngEYear-lngSYear) + 1
For lngIter = 1 To lngYearDiff
' Code your create records here
Set db = CurrentDb
strSQL = "SELECT * FROM YourTable;"
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
If rst.RecordCount <> 0 Then
rst.MoveFirst
Else
Exit Sub
End If

Do While Not rst.EOF
'Create something from here
strSQL2 = &quot;SELECT * FROM YourOtherTable;&quot;
Set rst2 = db.OpenRecordset(strSQL2)
rst2.AddNew
rst2.YourField = rst!ThatField
rst2.Update
rst.MoveNext
Loop
Next lngIter

'Don't forget to refresh your form control
Me.WhateverControl.Refresh

End Sub

Steve King Growth follows a healthy professional curiosity

 

[rajeessh]

A small variation of steve code here

Example Project starts 01/01/2000, ends 12/31/2002.

Public Sub CreateRecords(StartDate As Date, EndDate As Date)

Dim lngSYear As Long
Dim lngEYear As Long
Dim lngYearDiff As Long
Dim lngIter As Long
Dim rst As DAO.Recordset
Dim xDate as DateTime

If StartDate > EndDate Then
Exit Sub
End If

lngYearDiff = DateDiff(&quot;yyyy&quot;,StartDate,EndDate)
Set xDate = StartDate
Set rst = Me.<sbFormName>.RecordSetClone
For lngIter = 1 To lngYearDiff
rst.AddNew
rst!<FldNameForDate> = DateAdd(&quot;yyyy&quot;,lngIter,xDate)
rst.Update
Next lngIter
Me.<SbfForm>.Requery
End Sub


Let me know if i am wrong

 

[fitzgeraldwm]

thanks you for the response. i will be trying to implement it this weekend. will let you know if it works and/if i have any questions/problems.

wally

 

[1ooo1]

Just as a matter of interest, where would this code be situated?

Ken

 

[willir]

This is an old thread so I do not know if any one is still reading.

I did something very similar to this for generating user defined check lists. The user would define a check list for a certain task -- eg. the leadership team would define steps for building a new laptop for remote sales. Then the technician would generate the check list and fill in the blanks. Basically the same idea as irongecho's project concept.

Since I did not know if the user was browsing, updating or creating, I used a command button in the header of the continous form to generate the check list after technician select the task. I suppose it could have been done after_update of selecting the task, but I wanted the tech to be certain of their selection before proceeding.