Sort Table; Then Assign Sequential Numbers

[amywolfie]

Hi All:

I know this is simple, but I just can't seem to get there:

I need to sort a table by a text field (txtDescription), then assign
sequential numbers to the field SEQUENCE in table.

Every time a new record is added, the all SEQUENCE #'s must be reset.

The re-numbering will be triggered by an EXPORT button.

What's the best way to accomplish this? An update query, or a Loop in
VBA?

Thanks!

amy
===

[Reggie]

Amy, you could simply design a query based on this table and sort it by
txtDescription. Now design a report based on this query. Ad an unbound
text box control to the detail section of the report. Select the text box
and view/properties from the main menu. Type in =1 as the control source and
set the Running Sum to Over All. Now it will always count them for you.
All you have to do is export the report.

--
Reggie

----------
"amywolfie" <am*******@verizon.net> wrote in message
news:33**************************@posting.google.c om...

Hi All:

I know this is simple, but I just can't seem to get there:

I need to sort a table by a text field (txtDescription), then assign
sequential numbers to the field SEQUENCE in table.

Every time a new record is added, the all SEQUENCE #'s must be reset.

The re-numbering will be triggered by an EXPORT button.

What's the best way to accomplish this? An update query, or a Loop in
VBA?

Thanks!

amy
===

[Larry Linson]

Obviously you are aware that relational tables are, by definition, unordered
so they don't have a "record number" as non-relational database tables
sometimes do. I'd guess the requirement for a sequential number is a
requirement of the software that will use the data you export.

You can create a user defined function to return the next number in sequence
so you can use a query to do what you want, but it's not nearly so simple as
it seems... close examination will show that the UDF may be called multiple
times for each record, so there may be skips in the numbers unless you code
very carefully to ensure you don't increment more than once for the same
record.

I think it'll be a good deal simpler (and perhaps safer, too) if you open a
recordset using a Query with the sort, and update the sequence numbers in
each record one by one. That will, almost certainly, be a slower operation
than using a Query, but creating the required UDF for the query can be very
frustrating.

Larry Linson
Microsoft Access MVP

"amywolfie" <am*******@verizon.net> wrote in message
news:33**************************@posting.google.c om...

Hi All:

I know this is simple, but I just can't seem to get there:

I need to sort a table by a text field (txtDescription), then assign
sequential numbers to the field SEQUENCE in table.

Every time a new record is added, the all SEQUENCE #'s must be reset.

The re-numbering will be triggered by an EXPORT button.

What's the best way to accomplish this? An update query, or a Loop in
VBA?

Thanks!

amy
===

[amywolfie]

Larry:

You are absolutely right -- the Sequence order is a requirement for an
export into DB2.

Is there a "simple" alternative to manual input, perhaps using VBA?

Thanks!

amy
===

"Larry Linson" <bo*****@localhost.not> wrote in message news:<gV****************@nwrddc01.gnilink.net>...

Obviously you are aware that relational tables are, by definition, unordered
so they don't have a "record number" as non-relational database tables
sometimes do. I'd guess the requirement for a sequential number is a
requirement of the software that will use the data you export.

You can create a user defined function to return the next number in sequence
so you can use a query to do what you want, but it's not nearly so simple as
it seems... close examination will show that the UDF may be called multiple
times for each record, so there may be skips in the numbers unless you code
very carefully to ensure you don't increment more than once for the same
record.

I think it'll be a good deal simpler (and perhaps safer, too) if you open a
recordset using a Query with the sort, and update the sequence numbers in
each record one by one. That will, almost certainly, be a slower operation
than using a Query, but creating the required UDF for the query can be very
frustrating.

Larry Linson
Microsoft Access MVP

"amywolfie" <am*******@verizon.net> wrote in message
news:33**************************@posting.google.c om...

Hi All:

I know this is simple, but I just can't seem to get there:

I need to sort a table by a text field (txtDescription), then assign
sequential numbers to the field SEQUENCE in table.

Every time a new record is added, the all SEQUENCE #'s must be reset.

The re-numbering will be triggered by an EXPORT button.

What's the best way to accomplish this? An update query, or a Loop in
VBA?

Thanks!

amy
===

[Pieter Linden]

this should get you started - tweak to suit your needs:

Option Compare Database
Option Explicit

Public Sub UpdateCounterInTable()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT UserName, IDNo FROM tblNames
ORDER BY UserName", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("IDNo") = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

[amywolfie]

Hi Peter:

I'm getting strange results by using the following code:

Function AssignSequenceNumber()

'Sorts tblExportParent by txtDescription, then RENUMBERS SEQUENCE on
each export -- AWW 7/21/04

On Error GoTo Error_Handler:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DESCRIPTION, SEQUENCE FROM
tblExportParent ORDER BY DESCRIPTION", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("SEQUENCE") = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Exit Function
Error_Handler:
MsgBox Err.Number & " " & Err.Description

End Function

=====
SEQUENCE numbers are being set as:
3,5,8,10,11,12,13,15,19,26,27,28,29,31,33,40,42,47 ,48,55,59,64,65,
etc.

??????

Thanks!

amy
====

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

this should get you started - tweak to suit your needs:

Option Compare Database
Option Explicit

Public Sub UpdateCounterInTable()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT UserName, IDNo FROM tblNames
ORDER BY UserName", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("IDNo") = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

[Pieter Linden]

am*******@verizon.net (amywolfie) wrote in message news:<33**************************@posting.google. com>...

Hi Peter:

I'm getting strange results by using the following code:

Function AssignSequenceNumber()

'Sorts tblExportParent by txtDescription, then RENUMBERS SEQUENCE on
each export -- AWW 7/21/04

On Error GoTo Error_Handler:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DESCRIPTION, SEQUENCE FROM
tblExportParent ORDER BY DESCRIPTION", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("SEQUENCE") = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Exit Function
Error_Handler:
MsgBox Err.Number & " " & Err.Description

End Function

=====
SEQUENCE numbers are being set as:
3,5,8,10,11,12,13,15,19,26,27,28,29,31,33,40,42,47 ,48,55,59,64,65,
etc.

??????

Thanks!

amy
====

no idea what's going on... do you have the table opened exclusively?
(Can you even do that using dbDenyWrite(?)...)

[amywolfie]

No -- backend is on the network, and I am currently the only user of
the front end, which is multi-user.

amy
===

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf*************************@posting.google.c om>...

am*******@verizon.net (amywolfie) wrote in message news:<33**************************@posting.google. com>...

Hi Peter:

I'm getting strange results by using the following code:

Function AssignSequenceNumber()

'Sorts tblExportParent by txtDescription, then RENUMBERS SEQUENCE on
each export -- AWW 7/21/04

On Error GoTo Error_Handler:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DESCRIPTION, SEQUENCE FROM
tblExportParent ORDER BY DESCRIPTION", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("SEQUENCE") = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Exit Function
Error_Handler:
MsgBox Err.Number & " " & Err.Description

End Function

=====
SEQUENCE numbers are being set as:
3,5,8,10,11,12,13,15,19,26,27,28,29,31,33,40,42,47 ,48,55,59,64,65,
etc.

??????

Thanks!

amy
====

no idea what's going on... do you have the table opened exclusively?
(Can you even do that using dbDenyWrite(?)...)

[amywolfie]

Can anyone out there debug this code or suggest alternate code to
accomplish assigning Sequence #'s to records on a re-sort?

Thanks!!

amy
===
am*******@verizon.net (amywolfie) wrote in message news:<33**************************@posting.google. com>...

Larry:

You are absolutely right -- the Sequence order is a requirement for an
export into DB2.

Is there a "simple" alternative to manual input, perhaps using VBA?

Thanks!

amy
===

"Larry Linson" <bo*****@localhost.not> wrote in message news:<gV****************@nwrddc01.gnilink.net>...

Obviously you are aware that relational tables are, by definition, unordered
so they don't have a "record number" as non-relational database tables
sometimes do. I'd guess the requirement for a sequential number is a
requirement of the software that will use the data you export.

You can create a user defined function to return the next number in sequence
so you can use a query to do what you want, but it's not nearly so simple as
it seems... close examination will show that the UDF may be called multiple
times for each record, so there may be skips in the numbers unless you code
very carefully to ensure you don't increment more than once for the same
record.

I think it'll be a good deal simpler (and perhaps safer, too) if you open a
recordset using a Query with the sort, and update the sequence numbers in
each record one by one. That will, almost certainly, be a slower operation
than using a Query, but creating the required UDF for the query can be very
frustrating.

Larry Linson
Microsoft Access MVP

"amywolfie" <am*******@verizon.net> wrote in message
news:33**************************@posting.google.c om...

Hi All:

I know this is simple, but I just can't seem to get there:

I need to sort a table by a text field (txtDescription), then assign
sequential numbers to the field SEQUENCE in table.

Every time a new record is added, the all SEQUENCE #'s must be reset.

The re-numbering will be triggered by an EXPORT button.

What's the best way to accomplish this? An update query, or a Loop in
VBA?

Thanks!

amy
===

[James Fortune]

am*******@verizon.net (amywolfie) wrote in message news:<33**************************@posting.google. com>...

Hi Peter:

I'm getting strange results by using the following code:

Function AssignSequenceNumber()

'Sorts tblExportParent by txtDescription, then RENUMBERS SEQUENCE on
each export -- AWW 7/21/04

On Error GoTo Error_Handler:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DESCRIPTION, SEQUENCE FROM
tblExportParent ORDER BY DESCRIPTION", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("SEQUENCE") = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Exit Function
Error_Handler:
MsgBox Err.Number & " " & Err.Description

End Function

=====
SEQUENCE numbers are being set as:
3,5,8,10,11,12,13,15,19,26,27,28,29,31,33,40,42,47 ,48,55,59,64,65,
etc.

??????

Thanks!

amy
====

Amy,

While you're waiting for us to figure this out, why don't you create a
Make Table query with all the fields in tblExportParent and sorted by
DESCRIPTION. After creating the new table, say tblExportParent2, go
into it's table design and remove the SEQUENCE field. Then add it
back as an AutoNumber field. After saving the design go back in and
change the SEQUENCE field to a Long. After checking out the results,
delete tblExportParent and rename tblExportParent2 to tblExportParent.
I can't believe I just suggested those steps :-). I notice a few
things about the code that still don't explain the results you are
getting. First, a Function that doesn't return a value is being used.
I would normally use a Sub for something like that but it's not
unheard of to use a Function without a return value. Second, you
don't give an initial value for lngCounter. I don't like assuming
that Access will give it a meaningful value. That doesn't explain the
results you are getting either. Third, the code doesn't do a
rs.MoveFirst before starting to edit the record. I wouldn't assume
that Access is at any particular record without performing some kind
of positioning command. It's possible that this could do some strange
things, but the MoveNext would have to skip around almost randomly to
miss hitting the EOF and increment the count for that record more than
once. It's a little confusing to me to name a variable lngCounter and
make it an Integer but it's not unheard of. I normally put a ';' at
the end of the SQL string but it's not required. None of these things
seem to account for what you are getting. Did Pieter think you were
just looking for someone to do your homework assignment? It doesn't
look like his normal style of coding.

James A. Fortune

[amywolfie]

Thanks James. I will try to implement some of your suggestions and
see if this makes a difference in getting the Sequence #'s to be
sequential.

amy
==
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...

am*******@verizon.net (amywolfie) wrote in message news:<33**************************@posting.google. com>...

Hi Peter:

I'm getting strange results by using the following code:

Function AssignSequenceNumber()

'Sorts tblExportParent by txtDescription, then RENUMBERS SEQUENCE on
each export -- AWW 7/21/04

On Error GoTo Error_Handler:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DESCRIPTION, SEQUENCE FROM
tblExportParent ORDER BY DESCRIPTION", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("SEQUENCE") = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Exit Function
Error_Handler:
MsgBox Err.Number & " " & Err.Description

End Function

=====
SEQUENCE numbers are being set as:
3,5,8,10,11,12,13,15,19,26,27,28,29,31,33,40,42,47 ,48,55,59,64,65,
etc.

??????

Thanks!

amy
====

Amy,

While you're waiting for us to figure this out, why don't you create a
Make Table query with all the fields in tblExportParent and sorted by
DESCRIPTION. After creating the new table, say tblExportParent2, go
into it's table design and remove the SEQUENCE field. Then add it
back as an AutoNumber field. After saving the design go back in and
change the SEQUENCE field to a Long. After checking out the results,
delete tblExportParent and rename tblExportParent2 to tblExportParent.
I can't believe I just suggested those steps :-). I notice a few
things about the code that still don't explain the results you are
getting. First, a Function that doesn't return a value is being used.
I would normally use a Sub for something like that but it's not
unheard of to use a Function without a return value. Second, you
don't give an initial value for lngCounter. I don't like assuming
that Access will give it a meaningful value. That doesn't explain the
results you are getting either. Third, the code doesn't do a
rs.MoveFirst before starting to edit the record. I wouldn't assume
that Access is at any particular record without performing some kind
of positioning command. It's possible that this could do some strange
things, but the MoveNext would have to skip around almost randomly to
miss hitting the EOF and increment the count for that record more than
once. It's a little confusing to me to name a variable lngCounter and
make it an Integer but it's not unheard of. I normally put a ';' at
the end of the SQL string but it's not required. None of these things
seem to account for what you are getting. Did Pieter think you were
just looking for someone to do your homework assignment? It doesn't
look like his normal style of coding.

James A. Fortune

[amywolfie]

Well, I did get this to work!

I used Pieter's code, just changed the syntax of the SQL statement to:

Dim sSQL As String
sSQL = "SELECT DESCRIPTION, SEQUENCE FROM tblExportParent ORDER BY
DESCRIPTION ASC"
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)

and Sorted the table before running the function.

Works great!

Thanks Pieter & James!!

amy
===
am*******@verizon.net (amywolfie) wrote in message news:<33**************************@posting.google. com>...

Thanks James. I will try to implement some of your suggestions and
see if this makes a difference in getting the Sequence #'s to be
sequential.

amy
==
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...

am*******@verizon.net (amywolfie) wrote in message news:<33**************************@posting.google. com>...

Hi Peter:

I'm getting strange results by using the following code:

Function AssignSequenceNumber()

'Sorts tblExportParent by txtDescription, then RENUMBERS SEQUENCE on
each export -- AWW 7/21/04

On Error GoTo Error_Handler:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DESCRIPTION, SEQUENCE FROM
tblExportParent ORDER BY DESCRIPTION", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("SEQUENCE") = lngCounter
rs.Update
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Exit Function
Error_Handler:
MsgBox Err.Number & " " & Err.Description

End Function

=====
SEQUENCE numbers are being set as:
3,5,8,10,11,12,13,15,19,26,27,28,29,31,33,40,42,47 ,48,55,59,64,65,
etc.

??????

Thanks!

amy
====

Amy,

While you're waiting for us to figure this out, why don't you create a
Make Table query with all the fields in tblExportParent and sorted by
DESCRIPTION. After creating the new table, say tblExportParent2, go
into it's table design and remove the SEQUENCE field. Then add it
back as an AutoNumber field. After saving the design go back in and
change the SEQUENCE field to a Long. After checking out the results,
delete tblExportParent and rename tblExportParent2 to tblExportParent.
I can't believe I just suggested those steps :-). I notice a few
things about the code that still don't explain the results you are
getting. First, a Function that doesn't return a value is being used.
I would normally use a Sub for something like that but it's not
unheard of to use a Function without a return value. Second, you
don't give an initial value for lngCounter. I don't like assuming
that Access will give it a meaningful value. That doesn't explain the
results you are getting either. Third, the code doesn't do a
rs.MoveFirst before starting to edit the record. I wouldn't assume
that Access is at any particular record without performing some kind
of positioning command. It's possible that this could do some strange
things, but the MoveNext would have to skip around almost randomly to
miss hitting the EOF and increment the count for that record more than
once. It's a little confusing to me to name a variable lngCounter and
make it an Integer but it's not unheard of. I normally put a ';' at
the end of the SQL string but it's not required. None of these things
seem to account for what you are getting. Did Pieter think you were
just looking for someone to do your homework assignment? It doesn't
look like his normal style of coding.

James A. Fortune

[Pieter Linden]

I notice a few

things about the code that still don't explain the results you are
getting. First, a Function that doesn't return a value is being used.
I would normally use a Sub for something like that but it's not
unheard of to use a Function without a return value.
Yeah, I was being lazy...

Second, you don't give an initial value for lngCounter. I don't like assuming
that Access will give it a meaningful value.
very true, but I assumed (perhaps wrongly) that Access/VB would
initialize at zero, unlike C which can do really weird things....

That doesn't explain the results you are getting either. Third, the code doesn't do a
rs.MoveFirst before starting to edit the record. I wouldn't assume
that Access is at any particular record without performing some kind
of positioning command. It's possible that this could do some strange
things, but the MoveNext would have to skip around almost randomly to
miss hitting the EOF and increment the count for that record more than
once.
Yup, me being sloppy again...

It's a little confusing to me to name a variable lngCounter and make it an Integer but it's not unheard of. I normally put a ';' at
the end of the SQL string but it's not required. None of these things
seem to account for what you are getting. Did Pieter think you were
just looking for someone to do your homework assignment? It doesn't
look like his normal style of coding.

Oh Okay! take me to school, why don't ya? (just kidding!)

[James Fortune]

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

don't give an initial value for lngCounter. I don't like assuming
that Access will give it a meaningful value.
very true, but I assumed (perhaps wrongly) that Access/VB would
initialize at zero, unlike C which can do really weird things....

I remember the problem this caused me once when I forgot to initialize
the value. It worked fine the first time, but when the form was
opened again the value initialized to the value it had when the form
closed.
Oh Okay! take me to school, why don't ya? (just kidding!)

Sorry about that Pieter. I should have phrased my comments in a more
sensitive manner.

James A. Fortune

[Pieter Linden]

Not to worry - I'm not offended. I was just more focused on figuring
out the answer than coding 100% properly...