Updating Multiple records

[NoodNutt]

Hi all

Looking to update multiple records based on what the user selects from a cbo
on a form that will influence the subset of records in the subform.

If user selects a condition from cboPupStatus on the main form in which all
the subset records are tied by the same record number which include
PupStatus, I need for the matching records in that subset to update to what
the user has selected in the main form cboStatus.

Mainfrom = tblPickUpPoint| PupStatus
Subform = tblPupDetails| PupStatus

Updating a single record is one thing, looping through multiples i'm not
familiar with.



TIA
Mark.

 

[Graham Mandeno]

Hi Mark

First of all, think carefully about whether you are doing the right thing.

Usually a subform contains records which are uniquely related to the current
record in the main form. If you want to update ALL the records in the
subform to reflect a change in the main form, shouldn't that field be stored
in the main form?

As an example, given the NorthWind sample database model of Customers and
Orders, if I change the customer's phone number I would not want to update a
field in every order record for that customer. Data pertaining to the
customer needs to be stored in only one place - the customer record.

However, sometimes it's necessary to update a whole bunch of records in one
go, because they are only loosely or temporarily related (such as by a
filter or something similar).

In this case, you can create an on-the-fly UPDATE query in code. For
example:

Private Sub PupStatus_AfterUpdate()
Dim sSQL as string, db as Database
sSQL = "Update tblPupDetails set PupStatus=" & Me.PupStatus _
& " where <insert your filter criteria here>"
Set db = CurrentDb
db.Execute sSQL, dbFailOnError
MsgBox db.RecordsAffected & " pup records updated"
End Sub

 

[NoodNutt]

Thx heaps Graham

Working well.

Regards
Mark.