Do I need a Nested Loop?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Everyone,

Access 2003, XP Pro

I use this function to assign a value to [linnum].

"Option Compare Database


Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim count, cycles, linecount As String

cycles = 0 'how many loops
count = 0 ' number of records
linecount = "01" ' increment for line items

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)
Set rst = dbs.OpenRecordset("tblBudgetLines", dbOpenTable)


rst.MoveLast
rst.MoveFirst
count = rst.RecordCount


Do Until cycles = count

With rst
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
cycles = cycles + 1
End With

Loop

rst.Close

End Function"

How can I modify this so that I restart numbering each time the function
encounters a new [phsnum]?

For Example:

phsnum Match linnum
01 1000 1
01 2000 2
01 3000 3
02 1000 1
02 1500 2
03 5000 1


TIA
 
You need to keep track of what the previous value was for phsnum, and reset
linecount each time it changes:

Dim Prevphsnum As ??? ' I don't know what the data type should be

Do Until cycles = count

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
cycles = cycles + 1
Prevphsnum = !phsnum
End With

Loop


Why have you set linecount to a string, though, if you're doing arithmetic
with it?
 
nomadk said:
Hi Everyone,

Access 2003, XP Pro

I use this function to assign a value to [linnum].

"Option Compare Database


Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim count, cycles, linecount As String

cycles = 0 'how many loops
count = 0 ' number of records
linecount = "01" ' increment for line items

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by
phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)
Set rst = dbs.OpenRecordset("tblBudgetLines", dbOpenTable)


rst.MoveLast
rst.MoveFirst
count = rst.RecordCount


Do Until cycles = count

With rst
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
cycles = cycles + 1
End With

Loop

rst.Close

End Function"

How can I modify this so that I restart numbering each time the function
encounters a new [phsnum]?

For Example:

phsnum Match linnum
01 1000 1
01 2000 2
01 3000 3
02 1000 1
02 1500 2
03 5000 1


TIA
Click to expand...

Insert the line:

savPhsnum = !phsnum

before the .Edit line. Then, just before the End With, insert:

If savPhsnum <> !phsnum Then cycles = 0
 
Douglas J. Steele said:
You need to keep track of what the previous value was for phsnum, and reset
linecount each time it changes:

Dim Prevphsnum As ??? ' I don't know what the data type should be

Do Until cycles = count

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
cycles = cycles + 1
Prevphsnum = !phsnum
End With

Loop


Why have you set linecount to a string, though, if you're doing arithmetic
with it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


nomadk said:
Hi Everyone,

Access 2003, XP Pro

I use this function to assign a value to [linnum].

"Option Compare Database


Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim count, cycles, linecount As String

cycles = 0 'how many loops
count = 0 ' number of records
linecount = "01" ' increment for line items

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by
phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)
Set rst = dbs.OpenRecordset("tblBudgetLines", dbOpenTable)


rst.MoveLast
rst.MoveFirst
count = rst.RecordCount


Do Until cycles = count

With rst
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
cycles = cycles + 1
End With

Loop

rst.Close

End Function"

How can I modify this so that I restart numbering each time the function
encounters a new [phsnum]?

For Example:

phsnum Match linnum
01 1000 1
01 2000 2
01 3000 3
02 1000 1
02 1500 2
03 5000 1


TIA
Click to expand...
Click to expand...
Thanks for your help, Doug.

Here's my revised code:

"Option Compare Database


Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim count, cycles As String
Dim lincount As Long

cycles = 0 'how many loops
count = 0 ' number of records
linecount = "01" ' increment for line items

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)
Set rst = dbs.OpenRecordset("tblBudgetLines", dbOpenTable)


rst.MoveLast
rst.MoveFirst
count = rst.RecordCount
Dim Prevphsnum As Long

Do Until cycles = count

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
cycles = cycles + 1
Prevphsnum = !phsnum

End With

Loop

rst.Close

End Function"

When I execute it I get error '3021' No Current Record.

Any suggestions?
 
Looking at your code, it would appear that the only reason you've got Cycles
and Count in there is to control your loop. It's far safer to use the EOF
property of the recordset. You're also opening rst twice, and since you're
strictly using the table in the second case, your data will be unsorted.

Try:

Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
Prevphsnum = !phsnum
End With

Loop

rst.Close

End Function

Your sample code declares count and cycles as String variables, yet you're
doing arithmetic with them. You declare linecount to be Long, but you
initialize it to a string ("01").
 
Thanks again for your help, Doug. I should have prefaced my question by
saying that I'm teaching myself VBA, one module at a time.

I see what you mean about the sort order. The table is already sorted by
phsnum and Match anyway.

Using your revised code I still get the No Current Record error. The updated
table shows this:

phsnum Match linnum
0 1100
0 2250 1
1 1100 2
1 1140 3
1 1230 4
3 1290 5
4 2280 6
 
Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
linecount = linecount + 1
Prevphsnum = !phsnum
rst.MoveNext 'Note the move of this line to the end the loop
End With

Loop

rst.Close

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Douglas J. Steele said:
Looking at your code, it would appear that the only reason you've got
Cycles and Count in there is to control your loop. It's far safer to use
the EOF property of the recordset. You're also opening rst twice, and
since you're strictly using the table in the second case, your data will
be unsorted.

Try:

Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by
phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
Prevphsnum = !phsnum
End With

Loop

rst.Close

End Function

Your sample code declares count and cycles as String variables, yet you're
doing arithmetic with them. You declare linecount to be Long, but you
initialize it to a string ("01").

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


nomadk said:
Thanks for your help, Doug.


When I execute it I get error '3021' No Current Record.

Any suggestions?
Click to expand...
Click to expand...
 
nomadk said:
Thanks again for your help, Doug. I should have prefaced my question by
saying that I'm teaching myself VBA, one module at a time.

I see what you mean about the sort order. The table is already sorted by
phsnum and Match anyway.
Click to expand...

No, it's not. You can never make any assumption about the order of records
in tables. Tables are "sacks of data": Access puts the data wherever it
feels like. The only way to ensure the order is to use a query with a
particular ORDER BY clause.
Using your revised code I still get the No Current Record error. The
updated
table shows this:

phsnum Match linnum
0 1100
0 2250 1
1 1100 2
1 1140 3
1 1230 4
3 1290 5
4 2280 6
Click to expand...

John corrected an error I made in the code I gave you earlier. Put the
MoveNext at the end of the section:

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
linecount = linecount + 1
Prevphsnum = !phsnum
.MoveNext
End With

The way it was before, at the last record, the code was moving to the end of
the recordset and then trying to access the value of phsnum in that
non-existant record. Sorry about that!
 
Thanks, John, for the suggestion.

Using your function I get the following:

phsnum Match linnum
0 1100
0 2250 1
1 1100 1
1 1140 2
1 1230 3
3 1290 1
4 2280 1

So restarting at phsnum is working but it still skips the first record.

John Spencer said:
Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
linecount = linecount + 1
Prevphsnum = !phsnum
rst.MoveNext 'Note the move of this line to the end the loop
End With

Loop

rst.Close

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Douglas J. Steele said:
Looking at your code, it would appear that the only reason you've got
Cycles and Count in there is to control your loop. It's far safer to use
the EOF property of the recordset. You're also opening rst twice, and
since you're strictly using the table in the second case, your data will
be unsorted.

Try:

Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by
phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
Prevphsnum = !phsnum
End With

Loop

rst.Close

End Function

Your sample code declares count and cycles as String variables, yet you're
doing arithmetic with them. You declare linecount to be Long, but you
initialize it to a string ("01").

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


nomadk said:
Thanks for your help, Doug.


When I execute it I get error '3021' No Current Record.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Small error in the DIM statement

Dim LineCount as Long
instead of
Dim LinCount as Long



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nomadk said:
Thanks, John, for the suggestion.

Using your function I get the following:

phsnum Match linnum
0 1100
0 2250 1
1 1100 1
1 1140 2
1 1230 3
3 1290 1
4 2280 1

So restarting at phsnum is working but it still skips the first record.

John Spencer said:
Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by
phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
linecount = linecount + 1
Prevphsnum = !phsnum
rst.MoveNext 'Note the move of this line to the end the loop
End With

Loop

rst.Close

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Douglas J. Steele said:
Looking at your code, it would appear that the only reason you've got
Cycles and Count in there is to control your loop. It's far safer to
use
the EOF property of the recordset. You're also opening rst twice, and
since you're strictly using the table in the second case, your data
will
be unsorted.

Try:

Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by
phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
Prevphsnum = !phsnum
End With

Loop

rst.Close

End Function

Your sample code declares count and cycles as String variables, yet
you're
doing arithmetic with them. You declare linecount to be Long, but you
initialize it to a string ("01").

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for your help, Doug.


When I execute it I get error '3021' No Current Record.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Thanks John, I should have noticed that myself.

I don't want to be a pest, but now I get:

phsnum Match linnum
0 1100 0
0 2250 1
1 1100 1
1 1140 2
1 1230 3
3 1290 1
4 2280 1

Can you tell me why the first record starts at "0", rather than "1"?


John Spencer said:
Small error in the DIM statement

Dim LineCount as Long
instead of
Dim LinCount as Long



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nomadk said:
Thanks, John, for the suggestion.

Using your function I get the following:

phsnum Match linnum
0 1100
0 2250 1
1 1100 1
1 1140 2
1 1230 3
3 1290 1
4 2280 1

So restarting at phsnum is working but it still skips the first record.

John Spencer said:
Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by
phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
linecount = linecount + 1
Prevphsnum = !phsnum
rst.MoveNext 'Note the move of this line to the end the loop
End With

Loop

rst.Close

End Function


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Looking at your code, it would appear that the only reason you've got
Cycles and Count in there is to control your loop. It's far safer to
use
the EOF property of the recordset. You're also opening rst twice, and
since you're strictly using the table in the second case, your data
will
be unsorted.

Try:

Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by
phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
Prevphsnum = !phsnum
End With

Loop

rst.Close

End Function

Your sample code declares count and cycles as String variables, yet
you're
doing arithmetic with them. You declare linecount to be Long, but you
initialize it to a string ("01").

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for your help, Doug.


When I execute it I get error '3021' No Current Record.

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Yes. On the first record phsNum = 0 and Prevphsnum = 0 so the LineCount
does not get set to 1 and remains at the default value of 0.

You can fix that by setting LineCount to 1 before you enter the loop.

Dim LineCount as Long
LineCount =1 'Set default value

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Glad it is working.

Douglas did all the work. I just nit-picked.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Good nitpicking, John. I made the bad assumption that

If !phsnum <> Prevphsnum Then
linecount = 1
End If

would set linecount = 1 for the first entry. It would have, but
unfortunately the first value of !phsnum was 0, which is what Prevphsnum
gets assigned as its default value.
 
Back
Top