If you use a subform only occasionally you can load its record source as
needed. Maybe the subform control is hidden, and there is no record souce
assigned to the subform. When you click a button to show the subform you
could load its Record Source:
Dim lngID as Long
Dim strSQL as String
lngID = Me.ID
strSQL = "SELECT * FROM tblContribution " & _
"WHERE [ID] = " & lngID & _
" ORDER BY [ContributionDate]"
Me.SubformControlName.Form.RecordSource = strSQL
You would need to reload the record source for every main form record in
which you want to view it, which you could do by setting the subform control
Visible property to False in the main form's Current event, forcing you to
click the button to show the subform, and thereby run the record source SQL
again. Or you could just use a named query as the Record Source, so that
once the form is visible the related records appear for every main form
record.
In any case, when entering related records you need a way to populate the
linking field's value. The most automatic way to do that is to use the Link
Master and Link Child properties of the subform control (the "box" on the
main form that contains the subform). You could do this with a standalone
form for related records, but it would involve some coding. For instance, in
that form's Current event:
Dim lngID as Long
lngID = Forms!MainForm!ID
Me.txtID.DefaultValue = lngID
ID is the primary key field of the main form's table and the linking field
for the related table.
However, if you want to enter contributions from a second client I expect you
would need to run the code again to establish the default value, but I am not
exactly sure how this would work. I would use form/subform with linking
fields, as it is a convenient built-in way of accomplishing this. No need to
reinvent the process.
If you need to open a form to show related records you could use the Where
condition of OpenForm, something like:
DoCmd.OpenForm "frmContributions", , , "ID = " & Forms!frmMain!ID
However, I will just say again that using Form/Subform probably gives you
what you need. If things seem to be slowing down you can rethink things then.
In my experience there will not be much of a performance hit unless the
subform query has a number of calculations.
If there are performance issues the first thing is to be sure you have
applied indexes to all fields used for sorting, filtering, and linking.
Placing subforms on a tab control may give you a neater form.
I put a 2nd subform for the contributions, and it is what I wanted. It
automatically fills in the ClientID number and I can add new entries right
there. Is this the best way to do it? I still wonder about slowing down
performance with too many subforms.
I guess I used Access so long ago that I keep thinking I have to open a
separate form to add contributions, or view a list of contributions. It's
just that when you do use separate forms for entering data, it does not
automatically fill in the Client ID #, even though the tables are related.
Please let me know if it's better just to use the subform. My other 2
subforms will be a list of each family member, and a list of events the
family participates in.
I do have 2 tables. One is all the client info with the clientID as the
primary key. A related table is the contributions table, where ClientID is
[quoted text clipped - 46 lines]
