How can a form's VBA code cause a commit

  • Thread starter Thread starter Jimbo213
  • Start date Start date
J

Jimbo213

I'd like to cause a commit as soon as the field value is changed.
I want to stay in the same record.

I found this in a posting but it doesn't work:

Form_FORMNAME.Refresh

Suggestions?
 
Hi Jimbo

You need to save the current record.

Either
Me.Dirty = False
or
DoCmd.RunCommand acCmdSaveRecord
 
Much better than my workaround - which should get a chuckle from you.

I created a new macro:
step 1 = GoToRecord Next
step 2 = GoToRecord Previous

I attached this macro to the field's OnChange event.

Wait - did I hear a laugh 1/2 way around the world?
Your answer is much better

Thanks for your reply & assistance.
Jimbo213


Graham Mandeno said:
Hi Jimbo

You need to save the current record.

Either
Me.Dirty = False
or
DoCmd.RunCommand acCmdSaveRecord
Click to expand...
 
Hey Graham

I assume your solution is for VBA code in an OnChange event
Is there any code I can put in the OnChange event line on the properties box
that will cause a commit?

Thanks for your reply & assistance.
Jimbo213


Graham Mandeno said:
Hi Jimbo

You need to save the current record.

Either
Me.Dirty = False
or
DoCmd.RunCommand acCmdSaveRecord
Click to expand...
 
LOL! Laugh? Would I be so cruel? :-)

You should use the AfterUpdate event, not the Change event, because Change
fires every time you press a key to change the text in a textbox, while
AfterUpdate fires only when you complete the edit and move to a different
control.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Jimbo213 said:
Much better than my workaround - which should get a chuckle from you.

I created a new macro:
step 1 = GoToRecord Next
step 2 = GoToRecord Previous

I attached this macro to the field's OnChange event.

Wait - did I hear a laugh 1/2 way around the world?
Your answer is much better

Thanks for your reply & assistance.
Jimbo213


Graham Mandeno said:
Hi Jimbo

You need to save the current record.

Either
Me.Dirty = False
or
DoCmd.RunCommand acCmdSaveRecord
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jimbo213 said:
I'd like to cause a commit as soon as the field value is changed.
I want to stay in the same record.

I found this in a posting but it doesn't work:

Form_FORMNAME.Refresh

Suggestions?
Click to expand...
Click to expand...
Click to expand...
 
See my previous reply for why you should use AfterUpdate, not Change.

To execute this code directly from the event property (as distinct from an
event procedure) you must wrap the code up in a function in a standard
module:

Public Function SaveRecord(f as Form)
On Error Then Goto ProcErr
If f.Dirty Then f.Dirty = False
ProcEnd:
Exit Function
ProcErr:
MsgBox Err.Description "Cannot save record"
Resume ProcEnd
End Function

You can then put the following in the AfterUpdate cell of the properties
sheet:

=SaveRecord([Form])
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Jimbo213 said:
Hey Graham

I assume your solution is for VBA code in an OnChange event
Is there any code I can put in the OnChange event line on the properties
box
that will cause a commit?

Thanks for your reply & assistance.
Jimbo213


Graham Mandeno said:
Hi Jimbo

You need to save the current record.

Either
Me.Dirty = False
or
DoCmd.RunCommand acCmdSaveRecord
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jimbo213 said:
I'd like to cause a commit as soon as the field value is changed.
I want to stay in the same record.

I found this in a posting but it doesn't work:

Form_FORMNAME.Refresh

Suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Jimbo213 said:
Hey Graham

I assume your solution is for VBA code in an OnChange event
Is there any code I can put in the OnChange event line on the properties
box
that will cause a commit?
Click to expand...

Use the after update event. The on change will fire for every keystroke. I
do have a few invoicing forms etc. in which I want the forms "totals" at the
bottom (a continues sub form) to update immediate, and thus I force a disk
write right after the user enters data.

You can use

me.refresh

however, the preferred syntax is:

if me.dirty = true then me.dirty = false
 
Hi Graham, all..

this thread was a good help to me also, so wanted to say thanks for posting.

So, thanks.

: )

Niamh, Kerry

Graham Mandeno said:
Hi Jimbo

You need to save the current record.

Either
Me.Dirty = False
or
DoCmd.RunCommand acCmdSaveRecord
Click to expand...
 
Back
Top