I need code to process an Access database

[Grrrrrumpy]

I'm a programmer, but have no knowledge of Access/VBA
I have a database "Members" (Access 2000). I want to sort by it by Lastname, Firstname & Suffix (Jr, Sr). I then want to go to the first record, place the value 1 in the field
MemNum, then move to the next record, increment the value
of that MemNum to 2, move to the next record, and do the same until the end of file.

I have a query created to open the database by Lastname,
Firstname and then Suffix. I assume I should be placing
the code in a module. I assume I should be accessing the
database via the query. Coding is not complicated but I'm
having trouble with Access 2000 syntax. Especially the
Dim statements. Can anyone help?

Thanks
Joe
Grrrrrumpy@tds.net

 

[arobbins100]

This will do the task for you, but don't you want to keep the existing member numbers as new member records are added?

If so make your field MemNum's Data Type AutoNumber! Otherwise.....
use this code

--------------------------------------

''''''''''''''''''''''
'We'll use a sub routine to first sort the records
'and then allocate our numbers
''''''''''''''''''''''
Sub SortMembers()

Dim lngMemNum As Long 'This is a long integer to hold the member number (long can be from -2147 * 10^6 to 2147 * 10^6)
Dim rstMembers As New ADODB.Recordset 'this is a recordset, a storage for our records that allows us to manipulate thme
Dim strSQL As String 'this is the string we use to hold the SQL that querys / modifies our records
Dim cn1 As New ADODB.Connection 'this is the connection to the database

'instantiat our connection to the database
Set cn1 = CurrentProject.Connection

'build up a query to get our data
'I like to keep the lines short & related to each functional sep
strSQL = "SELECT LastName, FirstName, Sufix, MemNum "
strSQL = strSQL & "FROM tblMembers "
strSQL = strSQL & "ORDER BY LastName, FirstName, Sufix;"

'open the recordset based on our sql
rstMembers.Open strSQL, cn1, adOpenKeyset, adLockOptimistic

'test for returned data, if the EOF (end of file) flag is true there is no data & we bail out!
If rstMembers.EOF = True Then Exit Sub

'we then loop through the records,
'incrementing the member number as we go (we'll use that lngMemNum here)
'& updating the records.

'set our initial member number up
lngMemNum = 1

'set up a do until...loop loop
Do Until rstMembers.EOF = True
'this writes our number in th field MemNum in the table directly!
rstMembers!MemNum = lngMemNum
rstMembers.Update

'move to the next record
rstMembers.MoveNext

'increment our member number
lngMemNum = lngMemNum + 1
Loop

'clean up by releasing the connection & recordset
rstMembers.Close
Set cn1 = Nothing
End Sub
--------------------------
THIS WILL RE-ORDER & RE-ASSIGN MemNum's EACH TIME YOU CALL IT
Andrew Robbins

 

[Grrrrrumpy]

Thanks...
I should have explained further.
Each month I add new members.
They want an alphabetical list AND
they want to renumber the membership
number so they only need one list.
One that is in alphabetical and
numerical order. You gave me what
exactly I needed.

Thank you very much.