Different Subform Data

[Samantha]

To imitate the appearance of Excel in displaying data, I added a subform
within the main form. The selection criteria is based on comboboxes and
textboxes on the main form. My problem is that the uers want various types of
data, therefore, the queries are different. What I have done is added 3
different subforms into the main form, and depending on the type of data
requested, only one subform is visible and the others invisible.

Now, the users are asking for more different types of data, and they like
the fact that the data is contained on one screen. That means, I would have
to place more different subforms on the main form (and making all invisible
except one at any given time). I'm afraid that placing many subforms will
make the whole form load slower, and cause other possible problems. So, is
there any other method that can dymanically change the record source with
only one subform on the main form?

 

[NevilleT]

Hi Samantha
One solution may be to have a number of queries and depending what the users
want, swap the controlsource on the form using a refresh button to trigger
the action.

btnRefresh_click:
if not isnull(cmbInput1) then
me![subFormName].ControlSource = qryFirstQuery
etc.

Another way to do it is to have a number of buttons on the main form which
each loads a new controlsource for the subform.

 

[NevilleT]

Hi again Samantha

Just found the following code that I used to determine which query to use if
a combination of from and to dates were entered.

Sub subFilter()
' Decide which query to use as the recordsource
Dim dteFrom As Date ' Date
From. Zero if blank
Dim dteTo As Date ' Date
To. Zero if blank

' Has a "From Date" been entered
If IsDate(Forms!frmHistoryReports.txtDateFrom) Then
dteFrom = Forms!frmHistoryReports.txtDateFrom
End If

' Has a "To Date" been entered
If IsDate(Forms!frmHistoryReports.txtDateTo) Then
dteTo = Forms!frmHistoryReports.txtDateTo
End If

If dteFrom > 0 And Not dteTo > 0 Then '"From
Date" - Yes, "To Date - No"
Me.[frmHistorySub].Form.RecordSource = "qryHistoryReportsFrom"

ElseIf Not dteFrom > 0 And dteTo > 0 Then '"From
Date" - No, "To Date" - Yes
Me.[frmHistorySub].Form.RecordSource = "qryHistoryReportsTo"

ElseIf dteFrom > 0 And dteTo > 0 Then ' Both
Dates Yes
Me.[frmHistorySub].Form.RecordSource = "qryHistoryReportsFromTo"

ElseIf dteFrom = 0 And dteTo = 0 Then ' Both
Dates No
Me.[frmHistorySub].Form.RecordSource = "qryHistoryReportsAll"
End If

End Sub

Hi Samantha
One solution may be to have a number of queries and depending what the users
want, swap the controlsource on the form using a refresh button to trigger
the action.

btnRefresh_click:
if not isnull(cmbInput1) then
me![subFormName].ControlSource = qryFirstQuery
etc.

Another way to do it is to have a number of buttons on the main form which
each loads a new controlsource for the subform.

To imitate the appearance of Excel in displaying data, I added a subform
within the main form. The selection criteria is based on comboboxes and
textboxes on the main form. My problem is that the uers want various types of
data, therefore, the queries are different. What I have done is added 3
different subforms into the main form, and depending on the type of data
requested, only one subform is visible and the others invisible.

Now, the users are asking for more different types of data, and they like
the fact that the data is contained on one screen. That means, I would have
to place more different subforms on the main form (and making all invisible
except one at any given time). I'm afraid that placing many subforms will
make the whole form load slower, and cause other possible problems. So, is
there any other method that can dymanically change the record source with
only one subform on the main form?