Updating Fields on clicking command button

[Richard B]

I am trying to get a command button on a form to update one of the fields on the same form when it is clicked. All I want it to do is change the value in the field to a the word "submitted" each time it is clicked. I only want it to change the value in the current record.

I have tried the following code:

Dim db As Database
Set db = CurrentDb()
' Updating "Request Status" Field to submitted
Dim rec As Recordset
Set rec = db.OpenRecordset("tblBase")
DoCmd.Requery
' Copy the current record to the copy buffer
rec.Edit
' Make changes to current record
rec("Request Status") = "Submitted"
' Save contents of copy buffer
rec.Update

But it doesn't do what I hoped.

Any help would be much appreciated.

 

[Ripley]

instead of update, try using the Refresh command

 

[Ripley]

or if you are doing what i think your doing, which is simply click a button and it will change the value of a field, the code below should help you:

Me.Form.[Request Status] = "Submitted"

that will change the value to "Submitted" as long as the field Request Status is on the form where the button is.

If you want to change the value on a diffent form, then you will need this:

Private Sub buttonname_Click()
DoCmd.OpenForm "Name Of Form Where Field Is"
DoCmd.GotoRecord , , acNewRecord
Forms![Name Of Form Where Field Is].[Request Status] = "Submitted"
DoCmd.Close acForm "Name Of Form Where Field Is, acSaveYes
End Sub

 

[Richard B]

Oh so simple - many thanks for your help

 

[Ripley]

no problem