Table Update Help

[mlr0911]

I have the following code that is supposed to take a table and update the fields that are null with a value above it and change to the new value when the field isn't null and move to the next record:

Dim db As Database
Dim rs As Recordset
Dim strSQL As String

Set db = CurrentDb()
strSQL = "SELECT ID FROM tblInformation"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do Until rs.EOF
If Len(rs.ID) <> 0 Then
strDocName = rs.ID
Else
rs.Edit
rs.DocName = strDocName
rs.Update
End If
rs.MoveNext
Loop
rs.Close

However; I am getting an error message on line 10 "(If Len (rs.ID)<>0 Then" that states "Method or data member not found".

What am I doing wrong?

Thanks for your help.

 

[pbaldy]

To refer to a field in the data, bang not dot:

rs!ID

That should solve the first problem.

 

[mlr0911]

Thanks Pbaldy, it works...(after I changed 1 other issue I had in the code). Do you know of a way to add a seqencial number to the end of my rs!ID=strdocname?

For example
APP
APP1
APP2
BPP
BPP1
BPP2

Again, thanks for your help.

 

[pbaldy]

Sure; you can create an Integer variable and increment it in the loop:

intWhatever = intWhatever + 1

then

rs!ID=strdocname & intWhatever

and reset it as appropriate.

 

[mlr0911]

Gotcha, thanks again for your help....everything works as inteneded.

Have good day.