Creating recurring events and adding records to a table

[Periklis Hristidis]

Hi all,

This is my first attemp to do something serious with access, so pls bear
with me.

I have a table of events called tblRoutes. I have another table called
tblRRoutes, which has exactly the same fields as tblRoutes, but also has the
following fields to indicate frequency of recurrunce:

StartDate Date/Time
EndDate Date/Time
Monday yes/no
Tuesday yes/no
Wednsday yes/no
Thursday yes/no
Friday yes/no
Saturday yes/no
Sunday yes/no
FrequencyCount Number
FrequencyPeriod Text (LookUp in "Days", "Weeks", "Months")

I want to create a rourine (assign it to a button) that calculates the
reccurence and appends records to the table tblRoutes, with
tblRoutes.RouteName=tblRRoutes.RRouteName + calculated date of reccurunce.

Any help would be appreciated.

Thanking you in advance
Periklis

 

[Steve Sanford]

Hi Periklis,

You haven't given enough information - remember we cannot see your MDB.

You want to append all of the fields from a record in table "tblRoutes" to a
record in table "tblRRoutes" PLUS fill in the additional calculated fields in
table "tblRRoutes".

Can you provide examples of records from "tblRoutes" and what the record
should be in "tblRRoutes" for a FrequencyPeriod in "Days", "Weeks" &
"Months"?

 

[Periklis Hristidis]

Hi Steve,

I guess I didn’t explain it correctly.

The table I want to populate (append data to) is tblRoutes.
I am not even sure if there should be a table tblRRoutes, since I don't need
to store the freequence of the recurrence. I use it for ease of use. The
table that holds the data I want to keep is tblRoutes.

The whole point is to keep track of Coach Routes. If the route is a once off
affair, I store all relevent details in tblRoutes. If the route is a
recurring event, than I use tblRRoutes to store the data of the route, plus
the freequency of reccurence.

What I need to do is place a button on the form frmRRoutes, that calculates
the recurrence and appends the data to the table tblRoutes.

For Example:

Data in tblRoutes is as follows
RouteID RouteName RouteStartingDate RouteStartingTime
RouteStartingAddress RouteEndingDate RouteEndingTime RoutePrice
Customer
(autonumber) Siemens 22/07/2008 07:00
22, Rond Str 22/07/2008 09:00
€ 80.00 Siemens SA

If the same route was recurring, than I would put the data in table
tblRRoutes as follows
RID RRName RRSDate RRStime RRSAdd
RREndD RREndT RRPrice Customer MON TUE WED THU
FRI SAT SUN FreCount FrePeriod
(autonum) Siemens 21/07/2008 07:00 22, Bond Str
15/08/2008 09:00 € 80.00 Siemens SA yes no no
no yes no no 0 (null)

this would mean that the route is repeated every Monday and Friday from
21/07/2008 till 15/08/2008. What I want the button to do (the code) is to
calculate all the ocurrances and append them to the table tblRoutes as
follows:

The route is repeated on 21/07/2008, 25/07/2008, 28/07/2008, 01/08/2008,
04/08/2008, 08/08/2008, 11/08/2008 and 15/08/2008

the record that should be appended to the tblRoutes are:

RouteID RouteName RouteStartingDate
RouteStartingTime RouteStartingAddress RouteEndingDate
RouteEndingTime RoutePrice Customer
(autonumber) Siemens 21/07/2008 21/07/2008 07:00
22, Rond Str 21/07/2008 09:00
€ 80.00 Siemens SA
(autonumber) Siemens 25/07/2008 25/07/2008 07:00
22, Rond Str 25/07/2008 09:00
€ 80.00 Siemens SA
(autonumber) Siemens 28/07/2008 28/07/2008 07:00
22, Rond Str 28/07/2008 09:00
€ 80.00 Siemens SA
(autonumber) Siemens 01/08/2008 01/08/2008 07:00
22, Rond Str 01/08/2008 09:00
€ 80.00 Siemens SA
(autonumber) Siemens 04/08/2008 04/08/2008 07:00
22, Rond Str 04/08/2008 09:00
€ 80.00 Siemens SA
(autonumber) Siemens 08/08/2008 08/08/2008 07:00
22, Rond Str 08/08/2008 09:00
€ 80.00 Siemens SA
(autonumber) Siemens 11/08/2008 11/08/2008 07:00
22, Rond Str 11/08/2008 09:00
€ 80.00 Siemens SA
(autonumber) Siemens 15/08/2008 15/08/2008 07:00
22, Rond Str 15/08/2008 09:00
€ 80.00 Siemens SA

Thanking you in advance for your help
Periklis

 

[Steve Sanford]

Well, I would normalize the table(s).

Anyway, the following code is kind of brute force. It only checks for Mon -
Sun, not weekly or monthly.

The code goes in the Click event of the button.

'-------- code beg ----------
'Private Sub Command36_Click()

Dim strSQL As String

Dim RTName As String
Dim STDate
Dim STTime As Date
Dim EDDate As Date
Dim EDTime As Date
Dim RTPrice As Single
Dim RTCustomer As String
Dim DayName As String
Dim NumOfDays As Integer
Dim i As Integer, k As Integer
Dim DoInsert As Boolean 'insert new record

Dim blnMon As Boolean
Dim blnTue As Boolean
Dim blnWed As Boolean
Dim blnThu As Boolean
Dim blnFri As Boolean
Dim blnSat As Boolean
Dim blnSun As Boolean

' get the values and store in variables so don't have
' to keep getting them from the form
RTName = Me.RRName
STDate = Me.RRSDate
STTime = Me.RRStime
EDDate = Me.RREndD
EDTime = Me.RREndT
RTPrice = Me.RRPrice
RTCustomer = Me.Customer
blnMon = Me.MON
blnTue = Me.TUE
blnWed = Me.WED
blnThu = Me.THU
blnFri = Me.FRI
blnSat = Me.SAT
blnSun = Me.SUN

'number of days between start and end dates
NumOfDays = DateDiff("d", STDate, EDDate) + 1
k = 0

For i = 1 To NumOfDays
' basic SQL statement (no pun intended)
strSQL = "INSERT INTO tblRoutes (RouteName,RouteStartingTime,"
strSQL = strSQL & " RouteEndingTime,RoutePrice,Customer,"
strSQL = strSQL & " RouteStartingDate,RouteEndingDate)"
strSQL = strSQL & " Values ('" & RTName & "', #" & STTime & "#,"
strSQL = strSQL & " #" & EDTime & "#, " & RTPrice & ", '" & RTCustomer &
"',"

'MsgBox strSQL

'get the weekday name
DayName = WeekdayName(Weekday(STDate), True)
DoInsert = False

Select Case DayName
Case "MON"
If blnMon Then
strSQL = strSQL & " #" & STDate & "#, #" & STDate & "#);"
DoInsert = True
k = k + 1
End If
Case "TUE"
If blnTue Then
strSQL = strSQL & " #" & STDate & "#, #" & STDate & "#);"
DoInsert = True
k = k + 1
End If
Case "WED"
If blnWed Then
strSQL = strSQL & " #" & STDate & "#, #" & STDate & "#);"
DoInsert = True
k = k + 1
End If
Case "THU"
If blnThu Then
strSQL = strSQL & " #" & STDate & "#, #" & STDate & "#);"
DoInsert = True
k = k + 1
End If
Case "FRI"
If blnFri Then
strSQL = strSQL & " #" & STDate & "#, #" & STDate & "#);"
DoInsert = True
k = k + 1
End If
Case "SAT"
If blnSat Then
strSQL = strSQL & " #" & STDate & "#, #" & STDate & "#);"
DoInsert = True
k = k + 1
End If
Case "SUN"
If blnSun Then
strSQL = strSQL & " #" & STDate & "#, #" & STDate & "#);"
DoInsert = True
k = k + 1
End If
End Select

If DoInsert Then
' MsgBox strSQL

'insert the record
CurrentDb.Execute strSQL
End If

'increment the date
STDate = DateAdd("d", 1, STDate)
Next i

MsgBox "Done!! " & k & " records added."
'End Sub
'-------- code end ----------


HTH

 

[Sean]

Hi,
I'm having a very similar problem, I used this code to tweak my database,
same situation, I have rooms to hire out and would like to be able to make a
recurrable booking.
When implementing the code it inputs into the table as an american date
(mm/dd/yyyy) unless the day of the month is greater than the 12th in which it
switches to the correct UK date format i.e dd/mm/yyyy.
When I step through the code the formatting is fine and also if I input a
date from the form to that field the formatting is fine. Very strange
problem, if anybody could offer assistance it would be greatly appreciated,
thank you.

 

[Douglas J. Steele]

Access does not respect the short date format set through Regional Settings.
Given a date in nn/nn/nnnn format, it will ALWAYS try to interpret that as
mm/dd/yyyy first. It's only if that's an invalid date (i.e. the first two
digits are 13 or higher) that it will then try dd/mm/yyyy format.

Take a look at my September, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

Allen Browne also has a good article about using international dates on his
site http://www.allenbrowne.com/tips.html but the site seems to be down at
the moment so I can't give you the exact URL.

 

[Sean]

Hi Douglas, thanks for the quick reply. I read something similar on another
site. Do you know of a solution or a way that it can be manipulated to
display in the format dd/mm/yyyy regardless of the day of the month. Thank
you once again.

Sean.

 

[John Spencer]

IF you set the FORMAT property to DD/MM/YYYY then the DISPLAY of the date will
always be in day, month, year order.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Sean]

I've tried that, still no joy, I'm trying to put the records directly into
the table, here's my code, hopefully it will shed some light on the
situation, thanks...

'Recurring Events
Private Sub btnReccure_Click()

Dim strSQL As String

Dim RTName As String
Dim STDate
Dim STTime As Date
Dim EDDate As Date
Dim EDTime As Date
Dim RTPrice As Single
Dim RTCustomer As String
Dim DayName As String
Dim NumOfDays As Integer
Dim i As Integer, k As Integer
Dim DoInsert As Boolean 'insert new record

Dim blnMon As Boolean
Dim blnTue As Boolean
Dim blnWed As Boolean
Dim blnThu As Boolean
Dim blnFri As Boolean
Dim blnSat As Boolean
Dim blnSun As Boolean

' get the values and store in variables so don't have to keep getting them
from the form
RTName = Me.txtName
STDate = Me.Date_From
STTime = Me.Time_From
EDDate = Me.Date_To
EDTime = Me.Time_To
blnMon = Me.Recurring_Monday
blnTue = Me.Recurring_Tuesday
blnWed = Me.Recurring_Wednesday
blnThu = Me.Recurring_Thursday
blnFri = Me.Recurring_Friday
blnSat = Me.Recurring_Saturday
blnSun = Me.Recurring_Sunday
Cost = Me.Cost
Room = Me.Room
Event_Summary = Me.Event_Summary
Client1_FirstName = Me.Client1_FirstName
Client1_Surname = Me.Client1_Surname
Client1_AddressLine1 = Me.Client1_AddressLine1
Client1_AddressLine2 = Me.Client1_AddressLine2
Client1_Town = Me.Client1_Town
Client1_PostCode = Me.Client1_PostCode
Client1_Tel1 = Me.Client1_Tel1
Client1_Tel2 = Me.Client1_Tel2
Client1_Fax = Me.Client1_Fax
Client1_EMail = Me.Client1_EMail
Client2_FirstName = Me.Client2_FirstName
Client2_Surname = Me.Client2_Surname
Client2_AddressLine1 = Me.Client2_AddressLine1
Client2_AddressLine2 = Me.Client2_AddressLine2
Client2_Town = Me.Client2_Town
Client2_PostCode = Me.Client2_PostCode
Client2_Tel1 = Me.Client2_Tel1
Client2_Tel2 = Me.Client2_Tel2
Client2_Fax = Me.Client2_Fax
Client2_EMail = Me.Client2_EMail
Number_Guests = Me.Number_Guests
Special_Requirements = Me.Special_Requirements
Catering_Notes = Me.Catering_Notes
Room_Layout = Me.Room_Layout
Booking_Date = Me.Booking_Date

STDate = Format(Date, "dd/mm/yyyy")

'number of days between start and end dates
NumOfDays = DateDiff("d", STDate, EDDate) + 1
k = 0

For i = 1 To NumOfDays
' basic SQL statement
strSQL = "INSERT INTO tblEvent (Name, Recurring_Monday, Recurring_Tuesday,
Recurring_Wednesday, Recurring_Thursday, Recurring_Friday,
Recurring_Saturday, Recurring_Sunday, Time_From, Time_To, Cost, Room,
Event_Summary, Client1_FirstName, Client1_Surname, Client1_AddressLine1,
Client1_AddressLine2, Client1_Town, Client1_PostCode, Client1_Tel1,
Client1_Tel2, Client1_Fax, Client1_EMail, Client2_FirstName, Client2_Surname,
Client2_AddressLine1, Client2_AddressLine2, Client2_Town, Client2_PostCode,
Client2_Tel1, Client2_Tel2, Client2_Fax, Client2_EMail, Number_Guests,
Special_Requirements, Catering_Notes, Room_Layout, Booking_Date, Date_From,
Date_To)"
strSQL = strSQL & " Values ('" & RTName & "', " & blnMon & ", " & blnTue &
", " & blnWed & ", " & blnThu & ", " & blnFri & ", " & blnSat & ", " & blnSun
& ", #" & STTime & "#, #" & EDTime & "#, '" & Cost & "', '" & Room & "', '" &
Event_Summary & "', '" & Client1_FirstName & "', '" & Client1_Surname & "',
'" & Client1_AddressLine1 & "', '" & Client1_AddressLine2 & "', '" &
Client1_Town & "', '" & Client1_PostCode & "', '" & Client1_Tel1 & "', '" &
Client1_Tel2 & "', '" & Client1_Fax & "', '" & Client1_EMail & "', '" &
Client2_FirstName & "', '" & Client2_Surname & "', '" & Client2_AddressLine1
& "', '" & Client2_AddressLine2 & "', '" & Client2_Town & "', '" &
Client2_PostCode & "', '" & Client2_Tel1 & "', '" & Client2_Tel2 & "', '" &
Client2_Fax & "', '" & Client2_EMail & "', '" & Number_Guests & "', '" &
Special_Requirements & "', '" & Catering_Notes & "', '" & Room_Layout & "'"
'MsgBox strSQL

'get the weekday name
DayName = WeekdayName(Weekday(STDate), True)
DoInsert = False

Select Case DayName
Case "MON"
If blnMon Then
strSQL = strSQL & ",'" & Booking_Date & "', #" & STDate & "#, #" & EDDate &
"#);"
DoInsert = True
k = k + 1
End If
Case "TUE"
If blnTue Then
strSQL = strSQL & ",'" & Booking_Date & "', #" & STDate & "#, #" & EDDate &
"#);"
DoInsert = True
k = k + 1
End If
Case "WED"
If blnWed Then
strSQL = strSQL & ",'" & Booking_Date & "', #" & STDate & "#, #" & EDDate &
"#);"
DoInsert = True
k = k + 1
End If
Case "THU"
If blnThu Then
strSQL = strSQL & ",'" & Booking_Date & "', #" & STDate & "#, #" & EDDate &
"#);"
DoInsert = True
k = k + 1
End If
Case "FRI"
If blnFri Then
strSQL = strSQL & ",'" & Booking_Date & "', #" & STDate & "#, #" & EDDate &
"#);"
DoInsert = True
k = k + 1
End If
Case "SAT"
If blnSat Then
strSQL = strSQL & ",'" & Booking_Date & "', #" & STDate & "#, #" & EDDate &
"#);"
DoInsert = True
k = k + 1
End If
Case "SUN"
If blnSun Then
strSQL = strSQL & ",'" & Booking_Date & "', #" & STDate & "#, #" & EDDate &
"#);"
DoInsert = True
k = k + 1
End If
End Select

If DoInsert Then
' MsgBox strSQL

'insert the record
CurrentDb.Execute strSQL
End If

'increment the date
STDate = DateAdd("d", 1, STDate)
Next i

MsgBox "Done! " & k & " records added."


End Sub

 

[Douglas J. Steele]

You MUST use a format Access will recognize in the SQL statement. That
format has nothing to do with the format used to display the date back to
the user.

strSQL = strSQL & ",'" & Booking_Date & "', " & Format(STDate,
"\#yyyy\-mm\-dd\#") & ", " & Format(EDDate, "\#yyyy\-mm\-dd\#")

Did you read the article I cited? (and the specific URL for Allen's article
is http://www.allenbrowne.com/ser-36.html )