MS Access - Write to Table Immediately After Changing Value in Form

Question

Is there a way to make Access write values immediately to a table when changing values in a form? Right now, I have to change a value in the form, and then click off the field onto the subform before the value is written to the corresponding table.

Sounds like no big deal, but certain controls require current data to give valid options. If the user doesn't know enough to click into the subform then the data they view could be out-of-date.

I cannot think of any suitable event, you certainly do not want to update the table in a change event, and `after update` requires that you move from the control. How about including a refresh button of some sort the updates the table? — Fionnuala, Mar 01 '12 at 19:07
There is a combo drop down that pulls a ticket number, ticket status, and most recent activity on that ticket (which is written by the subform). Users can change the status of the ticket but the combo drop down won't reflect the change (and neither does the table source, in my testing) until they move off the record entirely. Moving off the record entirely includes changing records or clicking the subform. The combo drop down will always update EVENTUALLY, but I'd like it immediate. I don't want to make the excuse about Access not being perfect. I just want it to work. :) — Flat Cat, Mar 01 '12 at 19:47
@Ben unfortunately Access isn't perfect, the users have to get used to that realization. :) — Taryn, Mar 01 '12 at 19:59
I suppose its not the best option but you could use a (form-)timer event in combination with Me.Dirty = False, though I don't know what the impact is on a field with current focus. — Christian, Mar 02 '12 at 02:09

score 2 · Answer 1 · answered Mar 01 '12 at 19:15

The behavior in your requirement are asking for is by default how access functions. In most typical scenarios your master form is going to be the parent table, and as a result before you can add child records to such a relational setup, then the parent record would have to be written and saved to disk.

And the reverse while not a requirement is also the default operation for access.

In other words it's not clear why when the focus moves from your parent form to a sub form, that the parent form's record is not been written to the table.

So something in your setup is incorrect here, or you'll have to expand on the behavior you're witnessing.

By default changing the focus from the main form to a sub form will cause the main form record to be written to the table, and changing the focus from a sub form tool main form as a general rule should also cause a sub form record to be written to the table.

Fionnuala · Answer 2 · 2012-03-01T20:09:44.637

2

If you are matching a combo box, you can use that as your link master field, that is, the name of the control itself. Your subform should update as soon as you select a ticket with the combo.

Link Master Fields: Combo1
Link Child Fields:  Field1

edited Mar 01 '12 at 20:09

answered Mar 01 '12 at 20:03

Fionnuala

90,370
7
114
152

score 0 · Answer 3 · edited Feb 07 '15 at 05:17

I was also facing the same problem, but in another scenario. I am not having sub-form, but a datasheet view of the same table on a split screen, which was not updating immediately after save button. On pressing DUPLICATE button, instead of getting duplicate data of latest record, I was getting pointer shifted to first record, and then the data from the first record was getting duplicated.

By inserting

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

before data duplicating code, I am now getting the data updated immediately with record pointer stick to the current record, and the same last record gets duplicated as required.

Sharing the experience.

Thanks ChrisPadgham

score 0 · Answer 4 · edited Sep 22 '16 at 19:35

0

Just use this in your form model:

Private Sub {your textbox name her}_AfterUpdate()
    Me.Refresh
End Sub

edited Sep 22 '16 at 19:35

Sardar Usama

19,536
9
36
58

answered Sep 22 '16 at 17:56

medjahed mohamed

11
1

score 0 · Answer 5 · answered Oct 27 '20 at 07:28

Essentially: in the AfterUpdate event of every desired control execute a acCmdSaveRecord command.

So:

In the design view of the form click on your control.
Menu > Design > Property Sheet (to bring up the property sheet for your control)
Property Sheet > Event [tab] > After Update. Choose "[Event Procedure]". Click on the elipsis "..." to take you through to the Code-Behind-Form VBA dev environment.
Supply your code as follows ...

Private Sub [Control Name]_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord
End Sub

E.g.

Private Sub cboFinancialYear_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord
End Sub

Repeat the above for every relevant control on the form.

The acCmdSaveRecord avoids an unecessary visual refresh of the current form, as when using Me.Refresh (which @medjahed mohamed has suggested).

score 0 · Answer 6 · answered Mar 01 '12 at 21:52

0

You can force the save of the record when a user moves off a field in the Lost_Focus event.

Private Sub MyField_LostFocus()

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

answered Mar 01 '12 at 21:52

ChrisPadgham

860
5
4

DoMenuItem was replaced in Access 2000 and only included since for backward compatibility : http://msdn.microsoft.com/en-us/library/aa203634(v=office.10).aspx – Fionnuala Mar 01 '12 at 23:47
Funny, Access wizard still uses it for this purpose, which is how I generated the command above – ChrisPadgham Mar 02 '12 at 03:52
Yes it does, which is why MVPs tell you to be very careful with Access wizards, for example, Bob Larson in http://www.tech-archive.net/Archive/Access/microsoft.public.access.gettingstarted/2008-07/msg00093.html – Fionnuala Mar 02 '12 at 09:40

MS Access - Write to Table Immediately After Changing Value in Form

6 Answers6

Linked