Error with Do Loop while trying to validate ranges in a table

[Loribeth]

Hi All,

I am working on a sub that will loop through a recordset and validate
that the ranges are valid, ie no overlapping values - no gaps in the
ranges. I am pretty sure I have the validation part right but not
sure since I keep getting an error message "Compile Error: Loop
without Do". Of course I have a Do in the code and have looked at the
help files to see what is wrong with my syntax and I don't see the
problem.

Will someone please take a look at my code and see if you can tell me
what I am doing wrong? Thanks to all in advance!!!!

*********begin code****************
Dim db As Database
Dim rst As DAO.Recordset
Dim cur_group_id As Integer, cur_range_id As Integer, cur_range_begin
As Integer, cur_range_end As Integer, Valid_range_begin As Integer
Dim prior_group_id As Integer, prior_range_id As Integer,
prior_range_begin As Integer, prior_range_end As Integer

Set rst = CurrentDb.OpenRecordset("SELECT
Local_market_score_group_member_range.mkt_score_gr p_id as group_id,
Local_market_score_group_member_range.range_id as range_id,
Local_market_score_group_member_range.range_begin as range_begin,
Local_market_score_group_member_range.range_end as range_end FROM
Local_market_score_group_member_range ORDER BY
Local_market_score_group_member_range.mkt_score_gr p_id,
Local_market_score_group_member_range.range_id; ", dbopensnapshot)

prior_group_id = 0
prior_range_id = 0
prior_range_begin = 0
prior_range_end = 0

rst.MoveFirst
cur_group_id = rst![group_id]
cur_range_id = rst![range_id]
cur_range_begin = rst![range_begin]
cur_range_end = rst![range_end]

Do Until rst.EOF
If cur_group_id > prior_group_id Then 'new group - reset "prior"
variables
prior_range_id = 0
prior_range_begin = 0
prior_range_end = 0

Else ' We are still looking at the same group so begin validating
ranges
If cur_range_end > cur_range_begin Then
MsgBox "Range end must be greater or equal to range begin"
Exit Do
Else ' Current range values are valid so check that current range
begin is prior_range_end + 1
Valid_range_begin = (prior_range_end + 1)
If cur_range_begin <> Valid_range_begin Then
MsgBox "Current Range Begin value is either less than prior
Range end or there is a gap in the range values."
Exit Do
End If
prior_group_id = rst![group_id] ' resets the group_id
variable to the current record
rst.MoveNext
Loop

*********end code************

Thanks again,
Loribeth

[Matthew Sullivan]

You're missing a couple of "End If" lines. You need one for each
"If".
On 9 Sep 2003 13:15:28 -0700, lp*********@insweb.com (Loribeth) wrote:

Hi All,

I am working on a sub that will loop through a recordset and validate
that the ranges are valid, ie no overlapping values - no gaps in the
ranges. I am pretty sure I have the validation part right but not
sure since I keep getting an error message "Compile Error: Loop
without Do". Of course I have a Do in the code and have looked at the
help files to see what is wrong with my syntax and I don't see the
problem.

Will someone please take a look at my code and see if you can tell me
what I am doing wrong? Thanks to all in advance!!!!

*********begin code****************
Dim db As Database
Dim rst As DAO.Recordset
Dim cur_group_id As Integer, cur_range_id As Integer, cur_range_begin
As Integer, cur_range_end As Integer, Valid_range_begin As Integer
Dim prior_group_id As Integer, prior_range_id As Integer,
prior_range_begin As Integer, prior_range_end As Integer

Set rst = CurrentDb.OpenRecordset("SELECT
Local_market_score_group_member_range.mkt_score_g rp_id as group_id,
Local_market_score_group_member_range.range_id as range_id,
Local_market_score_group_member_range.range_beg in as range_begin,
Local_market_score_group_member_range.range_end as range_end FROM
Local_market_score_group_member_range ORDER BY
Local_market_score_group_member_range.mkt_score_g rp_id,
Local_market_score_group_member_range.range_id; ", dbopensnapshot)

prior_group_id = 0
prior_range_id = 0
prior_range_begin = 0
prior_range_end = 0

rst.MoveFirst
cur_group_id = rst![group_id]
cur_range_id = rst![range_id]
cur_range_begin = rst![range_begin]
cur_range_end = rst![range_end]

Do Until rst.EOF
If cur_group_id > prior_group_id Then 'new group - reset "prior"
variables
prior_range_id = 0
prior_range_begin = 0
prior_range_end = 0

Else ' We are still looking at the same group so begin validating
ranges
If cur_range_end > cur_range_begin Then
MsgBox "Range end must be greater or equal to range begin"
Exit Do
Else ' Current range values are valid so check that current range
begin is prior_range_end + 1
Valid_range_begin = (prior_range_end + 1)
If cur_range_begin <> Valid_range_begin Then
MsgBox "Current Range Begin value is either less than prior
Range end or there is a gap in the range values."
Exit Do
End If
prior_group_id = rst![group_id] ' resets the group_id
variable to the current record
rst.MoveNext
Loop

*********end code************

Thanks again,
Loribeth

[Loribeth Pellegrini]

Not sure why I need to use End If instead of Else but I made the change.
Replaced the instances of Else with End If.

Now when I try to run I get a message that just says "Overflow". I
tried to check my help files and the word "overflow" shows up in the
index but when I click on it nothing happens(this is not the only topic
that behaves like this). Can anyone please help with

1. What does the "overflow" message mean?

2. Why can I see some of the help files and not others?

Thanks so much!!!

Loribeth

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!