Auto-populating fields using different table

[Ceriumde58]

Hello Everyone,

I have been working on a form that field employees have been completing on a daily basis. As part of this form, employees must record their progress in different activities in the activity schedule for their Project. The vital information for the Activity Schedule is stored on a one table, while the info entered on the Daily Form is stored in a daily table (which allows us to track the progress of various activities over time).

What I would like the form to do is autoupdate the "completion%" and the "comments"(which the most recent entries are saved to in addition to the daily table) once an activity is selected in the activity combobox.

I don't know how to accomplish this with fields that are bound to a different table than the one i'd like to pull information from.

Thanks in advance for any assistance with this.

 

[Pat Hartman]

When you want to show information from multiple tables on the same form, create a query that joins the tables/queries and use that as the RecordSource for the form.

 

[Ceriumde58]

I really only need to save the information into one table, the daily. I'm looking for an auto-populate option to save time and reduce human error (i.e. The person filling out the report wont put in a value lower than the overall progress on a given activity)

Will creating a query that joins the activity schedule and the daily report tables allow me to autopopulate information from the activity schedule table, and save it to the corresponding column in the daily report table?

As it stands now I have an append query that runs(when a button is clicked) and just adds all of the activity numbers and completion% based on the daily report date. I would like something that works similarly but only for one row at a time when a user selects an activity manually(from a combobox)

 

[Pat Hartman]

It is poor practice to actually store the same data in multiple places. That's why we use queries to bring data from multple tables together to make information. You don't store the customer information in each order. You store it in the customer table yet you need to see it when viewing an order so you base the order form on a query that joins the order table to the customer table. Is that what we are talking about or are you actually trying to copy fields from one table to another?

 

[Ceriumde58]

The database is designed so that there is very little redundant data, but I realize it sounds like I am storing the same info twice (which I may be). I will try to explain the differences between the two tables so that their purpose is more clear.

The database is being used by construction management personnel in the field. Its main purpose is to provide project management with thorough information about A: The current status of the Job. B: The projects progress over time.

The Activity_Schedule table holds the information for the current status of the job. It includes information for each activity scheduled for completion (based on a schedule from the General Contractor) Here we track data such as The ActivityID, ActivityDescription, Scheduled_Start, Scheduled_Finish, and Completion%.

The Daily_Entry table holds information about the progress over time. Users fill out a form that saves all of the information about activities performed on a given day. Users are asked to fill out a log of daily info for each activity they've worked on. These fields include: ActivityID(The related field for the 1 to many relationship), Status (Dropdown list with Start, Continue, Disruption, Finish), Completion% (This field shows up again to allow us to track the completion rate over time, if I use the other table for this field I will lose data every time a user changes the value), and DailyComments.

We want to implement an autopopulate to speed up the data entry process and reduce the chance for error. If we have 2 users filling out reports on different dates User A may estimate the activity completion% for a given day to be 75%, and on the following day User B may estimate the completion% to be 70%.

Currently I have implemented a After Update procedure on the daily completion field to save the input value into the Activity_Schedule table, just for the purpose of saving the most recent amount as the "overall" value. What I would like to do is have that "overall" value automatically populate the field on the daily form so that users know the current reported progress on the activity which they have selected.

I can provide a sample of my database if my description is not sufficient

 

[Ceriumde58]

I have been able to get the desired functionality using an Update Query in the afterupdate event. Thank you for the assistance.

 

[Pat Hartman]

I see now that you are keeping history and that is fine. The update query will work as long as there is no possibility of changing the data from any other form or application. Storing calculated values, which is what it now sounds like you are doing, leads to anomolies when data is changed but the calculation is not done. It is better to calculate the value as you need it. For example by using a DSum() or DLookup() from the form where you show the calculated value. In a report, you would not use a domain function you would join to a totals query which is more efficient.