By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,964 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,964 IT Pros & Developers. It's quick & easy.

Entering Data In A Form

P: n/a
I'm designing a basic form for data entry into one main table. There
are two fields in the table that I would like to be automatically
populated, not user-entered, when the record is posted.

One is the current system time as a Date/Time value, the other is the
current system username as a Text value.

Being _completely_ new to MS Access, how/where do I set the form to do
this when it posts a record? Also, can I distinguish between an INSERT
and an UPDATE? That way I could have one pair of fields for "record
created" and another for "record last updated."

For reference, I'm using MS Access 2002. The form was created using
the wizard and the only thing I've edited, other than cosmetic changes,
is replacing one of the fields (a foreign key to another table) with a
ComboBox for a support table.

I'm sure this is a terribly trivial task (it's just a matter of getting
the values from the system and putting them into the queries that the
form uses), I'm just entirely unfamiliar with the MS Access environment
and don't know where to look.

A quick nudge in the right direction would be much appreciated. Thank
you.
-cyber0ne

Aug 12 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
to date/time stamp each record as it's created, you can set a Default Value
on the field at the table level, in the field's DefaultValue property, or at
the form level, in the DefaultValue property of the control bound to the
field, or in the form's BeforeInsert event procedure. the syntax for setting
the property in table or form control is

=Now()

the syntax for setting the field's value in a procedure is

Me!CreatedOnFieldName = Now

substitute the correct field name, of course. to get the current user's
system ID, you can use the procedure found at
http://www.mvps.org/access/api/api0008.htm. paste the procedure into a
public module in your database, and call it from the form's BeforeInsert
event procedure, as

Me!CreatedByFieldName = fOSUserName

to update the "last modified" fields, run the code in the form's
BeforeUpdate event procedure - AFTER any code that may cancel the event -
as,

Me!LastModifiedFieldName = Now
Me!ModifiedByFieldName = fOSUserName

again, substitute the correct field names, of course.

hth
"cyber0ne" <cy*******@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...
I'm designing a basic form for data entry into one main table. There
are two fields in the table that I would like to be automatically
populated, not user-entered, when the record is posted.

One is the current system time as a Date/Time value, the other is the
current system username as a Text value.

Being _completely_ new to MS Access, how/where do I set the form to do
this when it posts a record? Also, can I distinguish between an INSERT
and an UPDATE? That way I could have one pair of fields for "record
created" and another for "record last updated."

For reference, I'm using MS Access 2002. The form was created using
the wizard and the only thing I've edited, other than cosmetic changes,
is replacing one of the fields (a foreign key to another table) with a
ComboBox for a support table.

I'm sure this is a terribly trivial task (it's just a matter of getting
the values from the system and putting them into the queries that the
form uses), I'm just entirely unfamiliar with the MS Access environment
and don't know where to look.

A quick nudge in the right direction would be much appreciated. Thank
you.
-cyber0ne

Aug 12 '06 #2

P: n/a
On 12 Aug 2006 10:19:02 -0700, "cyber0ne" <cy*******@gmail.comwrote:

The Form_BeforeInsert event fires when a new record is added.
The Form_BeforeUpdate event fires just before a modified record is
updated.
Those would be the ideal events to put your date/user created and
date/user updated logic.
Bring the form in design mode, choose properties at the form level
(click in the square in the far upperleft of the form), choose events
in the properties tab. Select your event, choose "Event Procedure"
from the dropdown, and click the ... button to the right of it to get
to the VBA event procedure.

To set the date, write code like:
DateCreated = Date()
or:
DateCreated = Now()
(assuming your field name is DateCreated)
Or you can set this default in the table design, DefaultValue property
of the field.

To set the username, write code like:
UserCreated = fOSUserName()
and use code from here:
http://www.mvps.org/access/api/api0008.htm

-Tom.

>I'm designing a basic form for data entry into one main table. There
are two fields in the table that I would like to be automatically
populated, not user-entered, when the record is posted.

One is the current system time as a Date/Time value, the other is the
current system username as a Text value.

Being _completely_ new to MS Access, how/where do I set the form to do
this when it posts a record? Also, can I distinguish between an INSERT
and an UPDATE? That way I could have one pair of fields for "record
created" and another for "record last updated."

For reference, I'm using MS Access 2002. The form was created using
the wizard and the only thing I've edited, other than cosmetic changes,
is replacing one of the fields (a foreign key to another table) with a
ComboBox for a support table.

I'm sure this is a terribly trivial task (it's just a matter of getting
the values from the system and putting them into the queries that the
form uses), I'm just entirely unfamiliar with the MS Access environment
and don't know where to look.

A quick nudge in the right direction would be much appreciated. Thank
you.
-cyber0ne
Aug 12 '06 #3

P: n/a
I knew it would be easy :)

Most importantly, this has given me a slightly better idea of how
things are done in the VB for MS Access world.

Thanks, both of you, for your help!
-cyber0ne

Aug 12 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.