Access VBA Help: Updating a Table

[intern2012]

Hi,

I'm trying to write a VBA function (for a Command Button on a Form) that edits a table. I need the function to set one field to a common value for all records. The value is entered into a textbox on the form. I've poked around the internet looking for hints, and this is what I have:

Sub Command55_Click()
Dim r As Recordset
Set r = CurrentDb.OpenRecordset("INVOICE_NEW")
CurrentDb.OpenRecordset ("INVOICE_NEW")
r.MoveFirst
r.Edit
r.Fields("WEEK_NBR") = Me.WEEK_NBR_BOX.Value
r.Update
r.MoveNext
End Sub

But it doesn't appear to do anything at all. The MoveNext command is there because ultimately I want to have a loop that applies the same update to all records in the table. Before I even attempt that, though, I want to know that this much of it works. I have Excel VBA experience, and I've gotten a few things to work in Access before, but I'm still pretty new at this. Any help would be appreciated. Thanks.

 

[pbaldy]

It would be far more efficient to execute an update query:

CurrentDb.Execute "UPDATE INVOICE_NEW SET WEEK_NBR = " & Me.WEEK_NBR_BOX.Value, dbFailOnError

 

[intern2012]

Thanks for the tip. I tried to create an Update Query that pulled the data right from the textbox on the form (using Query Design, not VBA), but instead the Update Query asks for a parameter value via pop-up. Not quite what I had intended, but sufficient for my purposes. Thanks!

 

[pbaldy]

If it asked for a parameter, either the form wasn't open or something was spelled wrong. The parameter prompt is Access telling you it can't find something.

 

[intern2012]

Oops, turns out I had entered the name of the textbox wrong. Now it works as intended. Thanks for your help.

 

[pbaldy]

No problem, and welcome to the site!