How to automatically populate another record when data is entered into a form

[quinnsfpa]

My form contains subforms for referrals and credits. When an acc no is entered into the referrals form I want to automatically update a field on the credits form for that record, eg Acc No 1 has been referred by Acc No 2 so I enter acc no 2 into the referrals subform for Acc No 1. I then want £5.00 to appear on the credits subform for the record belonging to Acc 2. I hope that makes sense!



Can anyone tell me how to do this please?

Thanks.

[June7]

Could maybe use VBA code to run an SQL UPDATE action. Something like:

CurrentDb.Execute "UPDATE tablename SET Amount = 5 WHERE AcctNo=" & Me.AcctNo

Or if you actually need to create a new record, run an INSERT action.

CurrentDb.Execute "INSERT into tablename(AcctNo, Amount) VALUES(" & Me.AcctNo & ", 5)"

Without knowing more about table structure and form design, hard to offer specific advice.

[NTC]

The implementation depends on the structure of your data, as well as the structure of your objects. J7's advice should be noted. It is the implementation via the record set level, and definitely will work.

If all the fields & records are displaying together via forms/subforms - then one may be able implement with VBA that references the controls; such as Me.SubformName.Form!SubformControlName = 5