Programmatically Change Field Value and Save the Record (1 Viewer)

whdyck · Oct 12, 2012

I'm using MS Access 2003.

Probably a dumb question, but I'm not finding the answer anywhere.

I have a command button that I'd like to use to logically delete the record and update the change to the database: When the user clicks the button, the Clicked event would change the value in the Status field to "D", then update the record.

I can get it to change the value in the textbox to "D", but I'm not sure how to programmatically update the record. Instead, after clicking the button, the Edit ("pencil") icon displays and I have to click off the record to save the change.

How can I do this in code?

Thanks.

Wayne

Rat1sully · Oct 12, 2012

could you not just update the table with sql?

Code:

docmd.runsql "UPDATE tblName SET tblName.fldToChangeName = "D" WHERE tblName.fldRecordID  = recordID;"

whdyck · Oct 12, 2012

That did it.

I thought maybe there was a simpler way, but perhaps not.

Thanks.

Wayne

BigHappyDaddy · Oct 12, 2012

You obviously got an answer that works, but a simple command to force the save of a record:

Code:

DoCmd.RunCommand acCmdSaveRecord

whdyck · Oct 12, 2012

BigHappyDaddy said:
You obviously got an answer that works, but a simple command to force the save of a record:

Code:

DoCmd.RunCommand acCmdSaveRecord

I like your solution. It works and is simple.

Thanks!

Wayne

gemma-the-husky · Oct 13, 2012

often you need to save a record before running another procedure

i am often torn between just saving the record with

runcommand accmdsaverecord
(or even simply me.dirty=false)

and the alternative of asking the user whether he wishes to save

Code:

if me.dirty then
    if msgbox("save record ",vbyesno)=vbno then
        exit sub
    end if
    runcommand accmdsaverecord 
end if

the latter gives the user an extra check, but requires another keypress also.

bear mind that once the record is saved, there is no undo facility available

bear in mind also that the saverecord can and will fail if other edits have introduced some constraint error - so you often need error handling as well.

Rat1sully · Oct 15, 2012

Ah fair didn't know about that method as all my forms pull data from different tables all over the place and updates have to be saved with a button press and only certain fields are allowed to be edited so for me that code is the simpilest way I've found

BigHappyDaddy · Oct 15, 2012

Rat1Sully: No, I actually think for the original question, yours was a better solution. He stated that he wants a User to click a button, then automatically run some updates. whdyck mentions that:

I can get it to change the value in the textbox to "D" ... after clicking the button, the Edit ("pencil") icon displays and I have to click off the record to save the change.

This leads me to believe he is using code to assign the value of "D" into the control that is bound to the status field of the table/query. If he is using code to assign "D" to the control, then I like your way better. If he is allowing the user to manually enter in the value of "D", then I like the idea of forcing a save of the record. I think my solution might have been simpler for what he already written, but i think yours is better for the action that was to actually take place. Make sense?

Rat1sully · Oct 16, 2012

yeah I follow that makes sense really if you're clicking a button to do it may as well update the single field in the table directly, if you're typing it in have it save on lost focus sort of thing

Programmatically Change Field Value and Save the Record (1 Viewer)

whdyck

Registered User.

Rat1sully

Unhappy Forced codemonkey

whdyck

Registered User.

BigHappyDaddy

Coding Monkey Wanna-Be

whdyck

Registered User.

gemma-the-husky

Super Moderator

Rat1sully

Unhappy Forced codemonkey

BigHappyDaddy

Coding Monkey Wanna-Be

Rat1sully

Unhappy Forced codemonkey

Similar threads

Users who are viewing this thread