Recordset to update a table

[john134]

I am trying to update a table using a recordset but I am having no luck.
There are 5 columns in the table that affect what I am trying to do.
They are: ConfCode, CC_B, CC_D, CC_ND, and CC_V. The first is
type Text and the others are Yes/No types. See the attached snippet
of the table.

For each record with a ConfCode of "D", I want the field CC_C to be
checked, i.e., True or Yes. Similarly with a ConfCode of "BVD", CC_B
to be checked, and so on.

The code runs without error, but the underlying table does not show
any check marks, even after clicking on "Refresh All".



Any assisttance appreciated.

Here is the code:


Dim myConnection As ADODB.Connection
Dim rst As New ADODB.Recordset
DoCmd.SetWarnings True
DoCmd.Echo True
Set myConnection = CurrentProject.Connection
rst.ActiveConnection = myConnection
rst.Open "SELECT * FROM [Individual Donor Table]"


'Check to see if the recordset actually contains rows
If Not (rst.EOF And rst.BOF) Then
rst.MoveFirst 'Unnecessary in this case, but still a good habit
Do Until rst.EOF = True
Select Case ConfCode
Case ConfCode = "D"
CC_D = True
Case ConfCode = "ND"
CC_ND = True
Case ConfCode = "BVD"
CC_B = True
Case ConfCode = "V"
CC_V = True
End Select


'Move to the next record. Don't ever forget to do this.
rst.MoveNext
Loop
Else
MsgBox "There are no records in the recordset."
End If


MsgBox "Finished looping through records."


rst.Close 'Close the recordset
Set rst = Nothing 'Clean up

[john134]

Correction: CC_C should read CC_D.

[pbaldy]

You aren't referring to the recordset. It would look like:

rst.Edit
rst!CC_D = True
rst.Update

That said, an update query would be more efficient. That said, I probably wouldn't have the checkbox fields, I'd have a related table that linked the conference code to the appropriate code.

[john134]

Tried the changes but I get the following message error on rst.Edit:

"Method or data member not found".

Can you elaborate more on the second part of your response?

John

[pbaldy]

I don't normally use ADO recordsets (I use DAO). Perhaps they don't have an Edit method. Try just the line setting a value:

rst!CC_D = True

[john134]

I switched to DAO and used your suggested edits in your first reply and everything works.

Thank you,

John

[pbaldy]

Happy to help John.