Full Version: After Update Event To Multiple Records
UtterAccess Forums > Microsoft® Access > Access Forms
vdurst71
Hello UA,
On my form, I have a field called Training Scheduled. On a subform, I have a field called Lab Follow Up. I'm using the After Update Event on the Training Scheduled field which populates a date in the Lab Follow Up field. The records are linked by ORG ID number. The way it works now is that it will only update one record (which I'm guessing is the first record it finds) Problem is - one group can have many labs. Need help with code to have it find each record where ORG ID matches and updates this field.
Thanks in advance for all help!
dmhzx
Are you using an Update SQL statement, or a me.field = something?
The first approach should work just fine, the other will only update one record.
vdurst71
Using second method, code below:
ollowUpDate = DateAdd("ww", -1, [TrainScheduled])
Would appreciate help on writing Update SQL statement.
Thanks again!
dmhzx
Sorry for delay, been away mentally for a while.
personally wouldn't do this as an SQL, simply because it's not something I do very often.
I would set up a loop based on the recordset of the sub form, and update one record at a time. - Unless there were more than about 20 records.
It's not as fast and an update SQL, but you probably wouldn't notice.
vdurst71
DM -
thanks for your reply and hope all is well. Yes, there would be less than 20 records, could you assist me with the loop?
dmhzx
Ok. This is what I think is called aircode, so use it as a guide rather than instructions.
The loop is something like this:
NewVal = me.mainformValToUse
with me.subform.recordset
.movefirst
do untill .eof
.edit
![Field] = newval
.update
.movenext
loop
It may work with a me.something instead of the ![field] bit. - see what intellisense offers as you type
Best of luck
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.