Ok Keri, try this:
Using Access97, I created an unbound form that contains:
=======================================
A calendar control ("MyCalendar")
2 un-bound text-boxes ("txtStartDate" and "txtEndDate") both "Enabled", but
"Locked", and formatted "Short Date".
(This forces the user to use the calendar control to set the dates ... which
WILL be formatted correctly.)
A Command Button ("cmdAddRecords")
A subform ("sbfFldDate") which is linked to the table ("tblFldDates")
Also... "flddate" in tblFldDates is indexed, Allow duplicates = No
This provision is so that you can not accidentally duplicate existing
entries, yet still allows newer dates.
(ie. if you entered a start date of "01/01/2007" again and changed the end
date to "01/01/2011", it wil not duplicate (Errorcode 3022) the 2007 to 2010
dates , but it WILL add records from 01/02/2010 to 01/01/2011.)
=======================================
Here is the entire code that drives this operation.
=======================================
Option Compare Database
Option Explicit
Dim ctl As Variant
Dim MyStartDate As Date
Dim MyDate As Date
Dim MyEndDate As Date
Dim MyCategory
Private Sub cmdAddRecords_Click()
On Error GoTo ErrHandler
Dim rstAddDates As DAO.Recordset
Set rstAddDates = Me.sbfFldDate.Form.RecordsetClone
MyStartDate = Me.txtStartDate
MyEndDate = Me.txtEndDate
'Let's make sure we have a Start Date and an End Date before proceeding.
If IsDate(MyStartDate) And IsDate(MyEndDate) Then
With rstAddDates ' Doing this will save us a bunch of typing.
'Add the initial values
.AddNew
!flddate = MyStartDate
MyDate = MyStartDate
MyCategory = WeekDay(MyDate, vbMonday)
!Category = MyCategory
.Update
Do Until MyDate = MyEndDate 'Set up a Loop that will continue until
we want it to end.
MyDate = DateAdd("d", 1, MyDate) 'Now add one day at a time
MyCategory = WeekDay(MyDate, vbMonday) 'Gets a "day of week
value"(1 to 5) for the date
If MyCategory 0 And MyCategory < 6 Then 'We only want to
add values 1 thru 5
.AddNew 'This adds the data into the subform's
recordset.
!flddate = MyDate
!Category = MyCategory
.Update
.Bookmark = .LastModified
Me.sbfFldDate.Form.Bookmark = .Bookmark 'This will show
the new records as they are created.
End If
Loop
.Close
End With
Set rstAddDates = Nothing 'Release the recordset object
Else
MsgBox ("Please make sure that both Start and End Dates are provided.")
End If
ErrHandler:
If Err.Number = 3022 Then
Resume Next
Else
Dim Msg
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error"
Exit Sub
End If
End Sub
Private Sub MyCalendar_AfterUpdate()
Select Case ctl
Case 1
Me.txtStartDate.SetFocus
Me![txtStartDate] = Me![MyCalendar]
MyStartDate = Me![MyCalendar]
Case 2
Me.txtEndDate.SetFocus
Me![txtEndDate] = Me![MyCalendar]
MyEndDate = Me![MyCalendar]
Case Else
Exit Sub
End Select
End Sub
Private Sub txtStartDate_Enter()
ctl = 1
End Sub
Private Sub txtEndDate_Enter()
ctl = 2
End Sub
Private Sub txtStartDate_KeyDown(KeyCode As Integer, Shift As Integer)
ctl = 1
MsgBox ("Please Select a date from the Calendar.")
Me.MyCalendar.SetFocus
Me![txtStartDate] = Me.MyCalendar
End Sub
Private Sub txtEndDate_KeyDown(KeyCode As Integer, Shift As Integer)
ctl = 2
MsgBox ("Please Select a date from the Calendar.")
Me.MyCalendar.SetFocus
Me![txtEndDate] = Me.MyCalendar
End Sub
I have tested this, and found that it does what I think you are trying to
do.
HTH,
Don
"Don Leverton" <le****************@telusplanet.netwrote in message
news:xwyNh.17747$x9.101@edtnps89...
Sorry Keri ... I hadn't absorbed what you had said. You want to populate a
table with these values, not use existing data ... right?
If this is the case, then I believe that Larry's code won't do the job
either ... he too is attempting to work with existing data.
Let me ponder on this for a while, and I'll post some new code.
Don.
"Don Leverton" <le****************@telusplanet.netwrote in message
news:4pyNh.17719$x9.2041@edtnps89...
Hi Keri,
Have you looked at the Weekday() function?
Weekday(date, [firstdayofweek])
You can set the "firstdayofweek" to 2, and it should display the results
you
want.
"keri" <ke********@hotmail.comwrote in message
news:11*********************@d57g2000hsg.googlegro ups.com...
Larry,
>
Many thanks for the above. I presume by the day of the week comment
you are referring to Mon being a 1 and Tue being a 2 etc etc, in which
case me needing 6 numbers on Mon - Fri wouldn't work. However if I am
missing the point please correct me as I may be making this difficult
for myself!
>
Anyway the code is super, thanks,
>
