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?

=?Utf-8?B?TWVpcmF2?= <(E-Mail Removed)> wrote in
news:797F8557-02CC-4653-BB02-(E-Mail Removed):

> 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

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

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...


"Tim Ferguson" wrote:

> =?Utf-8?B?TWVpcmF2?= <(E-Mail Removed)> wrote in
> news:797F8557-02CC-4653-BB02-(E-Mail Removed):
>
> > 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
>
>

It DOES! Thanks

"laudus" wrote:

> Function Add_Dates(lngWeekCnt As Long, dteStartDate As Date)
>
> Dim rst As ADODB.Recordset
> Dim lngAdd_Week As Long
> Dim lngCount As Long
>
> 'Optional check to make sure that the date
> 'that was entered is a Monday
> If DatePart("w", dteStartDate) <> 2 Then
> Do
> dteStartDate = InputBox("The date you entered is not a
> Monday." & _
> Chr(10) & "Please enter a date that is a Monday.")
> Loop Until DatePart("w", dteStartDate) = 2
> End If
>
> Set rst = New ADODB.Recordset
> Set rst.ActiveConnection = CurrentProject.Connection
> rst.CursorType = adOpenDynamic
> rst.LockType = adLockOptimistic
> rst.Open "tblWeek"
>
> 'In case someone enters a zero
> If lngWeekCnt = 0 Then Exit Function
>
> For lngCount = 1 To lngWeekCnt
> With rst
> .AddNew
> .Fields(1) = dteStartDate
> .Fields(2) = dteStartDate + 1
> .Fields(3) = dteStartDate + 2
> .Fields(4) = dteStartDate + 3
> .Fields(5) = dteStartDate + 4
> .Fields(6) = dteStartDate + 5
> .Fields(7) = dteStartDate + 6
> .Update
> End With
> dteStartDate = dteStartDate + 7
> Next
> rst.Close
> Set rst = Nothing
>
> End Function
>
> It worked for me. Hope it helps.
>
> Erik Sparks
>
>