Auto Generating Records in Table

[Meirav]

I have a table tblWeek with the following fields:
WeekID (AutoNum)
Mon (Date Field)
Tue (Date Field)
Wed (Date Field)
..
..
..
Sun (Date Field)

I would like to have a form with the following parameters:
txtNumberOfWeeks (Number)
txtFromMonDate (Date)

And have a button that when pushed, a program will create records in my
tblWeek for the number of weeks asked and populate the dates from the
txtFromMonDate onwards.

For Example:
txtNumberOfWeeks (Number) = 3
txtFromMonDate (Date) = 06/20/2005

Will create:

WeekIDNo Mon Tue Wed Thu Fri Sat Sun
====== === === === === === === ===
<next no> 06/20/2005 06/21/2005 06/22/2005 ... ... ... 06/26/2005
<next no> 06/27/2005 06/28/2005 06/29/2005 ... ... ... 07/03/2005
<next no> 07/04/2005 07/05/2005 07/06/2005 ... ... ... 07/10/2005

Any Ideas?

 

[Tim Ferguson]

And have a button that when pushed, a program will create records in
my tblWeek for the number of weeks asked and populate the dates from
the txtFromMonDate onwards.

Any Ideas?

Do it in Excel. As stated, this is a spreadsheet question, not a database
one. There is virtually never any need to create dummy empty records; and
never ever ever ever ever ever any need to create fields like MondayDate,
TuesdayDate, WednesdayDate and so on.

What is the problem that this solution is meant to solve?

B wishes


Tim F

 

[laudus]

Function Add_Dates(lngWeekCnt As Long, dteStartDate As Date

Dim rst As ADODB.Recordse
Dim lngAdd_Week As Lon
Dim lngCount As Lon

'Optional check to make sure that the dat
'that was entered is a Monda
If DatePart("w", dteStartDate) <> 2 The
D
dteStartDate = InputBox("The date you entered is not
Monday." &
Chr(10) & "Please enter a date that is a Monday."
Loop Until DatePart("w", dteStartDate) =
End I

Set rst = New ADODB.Recordse
Set rst.ActiveConnection = CurrentProject.Connectio
rst.CursorType = adOpenDynami
rst.LockType = adLockOptimisti
rst.Open "tblWeek

'In case someone enters a zer
If lngWeekCnt = 0 Then Exit Functio

For lngCount = 1 To lngWeekCn
With rs
.AddNe
.Fields(1) = dteStartDat
.Fields(2) = dteStartDate +
.Fields(3) = dteStartDate +
.Fields(4) = dteStartDate +
.Fields(5) = dteStartDate +
.Fields(6) = dteStartDate +
.Fields(7) = dteStartDate +
.Updat
End Wit
dteStartDate = dteStartDate +
Nex
rst.Clos
Set rst = Nothin

End Functio

It worked for me. Hope it helps

Erik Spark

 

[Meirav]

It is part of the design, the reasons are complicated

It was actually a question of VB Programming (not excel since I am using an
ACCESS database), but I was able to do it...

Just created a module that does the work

Thanks for the answer though...

 

[Meirav]

It DOES! Thanks