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

[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******@oaklan d.edu (James Fortune) wrote in message news:<a6******* *************** ****@posting.go ogle.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
====

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

[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.OpenRecordse t(sSQL, dbOpenDynaset)

and Sorted the table before running the function.

Works great!

Thanks Pieter & James!!

amy
===
am*******@veriz on.net (amywolfie) wrote in message news:<33******* *************** ****@posting.go ogle.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******@oaklan d.edu (James Fortune) wrote in message news:<a6******* *************** ****@posting.go ogle.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
====

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

[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********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ****@posting.go ogle.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...