Do I need a Nested Loop?

[nomadk]

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

[Douglas J. Steele]

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
http://I.Am/DougSteele
(no e-mails, please!)

"nomadk" <nom...@discussions.microsoft.com> wrote in message
news:95A25BB3-A992-4B71...@microsoft.com...

[Stuart McCall]

"nomadk" <nom...@discussions.microsoft.com> wrote in message
news:95A25BB3-A992-4B71...@microsoft.com...

Insert the line:

savPhsnum = !phsnum

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

If savPhsnum <> !phsnum Then cycles = 0

[nomadk]

"Douglas J. Steele" wrote:

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?

[Douglas J. Steele]

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
http://I.Am/DougSteele
(no e-mails, please!)

"nomadk" <nom...@discussions.microsoft.com> wrote in message

news:CF3497E9-CDA8-49F4...@microsoft.com...

> Thanks for your help, Doug.
>
>

[nomadk]

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

[John Spencer]

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" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:ulcD0X7J...@TK2MSFTNGP02.phx.gbl...

[Douglas J. Steele]

"nomadk" <nom...@discussions.microsoft.com> wrote in message

news:8C84E028-E5AB-461C...@microsoft.com...

> 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.

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

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!

[nomadk]

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" wrote:

> ..

[John Spencer]

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" <nom...@discussions.microsoft.com> wrote in message
news:BC9C352E-8778-48E1...@microsoft.com...

[nomadk]

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" wrote:

> 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

> ..

[John Spencer]

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
.

"nomadk" <nom...@discussions.microsoft.com> wrote in message
news:EAFC820C-85D0-4EEA...@microsoft.com...

[nomadk]

Perfect!

Thanks John and Doug for setting me straight.

"John Spencer" wrote:

> 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

> ..

[John Spencer]

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
.

"nomadk" <nom...@discussions.microsoft.com> wrote in message
news:52BCA72F-C37E-4F55...@microsoft.com...

[Douglas J. Steele]

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"John Spencer" <spe...@chpdm.edu> wrote in message
news:enotpTF...@TK2MSFTNGP06.phx.gbl...