By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

How to reuse a subform?

P: n/a
Hi all,

I'm trying to reduce the number of subforms used to display seom data
on a form. At the moment there is 36 subforms on the main form, all
with the same basic format. The only change between each of them is
the recordsource. I wish to significantly reduce the numbers of
subforms in the database (there are two main forms with 36 subforms
each).

The plan is to have one subform and instance this form 36 times on the
main form with a different recordset for each instance.

I have set up a new subform [sub_form] within the [main_form] and am
trying to dynamically update the code for the first two instances of
the subform as a test. The problem I run into is, the data displayed
in the two instances ends up being from the same recordsource. I have
attached the main form's OnCurrent code for reference. I'm assuming
that I need to use the NewForm method or similar to get this to work.

Is it possible to use the same form as a subform multiple times? If
so, how can I change the recordsource and not have it alter all
instances of the subform?

This is being attempted in Access 97 SR2.

Regards
Steve


Private Sub Form_Current()
Dim strSQL As String
Dim strSQL2 As String
strSQL = "SELECT qry.[ID1], qry.[ID2], qry2.[Field1], qry2.[Field2] "

strSQL = strSQL & "FROM qry LEFT JOIN qry2 ON
(qry.[Field3] = qry2.Field1) AND
(qry.[Field4] = qry2.Field2)"

strSQL = strSQL & " WHERE (qry.[ID1]= " & Me![ID1] & ");"

Me![Subform_1st_Instance].Form.RecordSource = strSQL
Me![Subform_1st_Instance].Requery

strSQL2 = "SELECT qry.[ID1], qry.[ID2], qry2.[Field1], qry2.[Field2] "

strSQL2 = strSQL2 & "FROM qry_Risk LEFT JOIN qry2 ON
(qry.[Field5] = qry2.Field1) AND
(qry.[Field6] = qry2.Field2)"

strSQL2 = strSQL2 & " WHERE (qry.[ID1]= " & Me![ID1] & ");"

Me![Subform_2nd_Instance].Form.RecordSource = strSQL
Me![Subform_2nd_Instance].Requery

End Sub
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
st********@hotmail.com (Steve Strik) wrote in message news:<2e**************************@posting.google. com>...
Hi all,

I'm trying to reduce the number of subforms used to display seom data
on a form. At the moment there is 36 subforms on the main form, all
with the same basic format. The only change between each of them is
the recordsource. I wish to significantly reduce the numbers of
subforms in the database (there are two main forms with 36 subforms
each).

The plan is to have one subform and instance this form 36 times on the
main form with a different recordset for each instance.

I have set up a new subform [sub_form] within the [main_form] and am
trying to dynamically update the code for the first two instances of
the subform as a test. The problem I run into is, the data displayed
in the two instances ends up being from the same recordsource. I have
attached the main form's OnCurrent code for reference. I'm assuming
that I need to use the NewForm method or similar to get this to work.

Is it possible to use the same form as a subform multiple times? If
so, how can I change the recordsource and not have it alter all
instances of the subform?

This is being attempted in Access 97 SR2.

Regards
Steve


Private Sub Form_Current()
Dim strSQL As String
Dim strSQL2 As String
strSQL = "SELECT qry.[ID1], qry.[ID2], qry2.[Field1], qry2.[Field2] "

strSQL = strSQL & "FROM qry LEFT JOIN qry2 ON
(qry.[Field3] = qry2.Field1) AND
(qry.[Field4] = qry2.Field2)"

strSQL = strSQL & " WHERE (qry.[ID1]= " & Me![ID1] & ");"

Me![Subform_1st_Instance].Form.RecordSource = strSQL Me![Subform_1st_Instance]![Control1].ControlSource="Field1"
Me![Subform_1st_Instance]![Control2].ControlSource="Field2" Me![Subform_1st_Instance].Requery

strSQL2 = "SELECT qry.[ID1], qry.[ID2], qry2.[Field1], qry2.[Field2] "

strSQL2 = strSQL2 & "FROM qry_Risk LEFT JOIN qry2 ON
(qry.[Field5] = qry2.Field1) AND
(qry.[Field6] = qry2.Field2)"

strSQL2 = strSQL2 & " WHERE (qry.[ID1]= " & Me![ID1] & ");"

Me![Subform_2nd_Instance].Form.RecordSource = strSQL\ Me![Subform_2nd_Instance]![Control1].ControlSource="Field1"
Me![Subform_2nd_Instance]![Control2].ControlSource="Field2" Me![Subform_2nd_Instance].Requery

End Sub


I got it to work. the subforms needed to have unbound controls on
them and I added a little bit of code (Inserted above) to set the
controlsource for each control.
Regards
Steve
Nov 13 '05 #2

P: n/a
st********@hotmail.com (Steve Strik) wrote in message news:<2e**************************@posting.google. com>...
st********@hotmail.com (Steve Strik) wrote in message news:<2e**************************@posting.google. com>...
Hi all,

I'm trying to reduce the number of subforms used to display seom data
on a form. At the moment there is 36 subforms on the main form, all
with the same basic format. The only change between each of them is
the recordsource. I wish to significantly reduce the numbers of
subforms in the database (there are two main forms with 36 subforms
each).

The plan is to have one subform and instance this form 36 times on the
main form with a different recordset for each instance.

I have set up a new subform [sub_form] within the [main_form] and am
trying to dynamically update the code for the first two instances of
the subform as a test. The problem I run into is, the data displayed
in the two instances ends up being from the same recordsource. I have
attached the main form's OnCurrent code for reference. I'm assuming
that I need to use the NewForm method or similar to get this to work.

Is it possible to use the same form as a subform multiple times? If
so, how can I change the recordsource and not have it alter all
instances of the subform?

This is being attempted in Access 97 SR2.

Regards
Steve


Private Sub Form_Current()
Dim strSQL As String
Dim strSQL2 As String
strSQL = "SELECT qry.[ID1], qry.[ID2], qry2.[Field1], qry2.[Field2] "

strSQL = strSQL & "FROM qry LEFT JOIN qry2 ON
(qry.[Field3] = qry2.Field1) AND
(qry.[Field4] = qry2.Field2)"

strSQL = strSQL & " WHERE (qry.[ID1]= " & Me![ID1] & ");"

Me![Subform_1st_Instance].Form.RecordSource = strSQL

Me![Subform_1st_Instance]![Control1].ControlSource="Field1"
Me![Subform_1st_Instance]![Control2].ControlSource="Field2"
Me![Subform_1st_Instance].Requery

strSQL2 = "SELECT qry.[ID1], qry.[ID2], qry2.[Field1], qry2.[Field2] "

strSQL2 = strSQL2 & "FROM qry_Risk LEFT JOIN qry2 ON
(qry.[Field5] = qry2.Field1) AND
(qry.[Field6] = qry2.Field2)"

strSQL2 = strSQL2 & " WHERE (qry.[ID1]= " & Me![ID1] & ");"

Me![Subform_2nd_Instance].Form.RecordSource = strSQL\

Me![Subform_2nd_Instance]![Control1].ControlSource="Field1"
Me![Subform_2nd_Instance]![Control2].ControlSource="Field2"
Me![Subform_2nd_Instance].Requery

End Sub


I got it to work. the subforms needed to have unbound controls on
them and I added a little bit of code (Inserted above) to set the
controlsource for each control.
Regards
Steve

:(

For some reason it works for the first 24 instances of the code, but
Access crashes when it hits the recordsource line of the 25th
instance. Any ideas?

Regards

Steve
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.