Confused about way Access saves data

[Fred Boer]

Hello:

I've been tinkering with some code that wouldn't run, and I need some help
because I don't think I understand something really fundamental about the
way Access saves data. I've been under the impression that once I exit a
bound control on a form, the data is immediately updated in the table. Is
this not correct? Here's what's happening: I have a form with a textbox.
Using the afterupdate event of the textbox, I run some code which pulls the
data from the field to which the textbox is bound. If I set a break point, I
can see, however, that the code isn't using the value I just entered, but
the previous value that existed in the table. Here's the code:

On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strAppTitle As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblsettings")
strAppTitle = DLookup("Apptitle", "tblSettings")

' Change the application title. Pull application title from settings
table.

db.Properties!AppTitle = strAppTitle

' Update title bar on screen.

Application.RefreshTitleBar

db.Close
Set db = Nothing
Set rs = Nothing

ExitPoint:
Exit Sub
ErrorHandler:
fncErrorMessage Err.Number, Err.Description
Resume ExitPoint


SO.... I set the value in the textbox to "My Latest Online Catalogue1", with
existing value "My Latest Online Catalogue". On exiting the textbox, the
code runs. I check the value of "strAppTitle" - it is the older, unchanged
value. I'm perplexed!

Thanks!
Fred Boer

 

[Arvin Meyer [MVP]]

Hello:

I've been tinkering with some code that wouldn't run, and I need some help
because I don't think I understand something really fundamental about the
way Access saves data. I've been under the impression that once I exit a
bound control on a form, the data is immediately updated in the table. Is
this not correct?

No. The data is saved when you move off the record, or explicitly save the
record. Until that time, the data is held in memory. That's why you can Undo
(escape) the changes.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Biz Enhancer]

Try saving the record before running the code.

 

[tina]

I've been under the impression that once I exit a

bound control on a form, the data is immediately updated in the table. Is
this not correct?

no, that's not correct. whether you're working in a form, or directly in a
table (not that you ever would!), new or changed data in the current record
is not saved to the table until you exit the *record*. in a form, that
happens when you save the record via code or by using the toolbar or
menubar, move from one record to another, move from a main form into a
subform, or close the form. merely exiting a control does not save the data,
unless exiting the control also moves off the record entirely (when it's the
last control in the Tab order, for instance).

of course, you can run code to save the current record, in the control's
AfterUpdate event. or you could change your code slightly, to

strAppTitle = Me!TextboxName

that'll capture the value you entered in the form, before the record is
saved to the underlying table. btw, was that only *partial* code that you
posted? you opened a DAO.Recordset, but didn't use it anywhere in the posted
code, so i just wondered. if the code you posted is complete, you could also
skip opening a DAO.Database, and just use

CurrentDb.Properties!AppTitle = Me!TextboxName
Application.RefreshTitleBar

hth

 

[Fred Boer]

Well, I feel sheepish! Thanks to all for setting me straight. Funny that I
should have been so wrong about that for so long! Sigh.

And, yes, Tina, that was a section taken from a longer piece of code.
However, I cannot tell a lie: it was incorrect in the original setting as
well! Double-sigh.

Thanks, again!

Fred

 

[John Vinson]

Hello:

I've been tinkering with some code that wouldn't run, and I need some help
because I don't think I understand something really fundamental about the
way Access saves data. I've been under the impression that once I exit a
bound control on a form, the data is immediately updated in the table. Is
this not correct?

It is not. It *couldn't* be, if any other control on the Form were
bound to a required field - it would mean that you would need to save
a record to disk with required fields undefined.

The record is saved when you close the Form; move off the current
record to some other record (including a NEW new record); or
explicitly save the record (with Shift-Enter or with VBA code,
including either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty = True Then Me.Dirty = False


John W. Vinson[MVP]

 

[tina]

Double-sigh.

LOL we all have days like that, Fred; if it's any consolation, you have
plenty of - if not good - company! <g>

 

[Tony Toews]

I've been tinkering with some code that wouldn't run, and I need some help
because I don't think I understand something really fundamental about the
way Access saves data. I've been under the impression that once I exit a
bound control on a form, the data is immediately updated in the table. Is
this not correct?

As a visual indicator of the record update status look for the
triangle or pencil in the record selector area which is to the left of
the form.

As soon as you start to insert or update a record you should see the
triangle change to a pencil.

Unless, of course, you have the Record Selectors property on the form
turned off.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm