Update underlying table (1 Viewer)

kirkm

Registered User.
Local time
Tomorrow, 04:07
Joined
Oct 30, 2008
Messages
1,257
I'm a bit confused... My Form is bound to a table e.g. RecordSource = "Select * From tblExport"

If the value of a control is changed, the change doesn't immediately appear in tblExport until the Form is closed.
But what if you want it to ? Is this where .Dirty is set ? if yes does it belong in the controls after update event ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:07
Joined
Oct 29, 2018
Messages
21,359
Hi. The table doesn't reflect new data until the record is saved to the table. You can force a save whenever you want to commit the data into the table.
 

kirkm

Registered User.
Local time
Tomorrow, 04:07
Joined
Oct 30, 2008
Messages
1,257
I'm not quite with you - what or how do you "commit the data into the table" ? It seems to when closing the Form (but I don't want to close the Form)
 

Isaac

Lifelong Learner
Local time
Today, 08:07
Joined
Mar 14, 2017
Messages
8,738
Me.dirty= false
Will do.
You can use a command button on the form and code that line into the click event.
 

kirkm

Registered User.
Local time
Tomorrow, 04:07
Joined
Oct 30, 2008
Messages
1,257
The way I've designed this, the change should occur (into the table) immediately... without any extra click. So I was thinking of the after update event, but that means that for every control that might be changed. Or am I on the wrong track ?
 

vhung

Member
Local time
Today, 08:07
Joined
Jul 8, 2020
Messages
235
"It seems to when closing the Form (but I don't want to close the Form)"
maybe form settings
>try to use OnTimer then set TimeInterval=1000
>OnTimer: insert codes like "me.requery"
>see attachment
 

Attachments

  • ontimer.png
    ontimer.png
    59.1 KB · Views: 107

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,175
you can use DBEngine.Idle dbRefreshCache to Force flush
update to table and at same time refresh your
memory-copy of the recordset with the latest update.

see:
 

kirkm

Registered User.
Local time
Tomorrow, 04:07
Joined
Oct 30, 2008
Messages
1,257
Thanks for the replies/ideas. About to try.. Not sure where in the code would
DBEngine.Idle dbRefreshCache go. I'll experiment.
vhung, does that mean after 1000 intervals Me.Requery fires? What starts the process?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,175
you add it after each Save.

Me.Dirty = False
DBEngine.Idle dbRefreshCache
 

kirkm

Registered User.
Local time
Tomorrow, 04:07
Joined
Oct 30, 2008
Messages
1,257
What Save? No save is done (yet).
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,175
if you add a record, unless you Cancel it it will be Saved, correct?
if you Change something in existing record, unless you press ESC key several times, the record will be Saved when
you go to Next record, correct?

some events fire when you Save/Change and one of them is the Form's AfterUpdate event?

Private Sub Form_AfterUpdate()
DBEngine.Idle dbRefreshCache
End Sub

i have put a linked for this command, please visit it.
there are other sites as well, googling is the Key.
 

vhung

Member
Local time
Today, 08:07
Joined
Jul 8, 2020
Messages
235
"does that mean after 1000 intervals Me.Requery fires? What starts the process?"
GUESS
>That is auto run
>maybe supply also the cmdsave codes if applicable
>when you insert that ontimer always run for save to table
>that if your acForm settings is true to your target actable
>i used it many times
>well in this case, supply your codes to ontimer, if you have use calculations
 

kirkm

Registered User.
Local time
Tomorrow, 04:07
Joined
Oct 30, 2008
Messages
1,257
Seems difficult to explain, I'm not adding a record, or editing an existing one. The form has 2 recordsources, a temp and main.
If the temp one is in use and edited, I want that passed to the other recordsouce. Without the Form being closed and there's no 'next record' to go to.
But it looks like the controls after update event is doing what I want, just means a bit more typing. Sorry vhing I don't follow.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:07
Joined
May 7, 2009
Messages
19,175
is temp and main based on Same table?
if different then is Sync the right word?

if same table, you only need to Requery the other when you edit/add record on the other.
otherwise, you will need an Update Query to update the other.
 
Last edited:

kirkm

Registered User.
Local time
Tomorrow, 04:07
Joined
Oct 30, 2008
Messages
1,257
The temp one is one record in the main table. It's created by an Export stored procedure in this database (but remotely). The Form shows both via a recordsource change. If the temp one is active and an edit done it needs to be copied to the main, so when the RS toggles that field stays visible. It's all working ok, it was just the instant update needed. Actually a real software developer may do this quite differently, I just make it work. I didn't consider requery, I'll have a play and see if that does what's required. The reason for the 2 recordsouces was to see the current/new by a click.
 

Isaac

Lifelong Learner
Local time
Today, 08:07
Joined
Mar 14, 2017
Messages
8,738
The way I've designed this, the change should occur (into the table) immediately... without any extra click. So I was thinking of the after update event, but that means that for every control that might be changed. Or am I on the wrong track ?
Ok, that's an unusual requirement to some degree, but you could code each control's AfterUpdate event.
 

kirkm

Registered User.
Local time
Tomorrow, 04:07
Joined
Oct 30, 2008
Messages
1,257
I may not need the update event after all. Am I correct that Me.Dirty-False updates the Forms Record source to the now values in the controls ? This appears to be so but good to be sure. In which case this comment before the Record source changes will do it.
 

Isaac

Lifelong Learner
Local time
Today, 08:07
Joined
Mar 14, 2017
Messages
8,738
I may not need the update event after all. Am I correct that Me.Dirty-False updates the Forms Record source to the now values in the controls ? This appears to be so but good to be sure.
I'm not entirely certain I understand that question.

Me.Dirty = False will force the form to SAVE ... Meaning any bound controls' current values, will become the values stored in the Table to which the form is bound, at that moment. Yes.
If you mean will this command actually change the form's Recordsource - like the form used to be based to tblOne and now it's based on tblTwo - No, it has nothing to do with that.

In which case this comment before the Record source changes will do it
I'm not quite sure what you mean by that. Put simply, if you want the changes actually saved to disk - completely saved into the underlying Table - as soon as anyone finishes entering any particular value in a control (say, they finish typing a word in a textbox), you'll have to add that command to code which you place in that control's (Textbox's) AfterUpdate event. The event will fire generally once they leave that control (tab, exit, click somewhere else, etc).

If you just meant that you want the data to save to the Table at the point in time where someone navigates to another record on the Form, well, it does that anyway.
 

kirkm

Registered User.
Local time
Tomorrow, 04:07
Joined
Oct 30, 2008
Messages
1,257
Thanks Isaac. Confirms what I'm seeing.. goal is to save the current data before any RS change. There's no other record to Navigate to. Many fields may be changed so one command is ideal, preventing AfterUpdate for each.
 

Isaac

Lifelong Learner
Local time
Today, 08:07
Joined
Mar 14, 2017
Messages
8,738
I'm a little lost on your latest response but if my comments have helped in any way then I'm thankful for that . Best of luck in your project, let us know how you get on.
 

Users who are viewing this thread

Top Bottom