Weird problem with SQL RecordSet (DAO) [RESOLVED]

Hi,

When I use this code

VB Code:

...
Public RS As Recordset
-----------------------------
Dim SQL As String
 
SQL = "SELECT Table.NAM, Table.* From Table WHERE Table.NAM='" & NM$ & "'"
or
SQL = "SELECT DISTINCTROW CDP.NAM, CDP.* From CDP WHERE CDP.NAM='" & NM$ & "'"
 
Set RS = DB.OpenRecordset(SQL, dbOpenDynaset)
 
While Not RS.EOF
    If RS("NAM") = NM$ Then
        RS.Edit
        RS("Number") = 1
        RS("Status") = "Help"
        ....
        RS.Update
    End If
    RS.MoveNext
Wend

I get "Item not found in this collection" only for RS("NAM"). All of the other fields are there.

I've been trying to figure this one out all day! What the hell is wrong with that?

If I do...

VB Code:

Dim fld As Field, td As TableDef
            
            Set td = DB.TableDefs!cdp
 
            For Each fld In td.Fields: Debug.Print fld.Name: Next

It displays all of the fields including 'NAM'.

Please help! :sick:

Your sql statement causes the name of the NAM field to become "Table.Nam" within the recordset. You would need to reference the recordset field using RS("Table.Nam").

Note it depends on the sql statement. For instance this statement

Select Table.Nam, Table.Nam From Table names the two recordset fields as "Expr100" and "Nam". Other forms of the sql statement may generate different names.

Also, get familiar with the Locals Window (View menu) to help in debugging.

Thanks a lot!

It's funny that after all those years I never noticed that. But then again, I never was very good in SQL. I use Access to create my queries and modify them for VB, just don't tell anybody.

And thanks also for that Locals tip. I've been using VB since version 3, I think, and I never stopped in there for whatever reason. By the way, my RS RecordSet doesn't show in there and is declared as Public in a main module. I wonder why? I have a DS (for DynaSet) that shows with all the other stuff (also declared as Public).

Bert