Create multiple records in subform and pre-populate each record

[fire2ice]

I have a subform that tracks multiple activities for each objective. However, one of my objectives always has the same three activities. What I want to do is, from the click of a checkbox (with the value = True), create the record for activity 1 and put specific text in the activity field, create a record for activity 2 and put specific text in the activity field, and the same for activity 3.

Of course, I need to make sure that no activities already exist for this objective before it performs this action. If activities exist in the subform, then the code would eliminate (or append) the current records and replace with the three specific activities.

I'd also like to be able to delete the three records if the checkbox = False.

Main form: frmObjectives
Subform: sfrmActivities
Linked Child field: ObjectiveID
Field to populate on subform: Activities
Checkbox to trigger code (via On Click event): chkMethodsUsed

Thank you.

 

[TylerTand]

Start out with an if statement to check if the objective has any activities. if the result is true then do updates to the fields with the intended message. (you didn't say what that was) otherwise it means that there are no activities for that objective and you need to create three new records. you can do this part a number of ways. I am assuming that the activities field is a combo box with your list of possible activities? Really all you need to do is create three records and then update the combo boxes to the proper values either in the code or manually after they are created. Since you didn't tell us the name of the table I will use fake names.

Set Varset=currentdb.openrecordset("Table1")
varset.addnew
Varset!Activities="Activity 1 Name Here"
Varset.update
varset.addnew
Varset!Activities="Activity 2 Name Here"
Varset.update
varset.addnew
Varset!Activities="Activity 3 Name Here"
Varset.update

If all three activities are the same thing than you could create a variable named the constant action and say:
VarActivity= "Paint the Fence!"
Varset!Activities= VarActivity
or what ever.

In the future if you give a little more detail about what you are trying to do and be sure to include table Names and Fields it is a great help. Good luck let us know
Tyler

 

[fire2ice]

Start out with an if statement to check if the objective has any activities. if the result is true then do updates to the fields with the intended message. (you didn't say what that was) otherwise it means that there are no activities for that objective and you need to create three new records. you can do this part a number of ways. I am assuming that the activities field is a combo box with your list of possible activities? Really all you need to do is create three records and then update the combo boxes to the proper values either in the code or manually after they are created. Since you didn't tell us the name of the table I will use fake names.

Set Varset=currentdb.openrecordset("Table1")
varset.addnew
Varset!Activities="Activity 1 Name Here"
Varset.update
varset.addnew
Varset!Activities="Activity 2 Name Here"
Varset.update
varset.addnew
Varset!Activities="Activity 3 Name Here"
Varset.update

If all three activities are the same thing than you could create a variable named the constant action and say:
VarActivity= "Paint the Fence!"
Varset!Activities= VarActivity
or what ever.

In the future if you give a little more detail about what you are trying to do and be sure to include table Names and Fields it is a great help. Good luck let us know
Tyler

First, how would I write the If statement to check whether there are records on the subform? Second, the three activities are all different.

A little more info about the database follows:

Table for main form: tblObjectives
Table for subform: tblActivities
PK on main form: ID (autonumber)
FK on subform: ObjectiveID
​

Text strings for the three activity records:

** Each staff person must have the following as an objective and the weight may be 10-40%.

Successfully complete performance objectives in a manner consistent with the behaviors demonstrated in the "Methods Used to Achieve Results" section of this appraisal form.

See Section III
​

There is another control on the subform named ActivityNumber which numbers the activities. It would basically have a value of 1 for the first record, 2 for the second, and 3 for the third.

Thank you.