Jon wrote:
I have a form that is to be used to populate a table (tblBookings) of future
Bookings for a chosen weekday - that is selected by a choice of Checkbox's
(so they can have one or more days).
The user also chooses what year, the dates will be every chosen weekday for
that year, (from 1/1 if not this year, from today if this year).
I need to create a routine for each Weekday, but I can't figure all of the
looping and weekday bits!
So far I have:
Dim MonSQL As String
'Monday
If Me.Monday.Value = True Then
'populate all the entries
MonSQL = "INSERT INTO tblBookings ( CollectionDate, CollectionTime ) VALUES
(" _
& "#" _
& ThisWillBeTheCalculatedDateBit _
& "#" _
& ", '" & Me.CollectionTime _
& "' );"
DoCmd.RunSQL MonSQL
End If
Can anyone either advise on the correct way to loop through this and choose,
for this bit, the Mondays.
Or is there a better way to do this?
In anticipation, many thanks
Jon
You can cut and paste the following code into a module. Change the year
(intYear) and day (strDay) and run it. Look in the debug window for the
results. If acceptable, change strDay and intYear method to use the
values from the input form, comment out the Debug.Print, and uncomment
the method to add the records.
Sub CheckDate()
Dim intYear As Integer 'year entered
Dim intDay As Integer 'acSunday to acSaturday value
Dim intWeekday As Integer
Dim strDay As String 'day selected
Dim datHold As Date
'change these values for testing
strDay = "Tuesday" 'this will be from checkbox of day selected
intYear = 2007 'year selected by operator
Select Case strDay
Case "Sunday"
intDay = 1 'acSunday value...and so on
Case "Monday"
intDay = 2
Case "Tuesday"
intDay = 3
Case "Wednesday"
intDay = 4
Case "Thursday"
intDay = 5
Case "Friday"
intDay = 6
Case "Saturday"
intDay = 7
End Select
If intYear = Year(Date) Then
'get current date
datHold = Date
Else
'get the first of the year
datHold = DateSerial(intYear, 1, 1)
End If
'determine the day of the week respective of the day to use
intWeekday = WeekDay(datHold, intDay)
If intWeekday <> 1 Then
datHold = datHold + (8 - intWeekday)
End If
'what is the calculated date?
'see if the day and date match. comment out if acceptable
MsgBox Format(datHold, "Long Date")
'now run subroutine to insert dates
If Year(datHold) = intYear Then
'it's possible someone wants to add dates for this year
'around Christmas time and the next date for day specified
'would go into next year and that would not be correct
'uncomment out if msgbox above is acceptable
'AddDatesToTable datHold, intYear
End If
End Sub
Private Function AddDatesToTable(datLoop As Date, intYear As Integer)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblBookings", dbOpenDynaset)
Do While Year(datLoop) = intYear
'use debug to see which days will be inserted. Comment
'out debug line if not in test mode
Debug.Print datLoop
'update the record. uncomment to update
'rst.AddNew
'rst!CollectionDate = datLoop
'rst!CollectionTime = Me.CollectionTime
'rst.Update
'now add 7 days and loop
datLoop = datLoop + 7
Loop
rst.Close
Set rst = Nothing
End Function