Re: [MS_AccessPros] How to add timestamp properly when adding and/or updating a form

Thanks John!!!

'Before Update' was the clue that finally solved everything in one stroke!

Regards
Dennis

16 okt 2011 kl. 15:22 skrev John Viescas:

> Dennis-
>
> My apologies. When you set the field in After Update, you're dirtying the
> record again! You need to do the update in the Before Update event of the form
> - that fires just before a new or changed record gets saved.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Dennis Davidsson
> Sent: Sunday, October 16, 2011 3:06 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] How to add timestamp properly when adding and/or
> updating a form
>
> John,
>
> I now changed to specify Timestamp = Now only for the event After Update.
> But when I have made some change to the contact person form and then click on a
> button on the form to save the record, (this is a button that I created via form
> design guide) I get an error message from Access saying: "The RunCommand was
> interrupted". The form is then locked, i.e. I cannot choose the record selector
> to choose another contact person record under the same company. When I click on
> the red X to close the form, I get a message telling me the record cannot be
> updated. However when I try again afterwards, the affected record is there ok,
> and the timestamp has been updated.
>
> What shall I do?
>
> Dennis
>
> 16 okt 2011 kl. 13:58 skrev John Viescas:
>
> > Dennis-
> >
> > On Change is overkill. That will fire with each keystroke!
> >
> > There shouldn't be any reason that After Update doesn't work, even if the only
> > changes you made to the data were through code. Are you meaning to update the
> > Timestamp in the company record whenever you change one of the contacts? If
> so,
> > you'll have to write additional code in the After Update event of the contacts
> > form to also change the value in the related companies row.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Dennis Davidsson
> > Sent: Sunday, October 16, 2011 1:42 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] How to add timestamp properly when adding and/or
> > updating a form
> >
> > Thanks John for your fast answer!
> >
> > However, now I think I have found what causes me the confusion!
> >
> > I have a form that I use to register new companies in my address table.
> > On this form I also have a button on which I click to open another form in
> which
> > I register a contact person of this company.
> > The underlying tables are connected in a one-to-many relation meaning I can
> have
> > several contact persons related to one and the same company.
> > The primary key of the address table is called Adressnr, and it appears as
> > secondary key in the contact person table.
> > Now, among the fields that I present on the contact person form is also the
> > Adressnr.
> > This field I want to have automatically filled in by Access, when I click the
> > button to open the contact person form to enter a new contact person. To
> > accomplish this, I have added the following piece of VBA code on the Event
> > property "Private Sub Form_Current()"
> >
> > 'If IsLoaded("Adresstable") Then
> > Me!Adressnr = Forms!Adresstable!Adressnr
> > End If
> >
> > This piece of code works fine for me, but it seems as if its very presence
> > prevents the 'Timestamp = Now' to be processed as I want.
> > What shall I do to solve this problem?
> >
> > Moreover, I have been using the event property 'On Change' so far to get
> > Timestamp = Now.
> > Is that ok instead of 'After Update, as you suggested?
> >
> > Regards
> > Dennis Davidsson
> >
> > 16 okt 2011 kl. 12:27 skrev John Viescas:
> >
> > > Dennis-
> > >
> > > After Update is all you need. When you change and save a row, After Update
> > > fires. When you create a new row, After Update fires followed by After
> Insert.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Dennis
> Davidsson
> > > Sent: Sunday, October 16, 2011 12:13 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] How to add timestamp properly when adding and/or
> > > updating a form
> > >
> > > Hello,
> > >
> > > I have a table in which I have included a timestamp column in order to keep
> > > track of every time I add a new record or update an existing record.
> > > How shall I specify the Event properties for the form that I use to handle
> my
> > > table?
> > > I know that I shall add a Visual Basic line like 'Timestamp = NOW'.
> > > But to which of all the possible Event properties shall I add this line?
> > > Shall I add it to all the following:
> > > After Insert
> > > After Update
> > > On Change
> > > Or is there even more properties to consider?
> > >
> > > I have tried some of these examples, but I am confused by the behavior of
> > > Access. I cannot quite figure out the logic behind.
> > >
> > > Regards
> > > Dennis Davidsson
> > >
> > > [Non-text portions of this message have been removed]
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> >
> > [Non-text portions of this message have been removed]
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/