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*******@veri zon.net> wrote in message
news:33******** *************** ***@posting.goo gle.com...

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*******@veri zon.net> wrote in message
news:33******** *************** ***@posting.goo gle.com...

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*****@localh ost.not> wrote in message news:<gV******* *********@nwrdd c01.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*******@veri zon.net> wrote in message
news:33******** *************** ***@posting.goo gle.com...

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 UpdateCounterIn Table()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordse t("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 AssignSequenceN umber()

'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.OpenRecordse t("SELECT DESCRIPTION, SEQUENCE FROM
tblExportParent ORDER BY DESCRIPTION", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("SEQU ENCE") = 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********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ****@posting.go ogle.com>...

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

Option Compare Database
Option Explicit

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

Set db = CurrentDb
Set rs = db.OpenRecordse t("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*******@veriz on.net (amywolfie) wrote in message news:<33******* *************** ****@posting.go ogle.com>...

Hi Peter:

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

Function AssignSequenceN umber()

'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.OpenRecordse t("SELECT DESCRIPTION, SEQUENCE FROM
tblExportParent ORDER BY DESCRIPTION", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("SEQU ENCE") = 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********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ***@posting.goo gle.com>...

am*******@veriz on.net (amywolfie) wrote in message news:<33******* *************** ****@posting.go ogle.com>...

Hi Peter:

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

Function AssignSequenceN umber()

'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.OpenRecordse t("SELECT DESCRIPTION, SEQUENCE FROM
tblExportParent ORDER BY DESCRIPTION", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("SEQU ENCE") = 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*******@veriz on.net (amywolfie) wrote in message news:<33******* *************** ****@posting.go ogle.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*****@localh ost.not> wrote in message news:<gV******* *********@nwrdd c01.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*******@veri zon.net> wrote in message
news:33******** *************** ***@posting.goo gle.com...

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*******@veriz on.net (amywolfie) wrote in message news:<33******* *************** ****@posting.go ogle.com>...

Hi Peter:

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

Function AssignSequenceN umber()

'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.OpenRecordse t("SELECT DESCRIPTION, SEQUENCE FROM
tblExportParent ORDER BY DESCRIPTION", dbOpenDynaset)

'--set initial counter value
Do Until rs.EOF
rs.Edit
lngCounter = lngCounter + 1
rs.Fields("SEQU ENCE") = 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 tblExportParent 2, 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 tblExportParent 2 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