Record updatng

[Croco]

Hi,

I think I am about to ask such a fundamental question that if you mind doesn't flip i will be most surprised.

When does the detail in a db table actually get "updated" after entries are made in a field?

Is it only when the form is closed? Or is there a little trick that can be set in the properties that can achieve this.

Sorry for such a simple question.....


Croco

 

[ajetrumpet]

When does the detail in a db table actually get "updated" after entries are made in a field?

what do you mean by DETAIL?

records are updated immediately if the form is bound

 

[Galaxiom]

records are updated immediately if the form is bound

Not quite. Values from bound controls are updated to the table when the current record changes.

 

[ajetrumpet]

Not quite. Values from bound controls are updated to the table when the current record changes.

IN advanced terms, yes. in beginner terms, no.

that's getting pretty specific, but good thing you posted the clarification I suppose.

 

[Croco]

Thanks to All...

In particular to "ajetrumpet" - the DETAIL I meant was the relevant field information. Once again - wrong language - sorry. Still a bit naive in the sense of the language.

I'll chase the "bound" suggestion up and if I eed to get back to you for a clarification I shall.

Thanks again...Croco

 

[Croco]

Hi,

I did a check of properties of the form and found it to be bound to the table required. As an experiment I built a query including all the fields being used on the relevant form.

In both cases I had to refresh the records in order to get a change to any field reflected in the table.

I take it therefre that this is the only way that a record is updated other than closing the form.

Sorry to labour the point. Can someone please clarify my thinking?


Croco

 

[Croco]

Hi,

I did a check of properties of the form and found it to be bound to the table required. As an experiment I built a query including all the fields being used on the relevant form.

In both cases I had to refresh the records in order to get a change to any field reflected in the table.

I take it therefre that this is the only way that a record is updated other than closing the form.

Sorry to labour the point. Can someone please clarify my thinking?


Croco

 

[Atomic Shrimp]

Hi,

I did a check of properties of the form and found it to be bound to the table required. As an experiment I built a query including all the fields being used on the relevant form.

In both cases I had to refresh the records in order to get a change to any field reflected in the table.

I take it therefre that this is the only way that a record is updated other than closing the form.

Sorry to labour the point. Can someone please clarify my thinking?


Croco

It is possible to force the record to be updated, even mid-way through editing a row, by manipulating the Dirty property of a bound form:

If Me.Dirty then Me.Dirty = False

You might do this on a timer event (not recommended though), or you can do it on a focus event - when the focus moves into or out of a specific control.

 

[Croco]

Re: Record updating

Atomic Shrimp, are you suggesting that from a user level point of view that the Record/Refresh may be the better way to leave the form.


And if not, can you please elaborate on the "on Dirty" property please? I have tried to follow the Microsoft Help on what little there seems to be (unless I am not looking in the right are) but can not follow their explanation. In particular the "Me. " component of the expression.
I have seen it used in a number of expressions in various explanations to different questions in the forum threads am do not have any idea what it is or ow to use it.

Hoping you can add little more of you wisdom...


Croco

 

[Atomic Shrimp]

'Me' is just a bit of convenient shorthand - when you're writing code in a form's own event modules, 'Me' just means 'this form', and saves you the hassle of having to explicitly name the form in your code.

The 'dirty' property of a form is 'true' if there are updates pending to the table bound to the form, otherwise the dirty property is false. And by assigning it to false, you force Access to apply any pending updates.

 

[boblarson]

And stated slightly a different way (but mostly the same):

ME

This is a programming shortcut which lets the programmer refer to the current CLASS object. Most of the time, in Access, you would be referring to a form or report using this. It can also apply to custom classes as well, but is less prevalent unless custom classes are being used.

Let's say you have a form named "frmMain" and you have a text box there called "txtEntry" and you wanted to refer to that text box in code. A fully-qualified reference would be Forms!frmMain.txtEntry but you could also refer to it using the shortcut (as long as the code is ON frmMain): Me.txtEntry

DIRTY

Dirty refers to a recordset that has pending updates or changes. A form can be "dirty" if someone adds a new record (not just goes to the new record area, but starts entering data), deletes something, or changes some existing data. A bound form can be "dirty" but an unbound form is never dirty, even if controls get changed there. As stated, it is the recordset which really is dirty, and so an unbound form does not have a recordset and therefore can't be dirty.

The code

If Me.Dirty Then Me.Dirty = False

forces a form to save the record. This is good code to use instead of

DoCmd.RunCommand acCmdSaveRecord

because the one that refers to Dirty, will ONLY attempt a save if the form is dirty and needs a save. The DoCmd one will attempt a save regardless and that can cause errors, if certain things exist - including default values.

Hope that also helps.

 

[gemma-the-husky]

you may be asking what is the event sequence

take a control on a form - there are a whole series of events that react with a controls - here's four of them, and the order in which they work

on entry
before update
after update
on exit

but if you want to get to an even more magnified level , you can actually use mouse events / key press events to process indivdiual intractions - eg you can disregard certain keypresses while working with a control

then there are similar events affecting an entire record. but there are a other important ones in addtiion - the before and after insert events, and the current events

there are also many events affecting the form as a whole

and you also have to consider the effect of making a different form active, without closing the current form.

amd you also have to manage the effect of run-time errors, to make your app robust.


so to get the programme to manage your data correctly, you have to interact correctly with the appropriate events - perhaps thats what you are really asking.

 

[Croco]

To the last 3 muskateers - you bring good news. That has collectively bought a new day into my life and it is still afternoon here (South Australia if you are interested). All these little snippets are beginning to make the big picture a little more focussed. Believe me...the default setting of my right eye was way out so I could never really get the left eye to synchronise at all. Thanks to all of you. I have a feeling another question is looming - I'll do some more research before I ask it though.


Croco

 

[gemma-the-husky]

ok

well lets say you want to datestamp a record to identify who did something to a record last

normally you would have fields for something like

lasteditedby
lasteditedon

now the right event to set this stuff is the forms beforeupdate event - this is the point immediately before the data is written -

so you can say

lasteditedby = currentuser (a function that picks up the username)
lasteditedon = now() (a function that returns the data and time)

you can also use the beforeupdate event to provide any important validation, as you can cancel the event if the validation fails. All this stuff needs some, but not a lot of VBA code

----------
if instead you want to do something else AFTER the changes are saved, then this stuff belongs in the forms AFTERUPDATE event.

every time you move to a new record, you also get a CURRENT EVENT -

---------
so if you need to make some fields active/inactive depending on settings, you often need to put this code into the current event (for when you get there the first time) AND the afterupdate event - in case you just changed something that affects these settings

 

[Croco]

Dear Gemma-the-husky (Dave),

Again many thanks - you are a mind reader too? A fine logical conclusion as to where I thought I may have to go in the future although this wasn't exactly the thought I alluded to earlier. Nevertheless, the notion yet again calrifies the "meaning" of some of these properties. When, and I mean when, I have to do the associated vba there will be a need to sort some "parsing", (if that's the right word) because from experience even though vba says "No, no, no...that's not going to work" the options that it provides is another language of its own. I'll get there though.

Thanks once again...

Croco