DAO behavior, something happened...

[docjohn52]

Below is a chunk of code, that was working
fine until I added the "'Mid(a$, 91, 10)" line...
The list control was listing over thirty smiths in the database.
I add the line and run a test, and now the Query acts like 'Distinct'.
It now only finds the first instance of smith and the k variable reports 1

It's not yanking a recordset anymore, just one record...

I did open the database, to verify the "ID" fieldname, but made no change,
and it doesnt matter whether the db is open or closed.

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsDebit = wrkJet.OpenDatabase(DBname)
Set Rst = dbsDebit.OpenRecordset("SELECT * FROM clients WHERE_
ClientLastName = """ + CustomerMaint.Text1(0).Text + """", dbOpenDynaset, ReadOnly)
While Rst.EOF = False
k = Rst.RecordCount ' this is a test line after failure

If CustomerMaint.Text1(1).Text = "" Then 'no firstname
a$ = Space$(100)
Mid(a$, 1, Len(Rst("ClientLastName")) + 2) = Rst("ClientLastName") + ", "
Mid(a$, Len(CustomerMaint.Text1(0).Text) + 3, Len(Rst("ClientFirstName"))) = Rst("ClientFirstName")
Mid(a$, 35, 4) = Right(Rst("ClientSSN"), 4)
'Mid(a$, 91, 10) = Right("0000000000" + LTrim(Str(Rst("ID"))), 10) ' this was the offending line
't$ = Right("0000000000" + LTrim(Str(Rst("ID"))), 10) 'this was to ck it - not production

List1.AddItem a$
Rst.MoveNext

Set wrkJet = Nothing
Set dbsDebit = Nothing
Set Rst = Nothing

Exit Sub
End If


Wend


Wha'happened!!!
Does anyone know of this happening to a DAO query, or have any ideas?

Oh, Access db 2000, opened/closed with 2003, VB6 sp6, refering DAO 3.6


John

 

[Golom]

Looks like you have displaced your closing of the workspace and recordset inside the While ... Wend loop. Because of that the recordset is being closed after you have processed only one record.

You need "Wend" immediately after "rst.MoveNext" instead of where you have it.