J
James Frater
Morning Gang,
I look after events and have a do...loop piece of code to create block
bookings (please see at the bottom). As part of this do...loop it gives each
individual event in the block a consecutive "episode" number. So, for one of
my rugby teams who book the rugby pitch in block of 7 weeks they get 8
consecutive episodes:
Episode Date
1 18/01/2009
2 25/01/2009
3 01/02/2009
4 08/02/2009
5 15/02/2009
6 22/02/2009
7 01/03/2009
8 08/03/2009
However, people often cancel an indivdual event or, more often than not,
request additional event. So the Rugby team has their block of 8 events, but
add in a few more events.
Episode Date
1 18/01/2009
2 25/01/2009
3 01/02/2009
04/02/2009
4 08/02/2009
5 15/02/2009
16/02/2009
19/02/2009
6 22/02/2009
7 01/03/2009
8 08/03/2009
So, what do I need to do to update the episode numbers to run from 1-11.
I've tried an UPDATE SQL piece of code (please see below) but can't get it to
work, any ideas?
Regards
JAMES
UPDATE SQL code
===============
Private Sub btnEpisodeRefresh_Click()
Do
intcounter = 0
intcounter = intcounter + 1
DoCmd.RunSQL "UPDATE tblEvent" & _
"SET [episode] = intcounter " & _
"WHERE [block] = " & Me.blockID
Exit Do
Loop
End Sub
Do...loop code
=============
Set dbs = CurrentDb()
Set rstprogramme = dbs.OpenRecordset("tblEvent", dbOpenDynaset)
intcounter = 0
intstartdate = DateAdd("d", -7, Me![startdate])
Do While intcounter <= Me![TotalWeeks]
intcounter = intcounter + 1
intstartdate = DateAdd("d", 7, intstartdate)
rstEvent.AddNew
rstEvent!Episode = intcounter
rstEvent!EventName = Me![EventName]
rstEvent!EventStart = intstartdate
rstEvent!SportType = Me![sporttypeid]
rstEvent!EventLoc = Me![LocationName]
rstEvent!EventBlock = Me![BlockID]
rstEvent!EventOn = Me![StartTime]
rstEvent!EventOff = Me![EndTime]
rstEvent.Update
If intcounter = Me![TotalWeeks] + 1 Then
Exit Do
End If
Loop
I look after events and have a do...loop piece of code to create block
bookings (please see at the bottom). As part of this do...loop it gives each
individual event in the block a consecutive "episode" number. So, for one of
my rugby teams who book the rugby pitch in block of 7 weeks they get 8
consecutive episodes:
Episode Date
1 18/01/2009
2 25/01/2009
3 01/02/2009
4 08/02/2009
5 15/02/2009
6 22/02/2009
7 01/03/2009
8 08/03/2009
However, people often cancel an indivdual event or, more often than not,
request additional event. So the Rugby team has their block of 8 events, but
add in a few more events.
Episode Date
1 18/01/2009
2 25/01/2009
3 01/02/2009
04/02/2009
4 08/02/2009
5 15/02/2009
16/02/2009
19/02/2009
6 22/02/2009
7 01/03/2009
8 08/03/2009
So, what do I need to do to update the episode numbers to run from 1-11.
I've tried an UPDATE SQL piece of code (please see below) but can't get it to
work, any ideas?
Regards
JAMES
UPDATE SQL code
===============
Private Sub btnEpisodeRefresh_Click()
Do
intcounter = 0
intcounter = intcounter + 1
DoCmd.RunSQL "UPDATE tblEvent" & _
"SET [episode] = intcounter " & _
"WHERE [block] = " & Me.blockID
Exit Do
Loop
End Sub
Do...loop code
=============
Set dbs = CurrentDb()
Set rstprogramme = dbs.OpenRecordset("tblEvent", dbOpenDynaset)
intcounter = 0
intstartdate = DateAdd("d", -7, Me![startdate])
Do While intcounter <= Me![TotalWeeks]
intcounter = intcounter + 1
intstartdate = DateAdd("d", 7, intstartdate)
rstEvent.AddNew
rstEvent!Episode = intcounter
rstEvent!EventName = Me![EventName]
rstEvent!EventStart = intstartdate
rstEvent!SportType = Me![sporttypeid]
rstEvent!EventLoc = Me![LocationName]
rstEvent!EventBlock = Me![BlockID]
rstEvent!EventOn = Me![StartTime]
rstEvent!EventOff = Me![EndTime]
rstEvent.Update
If intcounter = Me![TotalWeeks] + 1 Then
Exit Do
End If
Loop