Automatically generate content on Continuous Forms

[Ceriumde58]

I am working on a Form that will be used by Construction personnel to enter Daily information about work done on their jobsite.

The personnel are required to report the status of various Activities that they performed that day based on a schedule.

All of the activities have been save in a "Activity Schedule" table. which includes various vitals about each schedule items such as Start Date and End Date.

In the daily Form a "Daily Activity Schedule" table is used to allow the end user to select via combo box the activities that they worked on on that given day. This Form is set up as a continuous form so that a new row opens up once a activity has been entered.

Is it possible to construct a query that will automatically fill in the Activities on the "Daily Activity Schedule" Continuous subform based on the Date entered on the Parent form? We are trying to only pull up schedule items that have a >= "Start Date" and a <= "End Date"

Any assistance is greatly appreciated.

EDIT: I've attached a Sample Database to show how the Forms are currently structured, hopefully this will make it a little more clear what I'm trying to accomplish.

 

[pbaldy]

I have what might be a similar situation, relating to drivers and different rates they can charge during their daily activity. What I do is run an append query that inserts each available rate plus the driver and date into the activity table with 0 hours. The user can then just tab down the rates and add hours for any rates that were used. When they close the form, I run a delete query to get rid of any rates with 0 hours.

 

[Ceriumde58]

I'm not sure if that solution will work for what I'm trying to do because the "Activity Schedule" and the "Daily Activity Schedule" are designed to hold different types of info. Basically the "Daily Activity Schedule" currently only uses the "Activity Number" from the "Activity Schedule" table to populate the Combo box on the subform.

 

[pbaldy]

So? Does the button do what you want?

 

[Ceriumde58]

Not quite, The button appears to add all of the items from the Activity Schedule table. I would only like to show items in the activity schedule that are scheduled on the "Daily Form Date"

So in this example, if the "daily form date" is set to 11/20/2012, only SAMP8888 and SAMP9999 should populate because they are the only schedule items that occur during the user provided date.

Generating all of the Schedule items just wouldn't work for this application because i cant expect my field employees to know the schedules for 400+ scheduled items, and sorting through would likely take more time than just entering information by hand.

Thanks for helping

 

[pbaldy]

Well, that's simply a matter of adding criteria to the query.

 

[Ceriumde58]

Then that's what I will do, thanks for all your help

 

[pbaldy]

No problem. Since I had it open to test, this should work:

INSERT INTO tblDailyActSched ( ActivityNum, DailyFormID )
SELECT tblActivitySchedule.ActivityNum, [Forms]![frmDailyFormSample]![DailyFormID] AS Expr1
FROM tblActivitySchedule
WHERE (((tblActivitySchedule.ScheduledStart)<=[Forms]![frmDailyFormSample]![DailyFormDate]) AND ((tblActivitySchedule.ScheduledCompletion)>=[Forms]![frmDailyFormSample]![DailyFormDate]));