Updated Multiple Tables with Data entered on a Form

[Ceriumde58]

I have a Form which my end users fill out on a daily basis, we use it to track the progress of various activities on their given project. As part of the daily form, end users must put a daily completion percentage for the given activity. I would like update an additional table with some of that daily information.

Below is an example of what currently happens, and what i would like to accomplish:

End user opens Daily form,
End user presses a button to load all currently scheduled activities(populated from a "Master Activity Schedule" table based on scheduled start date and completion percentage),
End user enters comments, and completion percentages for daily activities,
Data is saved to a Daily Activity table

I would like to save the completion percentage value to the "Master Activity Schedule" table as well, so long as the daily percentage is higher than the value stored in the "Master Activity Schedule" table. This would prevent activities that have already been completed from reappearing on subsequent daily forms


What would be the best way of accomplishing this

 

[Isskint]

When you do this, will you be adding a new record to this Daily Activity table or updating an existing record?

You could do this via an UPDATE or APPEND query OR you could do this via the RunSQL method in VBA. The same applies to the Master Activity Schedule (I presume that would be an UPDATE to the current value?)

Which method would you prefer to use?

 

[Ceriumde58]

The Daily Activity Schedule is a continuous subform, and when it is filled in on the form it will actually add a new row to the "Daily Activity" table. In addition to that, I would like to update the Completion% field on the "Master Activity Schedule" table.

Initially I had tried to use an Update query, but it was not actually changing the value in the "Master Activity Schedule" table. I most likely did not have it configured correctly. I need it to update the Master table only for the Activity with the same ActivityNumber as the item on the Daily form.

I can include an example access file if my description is unclear

 

[Isskint]

An update query is the easiest method for this. In basic terms the update query needs to know what field(s) to update, the value to update to and the criteria to identify which record(s) need updating, so;

UPDATE [TableToUpdate] SET [FieldToUpdate] = ValueToUpdateTo WHERE PrimaryField = ValueOfPrimaryFieldOnForm

[TableToUpdate] = Master Activity Schedule?
[FieldToUpdate] = Completion%? (btw, try not to use symbols in field names, it WILL cause you all sorts of headaches)
ValueToUpdateTo = Value entered to new record on form?
PrimaryField = ??
ValueOfPrimaryFieldOnForm = Completion%Textbox?

Could you post the SQL of the update query you have tried?

 

[Ceriumde58]

Ok so its getting close to working except for two things, using the example statement you pasted before, I built the query below:

UPDATE tblActivitySchedule SET [%Complete] = ![Forms]![sbfrmDailyActivitySchedule]![ActivityDailyCompletion%]
WHERE [ActivityNum]=![Forms]![sbfrmDailyActivitySchedule]![ActivityNum];

When this query runs from the subform though, access prompts me asking for a value for ![Forms]![sbfrmDailyActivitySchedule]![ActivityDailyCompletion%] and !Forms!sbfrmDailyActivitySchedule!ActivityNum

 

[Isskint]

You want to remove the leading !
UPDATE tblActivitySchedule SET [%Complete] = ![Forms]![sbfrmDailyActivitySchedule]![ActivityDailyCompletion%]
WHERE [ActivityNum]=![Forms]![sbfrmDailyActivitySchedule]![ActivityNum];

Also I Presume [sbfrmDailyActivitySchedule] is the SubForm. If so you want to reference it differently. SubForms have to be referenced as an object of the main form. So
Forms!MainFormName!sbfrmDailyActivitySchedule.Form![ActivityNum]