Update Query - Consecutive Numbers

[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

 

[Dale Fye]

James,

You should be able to do something like:


strSQL = "UPDATE tblEvent " _
& "SET [Episode] = DCOUNT('ID', 'tblEvent', " _
& "[EventDate] <= # &
[EventDate] & "# " _
& "AND [Block] = " &
me.BlockID & ") " _
& "WHERE [Block] = " & me.BlockID

Basically, what this does is count the number of records for your BlockID
where the date is less than or equal to the EventDate (note that I changed
the field name to EventDate, Date is a reserved word and using it as a field
name will ultimately cause you problems) of the current record.

Watch out for word wrap. Before you actually run this SQL string, I would
do a debug.print strSQL so you can see what the string actually looks like
and make sure that it is doing what you want.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

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

 

[James Frater]

Dale,

Thanks for your speedy response.

Apologies on the naming convention for "Date" I'd typed in the caption from
the subform. The actual field is called "EventStart".

It's counting the number of records perfectly, however it gives every record
the DCount value. So using the example below I now have 11 episodes with the
same episode number of 11.

Should I then use the strSQL as the part of the Do While?

Cheers

JAMES

James,

You should be able to do something like:


strSQL = "UPDATE tblEvent " _
& "SET [Episode] = DCOUNT('ID', 'tblEvent', " _
& "[EventDate] <= # &
[EventDate] & "# " _
& "AND [Block] = " &
me.BlockID & ") " _
& "WHERE [Block] = " & me.BlockID

Basically, what this does is count the number of records for your BlockID
where the date is less than or equal to the EventDate (note that I changed
the field name to EventDate, Date is a reserved word and using it as a field
name will ultimately cause you problems) of the current record.

Watch out for word wrap. Before you actually run this SQL string, I would
do a debug.print strSQL so you can see what the string actually looks like
and make sure that it is doing what you want.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

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

 

[Dale Fye]

James,

You shouldn't need to do any kind of looping to update the Episode field.

Can you post that code as you now have it, with the correct field names and
stuff? I may have missed something, or you may have, so I don't want to go
down the wrong track.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale,

Thanks for your speedy response.

Apologies on the naming convention for "Date" I'd typed in the caption from
the subform. The actual field is called "EventStart".

It's counting the number of records perfectly, however it gives every record
the DCount value. So using the example below I now have 11 episodes with the
same episode number of 11.

Should I then use the strSQL as the part of the Do While?

Cheers

JAMES

James,

You should be able to do something like:


strSQL = "UPDATE tblEvent " _
& "SET [Episode] = DCOUNT('ID', 'tblEvent', " _
& "[EventDate] <= # &
[EventDate] & "# " _
& "AND [Block] = " &
me.BlockID & ") " _
& "WHERE [Block] = " & me.BlockID

Basically, what this does is count the number of records for your BlockID
where the date is less than or equal to the EventDate (note that I changed
the field name to EventDate, Date is a reserved word and using it as a field
name will ultimately cause you problems) of the current record.

Watch out for word wrap. Before you actually run this SQL string, I would
do a debug.print strSQL so you can see what the string actually looks like
and make sure that it is doing what you want.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

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

 

[Dale Fye]

James,

The following query worked for me, to update all of the Episode values, for
all of the BlockIDs:

UPDATE tbl_Events
SET tbl_Events.Episode = DCount("*","tbl_Events","[StartDate] <= #" &
[tbl_Events].[StartDate] & "# AND [BlockID] = " & [tbl_Events].[BlockID]);

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale,

Thanks for your speedy response.

Apologies on the naming convention for "Date" I'd typed in the caption from
the subform. The actual field is called "EventStart".

It's counting the number of records perfectly, however it gives every record
the DCount value. So using the example below I now have 11 episodes with the
same episode number of 11.

Should I then use the strSQL as the part of the Do While?

Cheers

JAMES

James,

You should be able to do something like:


strSQL = "UPDATE tblEvent " _
& "SET [Episode] = DCOUNT('ID', 'tblEvent', " _
& "[EventDate] <= # &
[EventDate] & "# " _
& "AND [Block] = " &
me.BlockID & ") " _
& "WHERE [Block] = " & me.BlockID

Basically, what this does is count the number of records for your BlockID
where the date is less than or equal to the EventDate (note that I changed
the field name to EventDate, Date is a reserved word and using it as a field
name will ultimately cause you problems) of the current record.

Watch out for word wrap. Before you actually run this SQL string, I would
do a debug.print strSQL so you can see what the string actually looks like
and make sure that it is doing what you want.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

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

 

[James Frater]

Dale,

I'm afraid all the codes are still giving each episode the DCount figure
instead of the consecutive numbers from 1 to DCount

As requested, code below.

Many thanks for all of this.

JAMES

======================
Private Sub btnEpisodeNo_Click()

Dim strSQL As String

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', tblevent," _
& "'[eventstart] <= [eventstart] and [block] = " & Me.blockid &
" and [origination] = " & True & "')" _
& "WHERE [block] = " & Me.blockid & " and [origination] = " &
True & ""
DoCmd.RunSQL strSQL
DoCmd.Requery "frmEventSub"

End Sub

James,

The following query worked for me, to update all of the Episode values, for
all of the BlockIDs:

UPDATE tbl_Events
SET tbl_Events.Episode = DCount("*","tbl_Events","[StartDate] <= #" &
[tbl_Events].[StartDate] & "# AND [BlockID] = " & [tbl_Events].[BlockID]);

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale,

Thanks for your speedy response.

Apologies on the naming convention for "Date" I'd typed in the caption from
the subform. The actual field is called "EventStart".

It's counting the number of records perfectly, however it gives every record
the DCount value. So using the example below I now have 11 episodes with the
same episode number of 11.

Should I then use the strSQL as the part of the Do While?

Cheers

JAMES

James,

You should be able to do something like:


strSQL = "UPDATE tblEvent " _
& "SET [Episode] = DCOUNT('ID', 'tblEvent', " _
& "[EventDate] <= # &
[EventDate] & "# " _
& "AND [Block] = " &
me.BlockID & ") " _
& "WHERE [Block] = " & me.BlockID

Basically, what this does is count the number of records for your BlockID
where the date is less than or equal to the EventDate (note that I changed
the field name to EventDate, Date is a reserved word and using it as a field
name will ultimately cause you problems) of the current record.

Watch out for word wrap. Before you actually run this SQL string, I would
do a debug.print strSQL so you can see what the string actually looks like
and make sure that it is doing what you want.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

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

 

[James Frater]

Dale,

Sorry I forgot to mention that I only need to issue "episode" numbers to
events which are held on our on premises and they're identified by a tick box
called "origination" (which explains why I added that into the criteria and
Where part of the argument) Other events which are held in other places are
still recorded in the block, mainly so we can sort out who pays for what at
month end.

Hope that makes a bit of sense.

Have a nice evening.

JAMES

Dale,

I'm afraid all the codes are still giving each episode the DCount figure
instead of the consecutive numbers from 1 to DCount

As requested, code below.

Many thanks for all of this.

JAMES

======================
Private Sub btnEpisodeNo_Click()

Dim strSQL As String

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', tblevent," _
& "'[eventstart] <= [eventstart] and [block] = " & Me.blockid &
" and [origination] = " & True & "')" _
& "WHERE [block] = " & Me.blockid & " and [origination] = " &
True & ""
DoCmd.RunSQL strSQL
DoCmd.Requery "frmEventSub"

End Sub

James,

The following query worked for me, to update all of the Episode values, for
all of the BlockIDs:

UPDATE tbl_Events
SET tbl_Events.Episode = DCount("*","tbl_Events","[StartDate] <= #" &
[tbl_Events].[StartDate] & "# AND [BlockID] = " & [tbl_Events].[BlockID]);

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale,

Thanks for your speedy response.

Apologies on the naming convention for "Date" I'd typed in the caption from
the subform. The actual field is called "EventStart".

It's counting the number of records perfectly, however it gives every record
the DCount value. So using the example below I now have 11 episodes with the
same episode number of 11.

Should I then use the strSQL as the part of the Do While?

Cheers

JAMES



:

James,

You should be able to do something like:


strSQL = "UPDATE tblEvent " _
& "SET [Episode] = DCOUNT('ID', 'tblEvent', " _
& "[EventDate] <= # &
[EventDate] & "# " _
& "AND [Block] = " &
me.BlockID & ") " _
& "WHERE [Block] = " & me.BlockID

Basically, what this does is count the number of records for your BlockID
where the date is less than or equal to the EventDate (note that I changed
the field name to EventDate, Date is a reserved word and using it as a field
name will ultimately cause you problems) of the current record.

Watch out for word wrap. Before you actually run this SQL string, I would
do a debug.print strSQL so you can see what the string actually looks like
and make sure that it is doing what you want.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

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

 

[Dale Fye]

Try this:

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', 'tblevent', " _
& "[eventstart] <= #" & [eventstart] & "# " _
& " AND [block] = " & Me.blockid _
& " and [origination] = -1) " _
& "WHERE [block] = " & Me.blockid _
& " AND [origination] = -1 "

I wrapped 'tblEvent' in single quotes.

Additionally, I modified the criteria string inside the DCOUNT. To use
DCOUNT in this method, you have to distinguish between the fields inside the
DCOUNT and the values from the fields in the main query (look at the way I
put the second instance of [EventStart] and the reference to me.BlockID
outside of the quotes.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale,

I'm afraid all the codes are still giving each episode the DCount figure
instead of the consecutive numbers from 1 to DCount

As requested, code below.

Many thanks for all of this.

JAMES

======================
Private Sub btnEpisodeNo_Click()

Dim strSQL As String

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', tblevent," _
& "'[eventstart] <= [eventstart] and [block] = " & Me.blockid &
" and [origination] = " & True & "')" _
& "WHERE [block] = " & Me.blockid & " and [origination] = " &
True & ""
DoCmd.RunSQL strSQL
DoCmd.Requery "frmEventSub"

End Sub

James,

The following query worked for me, to update all of the Episode values, for
all of the BlockIDs:

UPDATE tbl_Events
SET tbl_Events.Episode = DCount("*","tbl_Events","[StartDate] <= #" &
[tbl_Events].[StartDate] & "# AND [BlockID] = " & [tbl_Events].[BlockID]);

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale,

Thanks for your speedy response.

Apologies on the naming convention for "Date" I'd typed in the caption from
the subform. The actual field is called "EventStart".

It's counting the number of records perfectly, however it gives every record
the DCount value. So using the example below I now have 11 episodes with the
same episode number of 11.

Should I then use the strSQL as the part of the Do While?

Cheers

JAMES



:

James,

You should be able to do something like:


strSQL = "UPDATE tblEvent " _
& "SET [Episode] = DCOUNT('ID', 'tblEvent', " _
& "[EventDate] <= # &
[EventDate] & "# " _
& "AND [Block] = " &
me.BlockID & ") " _
& "WHERE [Block] = " & me.BlockID

Basically, what this does is count the number of records for your BlockID
where the date is less than or equal to the EventDate (note that I changed
the field name to EventDate, Date is a reserved word and using it as a field
name will ultimately cause you problems) of the current record.

Watch out for word wrap. Before you actually run this SQL string, I would
do a debug.print strSQL so you can see what the string actually looks like
and make sure that it is doing what you want.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

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

 

[James Frater]

Morning Dale,

Ha ha ha as you can probably guess I'm still a bit 'green' with my code but
am slowly meandering towards an understanding!!!!!

I'm understand the code, but am the error message "Run-time error '2465' -
Microsoft Access can't find the field '|' referred to in your expression"

I currently have strSQL set as a string, that wouldn't be the cause of this
pesky error.

Regards

JAMES

Try this:

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', 'tblevent', " _
& "[eventstart] <= #" & [eventstart] & "# " _
& " AND [block] = " & Me.blockid _
& " and [origination] = -1) " _
& "WHERE [block] = " & Me.blockid _
& " AND [origination] = -1 "

I wrapped 'tblEvent' in single quotes.

Additionally, I modified the criteria string inside the DCOUNT. To use
DCOUNT in this method, you have to distinguish between the fields inside the
DCOUNT and the values from the fields in the main query (look at the way I
put the second instance of [EventStart] and the reference to me.BlockID
outside of the quotes.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale,

I'm afraid all the codes are still giving each episode the DCount figure
instead of the consecutive numbers from 1 to DCount

As requested, code below.

Many thanks for all of this.

JAMES

======================
Private Sub btnEpisodeNo_Click()

Dim strSQL As String

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', tblevent," _
& "'[eventstart] <= [eventstart] and [block] = " & Me.blockid &
" and [origination] = " & True & "')" _
& "WHERE [block] = " & Me.blockid & " and [origination] = " &
True & ""
DoCmd.RunSQL strSQL
DoCmd.Requery "frmEventSub"

End Sub

James,

The following query worked for me, to update all of the Episode values, for
all of the BlockIDs:

UPDATE tbl_Events
SET tbl_Events.Episode = DCount("*","tbl_Events","[StartDate] <= #" &
[tbl_Events].[StartDate] & "# AND [BlockID] = " & [tbl_Events].[BlockID]);

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Dale,

Thanks for your speedy response.

Apologies on the naming convention for "Date" I'd typed in the caption from
the subform. The actual field is called "EventStart".

It's counting the number of records perfectly, however it gives every record
the DCount value. So using the example below I now have 11 episodes with the
same episode number of 11.

Should I then use the strSQL as the part of the Do While?

Cheers

JAMES



:

James,

You should be able to do something like:


strSQL = "UPDATE tblEvent " _
& "SET [Episode] = DCOUNT('ID', 'tblEvent', " _
& "[EventDate] <= # &
[EventDate] & "# " _
& "AND [Block] = " &
me.BlockID & ") " _
& "WHERE [Block] = " & me.BlockID

Basically, what this does is count the number of records for your BlockID
where the date is less than or equal to the EventDate (note that I changed
the field name to EventDate, Date is a reserved word and using it as a field
name will ultimately cause you problems) of the current record.

Watch out for word wrap. Before you actually run this SQL string, I would
do a debug.print strSQL so you can see what the string actually looks like
and make sure that it is doing what you want.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

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

 

[Dale Fye]

James,

My bad. The last argument of the Dcount also has to be a string, but the
way I have handled it, it is not.

Lets try:

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', 'tblevent', " _
& "'[eventstart] <= #" & [eventstart] & "# " _
& " AND [block] = " & Me.blockid _
& " and [origination] = -1') " _
& "WHERE [block] = " & Me.blockid _
& " AND [origination] = -1 "

I've added a single quote right after the " in line 3, and another right
after the -1 in line 5.

Hope this works.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Morning Dale,

Ha ha ha as you can probably guess I'm still a bit 'green' with my code but
am slowly meandering towards an understanding!!!!!

I'm understand the code, but am the error message "Run-time error '2465' -
Microsoft Access can't find the field '|' referred to in your expression"

I currently have strSQL set as a string, that wouldn't be the cause of this
pesky error.

Regards

JAMES

Try this:

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', 'tblevent', " _
& "[eventstart] <= #" & [eventstart] & "# " _
& " AND [block] = " & Me.blockid _
& " and [origination] = -1) " _
& "WHERE [block] = " & Me.blockid _
& " AND [origination] = -1 "

I wrapped 'tblEvent' in single quotes.

Additionally, I modified the criteria string inside the DCOUNT. To use
DCOUNT in this method, you have to distinguish between the fields inside the
DCOUNT and the values from the fields in the main query (look at the way I
put the second instance of [EventStart] and the reference to me.BlockID
outside of the quotes.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Dale,

I'm afraid all the codes are still giving each episode the DCount figure
instead of the consecutive numbers from 1 to DCount

As requested, code below.

Many thanks for all of this.

JAMES

======================
Private Sub btnEpisodeNo_Click()

Dim strSQL As String

strSQL = "UPDATE tblevent" _
& "SET [episode] = DCount('[eventid]', tblevent," _
& "'[eventstart] <= [eventstart] and [block] = " & Me.blockid &
" and [origination] = " & True & "')" _
& "WHERE [block] = " & Me.blockid & " and [origination] = " &
True & ""
DoCmd.RunSQL strSQL
DoCmd.Requery "frmEventSub"

End Sub




:

James,

The following query worked for me, to update all of the Episode values, for
all of the BlockIDs:

UPDATE tbl_Events
SET tbl_Events.Episode = DCount("*","tbl_Events","[StartDate] <= #" &
[tbl_Events].[StartDate] & "# AND [BlockID] = " & [tbl_Events].[BlockID]);

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Dale,

Thanks for your speedy response.

Apologies on the naming convention for "Date" I'd typed in the caption from
the subform. The actual field is called "EventStart".

It's counting the number of records perfectly, however it gives every record
the DCount value. So using the example below I now have 11 episodes with the
same episode number of 11.

Should I then use the strSQL as the part of the Do While?

Cheers

JAMES



:

James,

You should be able to do something like:


strSQL = "UPDATE tblEvent " _
& "SET [Episode] = DCOUNT('ID', 'tblEvent', " _
& "[EventDate] <= # &
[EventDate] & "# " _
& "AND [Block] = " &
me.BlockID & ") " _
& "WHERE [Block] = " & me.BlockID

Basically, what this does is count the number of records for your BlockID
where the date is less than or equal to the EventDate (note that I changed
the field name to EventDate, Date is a reserved word and using it as a field
name will ultimately cause you problems) of the current record.

Watch out for word wrap. Before you actually run this SQL string, I would
do a debug.print strSQL so you can see what the string actually looks like
and make sure that it is doing what you want.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

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