prepopulate dates in a record using fields from another table

[joebeagle via AccessMonster.com]

I have a table that contains an Id field (autoID), a start date field for a
service and another field that contains the number of weeks the service
should go.

I have another table in which contains an ID field (linked to the table above)
, a date field, and the amount billed for each week the service was
performed.

What I would like to do is find a way to prepopulated the date field in the
second table (for the first record) to start with the date field from the
first table. Then I would like it to prepopulate more date fields for the
number of weeks the service was authorized.

EG:

Service start date=3/1/2007 for 4 weeks.
First record in the second table should show 3/1/07 in the date field
second record should be 3/8/07
Third record should be 3/15/07
Fourth record should be 3/22/07

Is this possible?

 

[Guest]

I believe that you can use the DateAdd function in Access to get at the days
one week apart. If you say DateAdd("ww",1,[InsertDate] you will get the date
one week from InsertDate.

Are you always looking for 4 weeks away?

If so similar code as this will work:

Sub GetDates()
Dim qry As dao.QueryDef
Dim strQuery As String
Dim i As Integer
Dim InsertDate As Date

'create the query, make sure a is a table in your db
Set qry = CurrentDb.CreateQueryDef("", "Select * from a")

'this is the starting date
InsertDate = #3/1/2007#

'loop for each date, you may use a function here to figure out how many
times you want to repeat
For i = 1 To 4
'insert the value to the new table, you can also add other values
strQuery = "INSERT INTO MyTable ( MyField ) SELECT DateAdd('ww',1,"
& InsertDate & ")"
qry.SQL = strQuery
qry.Execute
Next

Set qry = Nothing

End Sub

Please let me know if I can provide more assistance.