Am I missing a Dim statement?

[Grrrrrumpy]

Here's the code I was given for Access 2000 to:
Sort a membership file by Lastname, Firstname & Suffix --
Spin thru the file and renumber the Membership number --

Sub Renumber_Members()

Dim MemberNumber as Long
Dim rstMEMBERS As New ADODB.Recordset
Dim strSQL As String
Dim cn1 As New ADODB.Recordset

Set cn1 = CurrentProject.Connection

strSQL = "SELECT LastName, Firstname, Suffix, MemNum "
strSQL = strSQL & "From tblMEMBERS "
strSQL = strSQL & "ORDER BY LastName, FirstName, Suffix;"

rstMEMBERS.Open strSQL, cn1, adOpenKeyset, adLockOptimistic

If rstMEMBERS.EOF = True then Exit Sub

MemberNumber = 1

Do Until rstMEMBERS.EOF = True
rstMEMBERS!MemNum = MemberNumber
rstMEMBERS.Update
rstMEMBERS.MoveNext

MemberNumber = MemberNumber + 1
Loop

rstMEMBERS.Close
Set cn1 = Nothing
End Sub
===========================
I'm getting the following error message --

Run-time error '-2147217865(80040e37)':
The Microsoft Jet database engine cannot find the input table or query 'tblMEMBERS'. Make sure that it exists and that the name is spelled correctly.

The table named MEMBERS does exist.
Am I missing a Dim statement?

(I'm not familiar with Access... this would be soooo
easy in DBase/Clipper/Foxpro)

Can anyone help me?

 

[xlbo]

Yes but is the table called tblMEMBERS ??
try changing tblMEMBERS to MEMBERS

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[lespaul]

Exactly!! Look at your from clause:

strSQL = "SELECT LastName, Firstname, Suffix, MemNum "
strSQL = strSQL & "From tblMEMBERS "
strSQL = strSQL & "ORDER BY LastName, FirstName, Suffix;"




Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Grrrrrumpy]

Thanks folks...

I know nothing about Access/VBA
I noticed the "rst" in front of "MEMBERS" and thot it necessary for the recordset.
I assumed the "tbl" in front of "MEMBERS" was necessary
to indicate this was a table.

Wrong Assumption!!!!

Thanks again... Much Appreciated.

 

[HobbitK]

A prefix is not mandatory but it is good programming practice to include the Hungarian naming convention prefix for every object in your program. It makes reading the code much easier later on.
Michael

 

[xlbo]

The difference in this case being that rstMEMBERs is a VARIABLE whereas MEMBERS is the name of an actual table that is being referenced

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde