Hello again,
In answer to your questions - basically - yes. Create a table for each
subform. Say subform1 has 5 fields, subform2 has 7 fields, subform3 has
10 fields. The main form is based on tbl1. When you cycle through the
records in the mainform you will do something like this:
Functin SynchSubformTables()
Dim intID As Integer
Dim strSql As String
intID = txtID '--this is the current ID you are on in the
'--main form
strSql = "Delete * From tblSubform1"
DoCmd.Run strSql
strSql = "Delete * From tblSubform2"
DoCmd.Run strSql
strSql = "Delete * From tblSubform3"
DoCMd.Run strSql
DoEvents
strSql = Insert Into tblSubfrom1 Select t1.fld1, t1.fld2, t2.fld3,
t2.fld4 From tbl1 t1 Inner Join tblwhatever1 t2 On t1.ID = t2.ID And
t1.Name = t2.Name and t1.Date1 = t2.Date1 Where t1.ID = " & intID
DoCmd.RunSql strSql
strSql = Insert Into tblSubfrom1 Select t1.fld1, t1.fld2, t2.fld3,
t2.fld4 From tbl1 t1 Inner Join tblwhatever2 t2 On t1.ID = t2.ID And
t1.Name = t2.Name and t1.Date1 = t2.Date1 Where t1.ID = " & intID
DoCmd.RunSql strSql
strSql = Insert Into tblSubfrom1 Select t1.fld1, t1.fld2, t2.fld3,
t2.fld4 From tbl1 t1 Inner Join tblwhatever3 t2 On t1.ID = t2.ID And
t1.Name = t2.Name and t1.Date1 = t2.Date1 Where t1.ID = " & intID
DoCmd.RunSql strSql
subform1.Requery
Subform2.Requery
subform3.Requery
Me.Requery
End Function
Remember - the subforms are displaying detail data. For each ID in the
master table, you should only have a few detail rows (well, a few as in
less than 100 rows of detail per master row). This is how it is done.
Assuming you don't have a ton of detail rows per ID, each time you move
to another record in the main form - the subforms will update very
quickly - within milliseconds.
Call the function from a button that moves to the next or previous
record.
hth
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
