Enter Date in Subform Automatically

[Rita]

I have a form (fSubsidy) with a field called "Pay Period Ending". Included in
this form is a subform (sfSubsidy). The user is only using this form to enter
a subsidy.

I would like to automatically fill the field "Date" in sfSubsidy with
whatever date is in "Pay Period Ending".

Example:
fSubsidy Form
Pay Period Ending: [9/5/08]
_________________________
sfSubsidy
Date: [9/5/08] Subsidy: [$1.05]
__________________________

THANKS!

 

[Rita]

I did create a table called DATE with a single field called DATE. I entered
9/5/08. I could also give the subsidy this date if that would make it any
easier. Is there a way of setting the default for the date field in sfSubsidy
to whatever date is in this field? That would be an easy fix if that's
possible.
THANKS!

 

[rquintal]

I did create a table called DATE with a single field called DATE. I entered
9/5/08. I could also give the subsidy this date if that would make it any
easier. Is there a way of setting the default for the date field in sfSubsidy
to whatever date is in this field? That would be an easy fix if that's
possible.
THANKS!

Date is a reserved word in Access, it's dangerous to use it as a field
name or variable name. Change it!

But you don't need the table at all. Since you already have the
payPeriodEnding field in the parent form, just set the value in the
subform from that.

I'd use the Before_Insert event of the subform to hold the following
code:
If Me.NewRecord then Me.[Pay Period Ending] = parent.[Pay Period
Ending]
(all on 1 line)

I have a form (fSubsidy) with a field called "Pay Period Ending". Included in
this form is a subform (sfSubsidy). The user is only using this form toenter
a subsidy.

I would like to automatically fill the field "Date" in sfSubsidy with
whatever date is in "Pay Period Ending".

Example:
fSubsidy Form
Pay Period Ending: [9/5/08]
_________________________
sfSubsidy
Date: [9/5/08]     Subsidy: [$1.05]
__________________________

THANKS!

 

[Steve]

You do not need the date field in the subform at all! Your tables should
look like:
TblPay
PayID
PayPeriodEnding
etc

TblSubsidy
SubsidyID
PayID
Subsidy

If in another form or in a report you need the data in TblSubsidy to include
the date, use a query that joins both tables and use the date in
PayPeriodEnding.

Your tables need revised!

Steve

 

[Rita]

THANKS SO MUCH! Previously the user was prompted to enter the pay period for
every form/report. I wanted to eliminate that, so I created a table called
Date and they would enter the date the pay period ends. I didn't realized
that was a bad name, so I renamed the table PAYPERIODDATE and the field to
PayPeriodDate. Then for my reports I use the date to run all the reports.
Every two weeks the date will of course change.

Here's a more detailed example:
fSubsidy Form
Pay Period Ending: [9/5/08] (this changes every 2 weeks)
MFID: [32433] (This is an auto number field that "connect" the main form to
subform
_________________________
sfSubsidy
Date Job Code Prog Units Pay Subsidy :
9/8/08 [32433] 123 691 4 .25 [cursor hits here]
9/8/08 [32433] 165 781 3 1.25 [ ]
9/5/08 [32433] 99 66 [ .50] would like to be here
*Using whatever is in Pay Period Ending--will change every 2 weeks
__________________________
They have a different form for entering all the daily work. They are
subsidized to bring their gross pay to $2.00 at the end of the pay period.
I'm using this form just to enter subsidies. I need to create a record in a
table.

I "locked" all the fields except for the subsidy field. On enter I clicked
[Event Procedure] and entered the following.
If Me.NewRecord Then Me.[Date] = Parent.[Pay Period Ending]

Your suggestion worked PERFECT!

Just one more question. Do you know how I would automatically drop down to
the last record in the subform? The records in the forms all vary according
to how many jobs they've done. When they open this form, I would love for
them to be sitting in a blank record so they could just enter the subsidy and
close the form (all other fields are locked.

THANKS!!!!!!!!!!!!

You do not need the date field in the subform at all! Your tables should
look like:
TblPay
PayID
PayPeriodEnding
etc

TblSubsidy
SubsidyID
PayID
Subsidy

If in another form or in a report you need the data in TblSubsidy to include
the date, use a query that joins both tables and use the date in
PayPeriodEnding.

Your tables need revised!

Steve

I have a form (fSubsidy) with a field called "Pay Period Ending". Included
in
this form is a subform (sfSubsidy). The user is only using this form to
enter
a subsidy.

I would like to automatically fill the field "Date" in sfSubsidy with
whatever date is in "Pay Period Ending".

Example:
fSubsidy Form
Pay Period Ending: [9/5/08]
_________________________
sfSubsidy
Date: [9/5/08] Subsidy: [$1.05]
__________________________

THANKS!

 

[Steve]

Rita,

Read my previous response again. You don't need the date field in the
subform!

As for you question....
Put the following code in the Open event of your subform:
' For Access2003 and previous
DoCmd.RunCommand acCmdRecordsGoToNew
' For Access2007
DoCmd.RunCommand acCmdRecordsGoToNew

Steve

THANKS SO MUCH! Previously the user was prompted to enter the pay period
for
every form/report. I wanted to eliminate that, so I created a table called
Date and they would enter the date the pay period ends. I didn't realized
that was a bad name, so I renamed the table PAYPERIODDATE and the field to
PayPeriodDate. Then for my reports I use the date to run all the reports.
Every two weeks the date will of course change.

Here's a more detailed example:
fSubsidy Form
Pay Period Ending: [9/5/08] (this changes every 2 weeks)
MFID: [32433] (This is an auto number field that "connect" the main form
to
subform
_________________________
sfSubsidy
Date Job Code Prog Units Pay Subsidy :
9/8/08 [32433] 123 691 4 .25 [cursor hits here]
9/8/08 [32433] 165 781 3 1.25 [ ]
9/5/08 [32433] 99 66 [ .50] would like to be
here
*Using whatever is in Pay Period Ending--will change every 2 weeks
__________________________
They have a different form for entering all the daily work. They are
subsidized to bring their gross pay to $2.00 at the end of the pay period.
I'm using this form just to enter subsidies. I need to create a record in
a
table.

I "locked" all the fields except for the subsidy field. On enter I clicked
[Event Procedure] and entered the following.
If Me.NewRecord Then Me.[Date] = Parent.[Pay Period Ending]

Your suggestion worked PERFECT!

Just one more question. Do you know how I would automatically drop down to
the last record in the subform? The records in the forms all vary
according
to how many jobs they've done. When they open this form, I would love for
them to be sitting in a blank record so they could just enter the subsidy
and
close the form (all other fields are locked.

THANKS!!!!!!!!!!!!

You do not need the date field in the subform at all! Your tables should
look like:
TblPay
PayID
PayPeriodEnding
etc

TblSubsidy
SubsidyID
PayID
Subsidy

If in another form or in a report you need the data in TblSubsidy to
include
the date, use a query that joins both tables and use the date in
PayPeriodEnding.

Your tables need revised!

Steve

I have a form (fSubsidy) with a field called "Pay Period Ending".
Included
in
this form is a subform (sfSubsidy). The user is only using this form to
enter
a subsidy.

I would like to automatically fill the field "Date" in sfSubsidy with
whatever date is in "Pay Period Ending".

Example:
fSubsidy Form
Pay Period Ending: [9/5/08]
_________________________
sfSubsidy
Date: [9/5/08] Subsidy: [$1.05]
__________________________

THANKS!

 

[Rita]

Thank you so much for the code--it operates just like I'd hoped it would!!!

I have another payroll entry form thatis used to enter the jobs the clients
do every day. This all gets store in a table picking up the MFID 32433. The
clients are guarantee to get paid $2.00. This particular form is just used to
enter a subsidy.

I do need a date field, because I'm only auto filling the subsidy
date--normally date is entered when they do the job. In the example below
they had a job on 9/9 and 9/8--all these jobs and the subsidy give me the pay
for the week. Subsidies are all entered on the same day so I just wanted to
use the Payroll Period ending date to eliminate data entry and just have them
enter the subsidy.

fSubsidy Form
Pay Period Ending: [9/5/08] (this changes every 2 weeks)
MFID: [32433] (This is an auto number field that "connects" the main form to
subform
_________________________
sfSubsidy
Date MFID Units Time Pay Subsidy
9/9/08 [4313] 691 1.75 1.5 ___
9/8/08 [4333] 781 .50 .25 ___
9/5/08*[5611] 66 .50
*Using whatever is in Pay Period Ending--will change every 2 weeks
__________________________
They have a different form for entering all the daily work. They are
subsidized to bring their gross pay to $2.00 at the end of the pay period.
I'm using this form just to enter subsidies. I need to create a record in a
table.

Thank you soooo much! I'm done and it works great! You saved me hours of
searching HELP!!!!!