Locked main form - validate 1 subform with another?

[misscrf]

I have a main form that has 3 fields. They are all locked fields, only to show the user the "person" record that they are looking at. They update to subforms on a tab. The first subform is activities (like from a mailing - received return no forwarding address, received updated address, called us, etc)

The other subform is for addresses, original and updated.

There is a date field for the date of any updated addresses.

What I need to do is allow users to enter activities and move around from main record to main record. The important thing is that if they enter an activity on the subform that is for receiving an address change, I need to make sure that they do not leave the main record, without entering in an updated address for that same date, on the address subform.

The activity subform has a date field too, so the validation part seems like it wouldnt be too hard...

IIF ?subform-activities-field-activitytype = "updated address" (3/code to lookup table) AND ?subform-address-field-date = grrr this is where I get confused.

Then msg "Stop, you must add the updated address for the activity of receiving an address change"
cancel = true

end if

make sense?

Any help would be gratefully appreciated!

 

[Pat Hartman]

There really isn't any way to force a user to do something of this type since they have the ultimate say - power off. A better solution is to have the Address sub create an activity record. That way you don't have to depend on the user doing the right thing.

 

[misscrf]

Thanks for the reply. The form works by the activities, simply because the users will look up a person (from a lookup form) enter in that day's activities, and only if the activity is one of the 5 possible, then the user has to enter in an address (because that activity is updated address).

I will say, that I agree with what you are saying in terms of db development. However, for this db, I am willing to throw aside the possibility of them trying to close the db. I want to cover if the user tries to go to the next record (I have a command button), the previous record, look up a record, or close the form, without satisfying this validation. IE if they are on a record where there is an updated address activity, and no updated address for the same date, then a message would come up saying that they have to enter in an address for that date, and the focus would move to the address1 field...

Sound good?

 

[Pat Hartman]

The problem is that I think that Access is going to save the activity record when focus moves somewhere else on the main form, including a different subform. If you are not allowed to prevent saving the activity record before the address change has been made, you can't prevent "bad" data from being added. The only way to get around all this would be to use unbound forms and I wouldn't recommend that at all.

I don't have an forms handy with two subforms. Try modifying something in sfA. When you click into sfB, does the pencil in the recordselector of sfA go away? If it does, the record in sfA has been saved.

 

[misscrf]

I have discussed this with a few people on different forums and I think it may work best that if someone chooses the activity for received updated address, the focus will move them to the address subform on an new record for today's date. I talked with the main user and she is ok with this idea.

Thanks for the adivce!