using DAO to edit record

[grades]

Hello,
I have a report (rptLettersList) based upon a query (qryLettersIssued) that uses 2 tables to gather data.
The report prints out 5 or 10 results, and for each result I need an option to edit a relevant field in one of the tables (agent_letters).

This is what i currently have for the button (pretty much a wild guess):

Private Sub Command0_Click()
Dim rs As DAO.Recordset
Set rs = "agent_letters" 

rs.Edit
'i need to delete the data from the field
rs.Fields("warningLevel").Value = ""
rs.Update

Set rs = Nothing

End Sub

I have extremely limited experience working with DAO. I think this should be relatively simple but not too sure about the process.
Any help would be grand.
Thanks,

 

[VilaRestal]

To open a recordset:

Set rs = CurrentDb.OpenRecordset("agent_letters", dbOpenDynaset)

In this case you don't want to be modifying all the records in agent_letters, just the 5 or 10 you're after so you would filter for those records. And furthermore a recordset is not the best way to do it (but if you did do it with a recordset you would need to loop through the records in it, you're current code will just edit the first record). Just execute an update query:

CurrentDb.Execute "UPDATE qryLettersIssued SET warningLevel = NULL"

or perhaps

CurrentDb.Execute "UPDATE agent_letters SET warningLevel = NULL WHERE ............."

 

[grades]

Thanks for the advice.
To be clear, I don't want to update the 5 or 10 records, i want to only update one at a time. So each iteration of the report has it's own button.
I've tried your code for example:
CurrentDb.Execute "UPDATE agent_letters SET warningLevel = 0 WHERE ID = 2434"
ID is my unique ID in the table and 2434 is a random one I set for testing. It works as it should, but what would be the variable to find just the one record based on which iteration of the report i am in? Let's say I add the ID field onto the report but putting me.reportID as the variable says "Too few parameters, expected 1"

 

[AccessMSSQL]

Try this:

Private Sub Command0_Click()
Dim rs As DAO.Recordset
set rs = currentdb.openrecordset("agent_letters",dbopendynaset,dbopendynamic)
if not rs.eof then
do until rs.eof
rs.Edit
'i need to delete the data from the field
 rs.Fields("warningLevel") = ""
rs.Update
rs.movenext
Loop
end if
rs.close
Set rs = Nothing
End Sub

hope this helps!
http://www.accessmssql.com

 

[grades]

Hey,
in that example, I guess since the recordset is the table, it updated every record in the table.
Then again if i switch the recordset to the query, presumably it would update all the records in the query.
Is there any way to get it to update just the one query result?
You know what i mean, the report is very short, but since there are ten query results, the report repeats itself, and each time there is a button there. I need the button to affect only that particular record.
I hope I am being clear, again I don't know much about how to use recordsets.
-Grades

 

[VilaRestal]

So did you try:

CurrentDb.Execute "UPDATE agent_letters SET warningLevel = 0 WHERE ID = " & Me.ReportID

?

To do it the recordset way, you just "SELECT ... WHERE ..." when you open it:

Set rs = CurrentDb.OpenRecordset("SELECT warningLevel FROM agent_letters WHERE ID = " & Me.ReportID,dbOpenDynaset)
With rs
    .Edit
    !warningLevel = 0
    .Update
    .Close
End With

And then you wouldn't need to loop because there'd only be one record in it.

The CurrentDB.Execute method is much better. More efficient for you to type and for the computer to process. Only use recordsets in extreme cases.

 

[grades]

So did you try:

CurrentDb.Execute "UPDATE agent_letters SET warningLevel = 0 WHERE ID = " & Me.ReportID

?

Thaaats what i was looking for!
i had CurrentDb.Execute "UPDATE agent_letters SET warningLevel = 0 WHERE ID = Me.ReportID"

Thanks so much for that!
And thanks to AccessMSSQL, i am going to use your code to update my tables in other ways, too.