Access 2003 Forms and Subforms with Parameter Query

[JCJ]

Hello,
I am using a form with combo boxes in Access 2003 which runs a query with 4
to 6 parameters, and I need to display the results of the query in a subform.
When I add a subform in Design View and run the form, I am immediately
prompted for the parameters before the form is displayed.
Can someone offer advice on how to link the form and subform together, so
that the subform is only displayed after the user clicks "Run Query" on the
form?

The data displayed on the subform should be dependant on the parameters the
user selects on the main form.

Any suggestions?

 

[Ken Snell \(MVP\)]

Post the SQL statement of the subform's RecordSource query so that we can
better assist you.

 

[JCJ]

Here is the subform Record Source statement:

SELECT [qtotStateAssessmentResults-grpPerformanceLevel-InCohort].*,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject FROM
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort] WHERE
((([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear)=Forms![frmRunQueryParam-TEST].cboVictorySchoolYear)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Grade)=Forms![frmRunQueryParam-TEST].cboVictoryGrade)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject)=Forms![frmRunQueryParam-TEST].cboSubject));

 

[Ken Snell \(MVP\)]

The problem you're having is that a subform is actually loaded before the
main form is loaded, thus the subform's query cannot "see" the main form's
controls when the subform is loaded and its query is run. There are
different ways you can handle this situation.

My preferred way is to save your subform's RecordSource query as a query in
your database (name it qryNameOfYourSubform). Then, delete the RecordSource
from the subform (design view). Then, click a button on the main form to
"open" the subform. The code for this button would be this:

Private Sub NameOfButton_Click()
With Me.NameOfSubformControlOnMainForm.Form.
If RecordSource = "" Then
' first time the subform is being "opened", so set the
' RecordSource property for the subform
.RecordSource = "qryNameOfYourSubform"
Else
' subform has already been "opened", so just
' requery the subform's RecordSource
.Requery
End If
End With
End Sub


Please note that I've used generic names in my example
(qryNameOfYourSubform, NameOfButton, NameOfSubformControlOnMainForm), so
replace these names with the actual names that you use when you implement
this suggestion.
--

Ken Snell
<MS ACCESS MVP>

Here is the subform Record Source statement:

SELECT [qtotStateAssessmentResults-grpPerformanceLevel-InCohort].*,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject FROM
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort] WHERE
((([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear)=Forms![frmRunQueryParam-TEST].cboVictorySchoolYear)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Grade)=Forms![frmRunQueryParam-TEST].cboVictoryGrade)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject)=Forms![frmRunQueryParam-TEST].cboSubject));

Post the SQL statement of the subform's RecordSource query so that we can
better assist you.

 

[JCJ]

Ken,
Thanks for the suggestion. However, since the subform opens a parameter
query, how would I pass those values along using your method?
Example of parameters needed:
qryQueryToRun.Parameters("[CohortStartDate]") = Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate

The problem you're having is that a subform is actually loaded before the
main form is loaded, thus the subform's query cannot "see" the main form's
controls when the subform is loaded and its query is run. There are
different ways you can handle this situation.

My preferred way is to save your subform's RecordSource query as a query in
your database (name it qryNameOfYourSubform). Then, delete the RecordSource
from the subform (design view). Then, click a button on the main form to
"open" the subform. The code for this button would be this:

Private Sub NameOfButton_Click()
With Me.NameOfSubformControlOnMainForm.Form.
If RecordSource = "" Then
' first time the subform is being "opened", so set the
' RecordSource property for the subform
.RecordSource = "qryNameOfYourSubform"
Else
' subform has already been "opened", so just
' requery the subform's RecordSource
.Requery
End If
End With
End Sub


Please note that I've used generic names in my example
(qryNameOfYourSubform, NameOfButton, NameOfSubformControlOnMainForm), so
replace these names with the actual names that you use when you implement
this suggestion.
--

Ken Snell
<MS ACCESS MVP>

Here is the subform Record Source statement:

SELECT [qtotStateAssessmentResults-grpPerformanceLevel-InCohort].*,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject FROM
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort] WHERE
((([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear)=Forms![frmRunQueryParam-TEST].cboVictorySchoolYear)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Grade)=Forms![frmRunQueryParam-TEST].cboVictoryGrade)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject)=Forms![frmRunQueryParam-TEST].cboSubject));

Post the SQL statement of the subform's RecordSource query so that we can
better assist you.
--

Ken Snell
<MS ACCESS MVP>


Hello,
I am using a form with combo boxes in Access 2003 which runs a query
with
4
to 6 parameters, and I need to display the results of the query in a
subform.
When I add a subform in Design View and run the form, I am immediately
prompted for the parameters before the form is displayed.
Can someone offer advice on how to link the form and subform together,
so
that the subform is only displayed after the user clicks "Run Query" on
the
form?

The data displayed on the subform should be dependant on the parameters
the
user selects on the main form.

Any suggestions?

 

[Ken Snell \(MVP\)]

Post the code that the subform is using to "open" the parameter query and
then assign that query to the subform's RecordSource.

What you'll need to do is to move that code to your main form and run it
from there for the "first" assignment.
--

Ken Snell
<MS ACCESS MVP>

Ken,
Thanks for the suggestion. However, since the subform opens a parameter
query, how would I pass those values along using your method?
Example of parameters needed:
qryQueryToRun.Parameters("[CohortStartDate]") = Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate

The problem you're having is that a subform is actually loaded before the
main form is loaded, thus the subform's query cannot "see" the main
form's
controls when the subform is loaded and its query is run. There are
different ways you can handle this situation.

My preferred way is to save your subform's RecordSource query as a query
in
your database (name it qryNameOfYourSubform). Then, delete the
RecordSource
from the subform (design view). Then, click a button on the main form to
"open" the subform. The code for this button would be this:

Private Sub NameOfButton_Click()
With Me.NameOfSubformControlOnMainForm.Form.
If RecordSource = "" Then
' first time the subform is being "opened", so set the
' RecordSource property for the subform
.RecordSource = "qryNameOfYourSubform"
Else
' subform has already been "opened", so just
' requery the subform's RecordSource
.Requery
End If
End With
End Sub


Please note that I've used generic names in my example
(qryNameOfYourSubform, NameOfButton, NameOfSubformControlOnMainForm), so
replace these names with the actual names that you use when you implement
this suggestion.
--

Ken Snell
<MS ACCESS MVP>

Here is the subform Record Source statement:

SELECT [qtotStateAssessmentResults-grpPerformanceLevel-InCohort].*,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject FROM
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort] WHERE
((([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear)=Forms![frmRunQueryParam-TEST].cboVictorySchoolYear)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Grade)=Forms![frmRunQueryParam-TEST].cboVictoryGrade)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject)=Forms![frmRunQueryParam-TEST].cboSubject));

:

Post the SQL statement of the subform's RecordSource query so that we
can
better assist you.
--

Ken Snell
<MS ACCESS MVP>


Hello,
I am using a form with combo boxes in Access 2003 which runs a query
with
4
to 6 parameters, and I need to display the results of the query in a
subform.
When I add a subform in Design View and run the form, I am
immediately
prompted for the parameters before the form is displayed.
Can someone offer advice on how to link the form and subform
together,
so
that the subform is only displayed after the user clicks "Run Query"
on
the
form?

The data displayed on the subform should be dependant on the
parameters
the
user selects on the main form.

Any suggestions?

 

[JCJ]

The nature of my post is to get suggestions on how to write the code for the
subform to open the parameter query (or to pass along the recordset from the
main form). I don't have code for the subform now.

On my main form, the "onclick" event for the button to run the query has
this code:

Dim strQueryName As String
strQueryName = "qtotStateAssessmentResults-grpPerformanceLevel-InCohort"

Dim dbThisDatabase As DAO.Database
Dim qryQueryToRun As QueryDef
Dim rsQuery As DAO.Recordset
Set dbThisDatabase = CurrentDb
Set qryQueryToRun = dbThisDatabase.QueryDefs(strQueryName)

qryQueryToRun.Parameters("[CohortStartDate]") = Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate

Set rsQuery = qryQueryToRun.OpenRecordset()

' This code below is based on your suggestion
With Me.NysQueryData.Form
If RecordSource = "" Then
.Recordset = rsQuery
Else
.Requery
End If
End With

rsQuery.Close

However, this doesn't actually open the subform yet.

Post the code that the subform is using to "open" the parameter query and
then assign that query to the subform's RecordSource.

What you'll need to do is to move that code to your main form and run it
from there for the "first" assignment.
--

Ken Snell
<MS ACCESS MVP>

Ken,
Thanks for the suggestion. However, since the subform opens a parameter
query, how would I pass those values along using your method?
Example of parameters needed:
qryQueryToRun.Parameters("[CohortStartDate]") = Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate

The problem you're having is that a subform is actually loaded before the
main form is loaded, thus the subform's query cannot "see" the main
form's
controls when the subform is loaded and its query is run. There are
different ways you can handle this situation.

My preferred way is to save your subform's RecordSource query as a query
in
your database (name it qryNameOfYourSubform). Then, delete the
RecordSource
from the subform (design view). Then, click a button on the main form to
"open" the subform. The code for this button would be this:

Private Sub NameOfButton_Click()
With Me.NameOfSubformControlOnMainForm.Form.
If RecordSource = "" Then
' first time the subform is being "opened", so set the
' RecordSource property for the subform
.RecordSource = "qryNameOfYourSubform"
Else
' subform has already been "opened", so just
' requery the subform's RecordSource
.Requery
End If
End With
End Sub


Please note that I've used generic names in my example
(qryNameOfYourSubform, NameOfButton, NameOfSubformControlOnMainForm), so
replace these names with the actual names that you use when you implement
this suggestion.
--

Ken Snell
<MS ACCESS MVP>




Here is the subform Record Source statement:

SELECT [qtotStateAssessmentResults-grpPerformanceLevel-InCohort].*,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject FROM
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort] WHERE
((([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear)=Forms![frmRunQueryParam-TEST].cboVictorySchoolYear)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Grade)=Forms![frmRunQueryParam-TEST].cboVictoryGrade)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject)=Forms![frmRunQueryParam-TEST].cboSubject));

:

Post the SQL statement of the subform's RecordSource query so that we
can
better assist you.
--

Ken Snell
<MS ACCESS MVP>


Hello,
I am using a form with combo boxes in Access 2003 which runs a query
with
4
to 6 parameters, and I need to display the results of the query in a
subform.
When I add a subform in Design View and run the form, I am
immediately
prompted for the parameters before the form is displayed.
Can someone offer advice on how to link the form and subform
together,
so
that the subform is only displayed after the user clicks "Run Query"
on
the
form?

The data displayed on the subform should be dependant on the
parameters
the
user selects on the main form.

Any suggestions?

 

[JCJ]

I'm getting an "Object variable or With block variable not set"

I believe it's caused by this line:
Any thoughts?

-JCJ

You cannot close the rsQuery object if you want the subform to use it as its
recordset. Delete this line from your code:

rsQuery.Close


--

Ken Snell
<MS ACCESS MVP>

The nature of my post is to get suggestions on how to write the code for
the
subform to open the parameter query (or to pass along the recordset from
the
main form). I don't have code for the subform now.

On my main form, the "onclick" event for the button to run the query has
this code:

Dim strQueryName As String
strQueryName =
"qtotStateAssessmentResults-grpPerformanceLevel-InCohort"

Dim dbThisDatabase As DAO.Database
Dim qryQueryToRun As QueryDef
Dim rsQuery As DAO.Recordset
Set dbThisDatabase = CurrentDb
Set qryQueryToRun = dbThisDatabase.QueryDefs(strQueryName)

qryQueryToRun.Parameters("[CohortStartDate]") = Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate

Set rsQuery = qryQueryToRun.OpenRecordset()

' This code below is based on your suggestion
With Me.NysQueryData.Form
If RecordSource = "" Then
.Recordset = rsQuery
Else
.Requery
End If
End With

rsQuery.Close

However, this doesn't actually open the subform yet.

Post the code that the subform is using to "open" the parameter query and
then assign that query to the subform's RecordSource.

What you'll need to do is to move that code to your main form and run it
from there for the "first" assignment.
--

Ken Snell
<MS ACCESS MVP>



Ken,
Thanks for the suggestion. However, since the subform opens a
parameter
query, how would I pass those values along using your method?
Example of parameters needed:
qryQueryToRun.Parameters("[CohortStartDate]") = Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate


:

The problem you're having is that a subform is actually loaded before
the
main form is loaded, thus the subform's query cannot "see" the main
form's
controls when the subform is loaded and its query is run. There are
different ways you can handle this situation.

My preferred way is to save your subform's RecordSource query as a
query
in
your database (name it qryNameOfYourSubform). Then, delete the
RecordSource
from the subform (design view). Then, click a button on the main form
to
"open" the subform. The code for this button would be this:

Private Sub NameOfButton_Click()
With Me.NameOfSubformControlOnMainForm.Form.
If RecordSource = "" Then
' first time the subform is being "opened", so set the
' RecordSource property for the subform
.RecordSource = "qryNameOfYourSubform"
Else
' subform has already been "opened", so just
' requery the subform's RecordSource
.Requery
End If
End With
End Sub


Please note that I've used generic names in my example
(qryNameOfYourSubform, NameOfButton, NameOfSubformControlOnMainForm),
so
replace these names with the actual names that you use when you
implement
this suggestion.
--

Ken Snell
<MS ACCESS MVP>




Here is the subform Record Source statement:

SELECT [qtotStateAssessmentResults-grpPerformanceLevel-InCohort].*,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject
FROM
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort] WHERE
((([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear)=Forms![frmRunQueryParam-TEST].cboVictorySchoolYear)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Grade)=Forms![frmRunQueryParam-TEST].cboVictoryGrade)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject)=Forms![frmRunQueryParam-TEST].cboSubject));

:

Post the SQL statement of the subform's RecordSource query so that
we
can
better assist you.
--

Ken Snell
<MS ACCESS MVP>


Hello,
I am using a form with combo boxes in Access 2003 which runs a
query
with
4
to 6 parameters, and I need to display the results of the query
in a
subform.
When I add a subform in Design View and run the form, I am
immediately
prompted for the parameters before the form is displayed.
Can someone offer advice on how to link the form and subform
together,
so
that the subform is only displayed after the user clicks "Run
Query"
on
the
form?

The data displayed on the subform should be dependant on the
parameters
the
user selects on the main form.

Any suggestions?

 

[Ken Snell \(MVP\)]

Is that the line on which the VBA code stops (if you turn off error
handling) when you get the error?

Your code is missing a leading period in this line:

If RecordSource = "" Then

It should be
If .RecordSource = "" Then

--

Ken Snell
<MS ACCESS MVP>

I'm getting an "Object variable or With block variable not set"

I believe it's caused by this line:
Any thoughts?

-JCJ

You cannot close the rsQuery object if you want the subform to use it as
its
recordset. Delete this line from your code:

rsQuery.Close


--

Ken Snell
<MS ACCESS MVP>

The nature of my post is to get suggestions on how to write the code
for
the
subform to open the parameter query (or to pass along the recordset
from
the
main form). I don't have code for the subform now.

On my main form, the "onclick" event for the button to run the query
has
this code:

Dim strQueryName As String
strQueryName =
"qtotStateAssessmentResults-grpPerformanceLevel-InCohort"

Dim dbThisDatabase As DAO.Database
Dim qryQueryToRun As QueryDef
Dim rsQuery As DAO.Recordset
Set dbThisDatabase = CurrentDb
Set qryQueryToRun = dbThisDatabase.QueryDefs(strQueryName)

qryQueryToRun.Parameters("[CohortStartDate]") = Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate

Set rsQuery = qryQueryToRun.OpenRecordset()

' This code below is based on your suggestion
With Me.NysQueryData.Form
If RecordSource = "" Then
.Recordset = rsQuery
Else
.Requery
End If
End With

rsQuery.Close

However, this doesn't actually open the subform yet.


:

Post the code that the subform is using to "open" the parameter query
and
then assign that query to the subform's RecordSource.

What you'll need to do is to move that code to your main form and run
it
from there for the "first" assignment.
--

Ken Snell
<MS ACCESS MVP>



Ken,
Thanks for the suggestion. However, since the subform opens a
parameter
query, how would I pass those values along using your method?
Example of parameters needed:
qryQueryToRun.Parameters("[CohortStartDate]") =
Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate


:

The problem you're having is that a subform is actually loaded
before
the
main form is loaded, thus the subform's query cannot "see" the main
form's
controls when the subform is loaded and its query is run. There are
different ways you can handle this situation.

My preferred way is to save your subform's RecordSource query as a
query
in
your database (name it qryNameOfYourSubform). Then, delete the
RecordSource
from the subform (design view). Then, click a button on the main
form
to
"open" the subform. The code for this button would be this:

Private Sub NameOfButton_Click()
With Me.NameOfSubformControlOnMainForm.Form.
If RecordSource = "" Then
' first time the subform is being "opened", so set the
' RecordSource property for the subform
.RecordSource = "qryNameOfYourSubform"
Else
' subform has already been "opened", so just
' requery the subform's RecordSource
.Requery
End If
End With
End Sub


Please note that I've used generic names in my example
(qryNameOfYourSubform, NameOfButton,
NameOfSubformControlOnMainForm),
so
replace these names with the actual names that you use when you
implement
this suggestion.
--

Ken Snell
<MS ACCESS MVP>




Here is the subform Record Source statement:

SELECT
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].*,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject
FROM
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort] WHERE
((([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear)=Forms![frmRunQueryParam-TEST].cboVictorySchoolYear)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Grade)=Forms![frmRunQueryParam-TEST].cboVictoryGrade)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject)=Forms![frmRunQueryParam-TEST].cboSubject));

:

Post the SQL statement of the subform's RecordSource query so
that
we
can
better assist you.
--

Ken Snell
<MS ACCESS MVP>


Hello,
I am using a form with combo boxes in Access 2003 which runs a
query
with
4
to 6 parameters, and I need to display the results of the
query
in a
subform.
When I add a subform in Design View and run the form, I am
immediately
prompted for the parameters before the form is displayed.
Can someone offer advice on how to link the form and subform
together,
so
that the subform is only displayed after the user clicks "Run
Query"
on
the
form?

The data displayed on the subform should be dependant on the
parameters
the
user selects on the main form.

Any suggestions?

 

[JCJ]

You are correct, I mistyped the code in my reply. But I was able to figure
out the problem, apparently the "Set" command was required for the code to
work.

This gives an error:
.Recordset = rsQuery

But this works:
Set .Recordset = rsQuery

Now all I have to do is figure out how to display the actual data in the
subform!

Is that the line on which the VBA code stops (if you turn off error
handling) when you get the error?

Your code is missing a leading period in this line:

If RecordSource = "" Then

It should be
If .RecordSource = "" Then

--

Ken Snell
<MS ACCESS MVP>

I'm getting an "Object variable or With block variable not set"

I believe it's caused by this line:

.Recordset = rsQuery

Any thoughts?

-JCJ

You cannot close the rsQuery object if you want the subform to use it as
its
recordset. Delete this line from your code:

rsQuery.Close


--

Ken Snell
<MS ACCESS MVP>


The nature of my post is to get suggestions on how to write the code
for
the
subform to open the parameter query (or to pass along the recordset
from
the
main form). I don't have code for the subform now.

On my main form, the "onclick" event for the button to run the query
has
this code:

Dim strQueryName As String
strQueryName =
"qtotStateAssessmentResults-grpPerformanceLevel-InCohort"

Dim dbThisDatabase As DAO.Database
Dim qryQueryToRun As QueryDef
Dim rsQuery As DAO.Recordset
Set dbThisDatabase = CurrentDb
Set qryQueryToRun = dbThisDatabase.QueryDefs(strQueryName)

qryQueryToRun.Parameters("[CohortStartDate]") = Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate

Set rsQuery = qryQueryToRun.OpenRecordset()

' This code below is based on your suggestion
With Me.NysQueryData.Form
If RecordSource = "" Then
.Recordset = rsQuery
Else
.Requery
End If
End With

rsQuery.Close

However, this doesn't actually open the subform yet.


:

Post the code that the subform is using to "open" the parameter query
and
then assign that query to the subform's RecordSource.

What you'll need to do is to move that code to your main form and run
it
from there for the "first" assignment.
--

Ken Snell
<MS ACCESS MVP>



Ken,
Thanks for the suggestion. However, since the subform opens a
parameter
query, how would I pass those values along using your method?
Example of parameters needed:
qryQueryToRun.Parameters("[CohortStartDate]") =
Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate


:

The problem you're having is that a subform is actually loaded
before
the
main form is loaded, thus the subform's query cannot "see" the main
form's
controls when the subform is loaded and its query is run. There are
different ways you can handle this situation.

My preferred way is to save your subform's RecordSource query as a
query
in
your database (name it qryNameOfYourSubform). Then, delete the
RecordSource
from the subform (design view). Then, click a button on the main
form
to
"open" the subform. The code for this button would be this:

Private Sub NameOfButton_Click()
With Me.NameOfSubformControlOnMainForm.Form.
If RecordSource = "" Then
' first time the subform is being "opened", so set the
' RecordSource property for the subform
.RecordSource = "qryNameOfYourSubform"
Else
' subform has already been "opened", so just
' requery the subform's RecordSource
.Requery
End If
End With
End Sub


Please note that I've used generic names in my example
(qryNameOfYourSubform, NameOfButton,
NameOfSubformControlOnMainForm),
so
replace these names with the actual names that you use when you
implement
this suggestion.
--

Ken Snell
<MS ACCESS MVP>




Here is the subform Record Source statement:

SELECT
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].*,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject
FROM
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort] WHERE
((([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear)=Forms![frmRunQueryParam-TEST].cboVictorySchoolYear)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Grade)=Forms![frmRunQueryParam-TEST].cboVictoryGrade)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject)=Forms![frmRunQueryParam-TEST].cboSubject));

:

Post the SQL statement of the subform's RecordSource query so
that
we
can
better assist you.
--

Ken Snell
<MS ACCESS MVP>


Hello,
I am using a form with combo boxes in Access 2003 which runs a
query
with
4
to 6 parameters, and I need to display the results of the
query
in a
subform.
When I add a subform in Design View and run the form, I am
immediately
prompted for the parameters before the form is displayed.
Can someone offer advice on how to link the form and subform
together,
so
that the subform is only displayed after the user clicks "Run
Query"
on
the
form?

The data displayed on the subform should be dependant on the
parameters
the
user selects on the main form.

Any suggestions?

 

[Ken Snell \(MVP\)]

My apologies, yes, I should have caught the missing Set. (May I plead
tiredness from overwork?)

Glad you found the fix.
--

Ken Snell
<MS ACCESS MVP>

You are correct, I mistyped the code in my reply. But I was able to
figure
out the problem, apparently the "Set" command was required for the code to
work.

This gives an error:
.Recordset = rsQuery

But this works:
Set .Recordset = rsQuery

Now all I have to do is figure out how to display the actual data in the
subform!

Is that the line on which the VBA code stops (if you turn off error
handling) when you get the error?

Your code is missing a leading period in this line:

If RecordSource = "" Then

It should be
If .RecordSource = "" Then

--

Ken Snell
<MS ACCESS MVP>

I'm getting an "Object variable or With block variable not set"

I believe it's caused by this line:
.Recordset = rsQuery

Any thoughts?

-JCJ

:

You cannot close the rsQuery object if you want the subform to use it
as
its
recordset. Delete this line from your code:

rsQuery.Close


--

Ken Snell
<MS ACCESS MVP>


The nature of my post is to get suggestions on how to write the code
for
the
subform to open the parameter query (or to pass along the recordset
from
the
main form). I don't have code for the subform now.

On my main form, the "onclick" event for the button to run the query
has
this code:

Dim strQueryName As String
strQueryName =
"qtotStateAssessmentResults-grpPerformanceLevel-InCohort"

Dim dbThisDatabase As DAO.Database
Dim qryQueryToRun As QueryDef
Dim rsQuery As DAO.Recordset
Set dbThisDatabase = CurrentDb
Set qryQueryToRun = dbThisDatabase.QueryDefs(strQueryName)

qryQueryToRun.Parameters("[CohortStartDate]") =
Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate

Set rsQuery = qryQueryToRun.OpenRecordset()

' This code below is based on your suggestion
With Me.NysQueryData.Form
If RecordSource = "" Then
.Recordset = rsQuery
Else
.Requery
End If
End With

rsQuery.Close

However, this doesn't actually open the subform yet.


:

Post the code that the subform is using to "open" the parameter
query
and
then assign that query to the subform's RecordSource.

What you'll need to do is to move that code to your main form and
run
it
from there for the "first" assignment.
--

Ken Snell
<MS ACCESS MVP>



Ken,
Thanks for the suggestion. However, since the subform opens a
parameter
query, how would I pass those values along using your method?
Example of parameters needed:
qryQueryToRun.Parameters("[CohortStartDate]") =
Me.CohortStartDate
qryQueryToRun.Parameters("[CohortEndDate]") = Me.CohortEndDate


:

The problem you're having is that a subform is actually loaded
before
the
main form is loaded, thus the subform's query cannot "see" the
main
form's
controls when the subform is loaded and its query is run. There
are
different ways you can handle this situation.

My preferred way is to save your subform's RecordSource query as
a
query
in
your database (name it qryNameOfYourSubform). Then, delete the
RecordSource
from the subform (design view). Then, click a button on the main
form
to
"open" the subform. The code for this button would be this:

Private Sub NameOfButton_Click()
With Me.NameOfSubformControlOnMainForm.Form.
If RecordSource = "" Then
' first time the subform is being "opened", so set the
' RecordSource property for the subform
.RecordSource = "qryNameOfYourSubform"
Else
' subform has already been "opened", so just
' requery the subform's RecordSource
.Requery
End If
End With
End Sub


Please note that I've used generic names in my example
(qryNameOfYourSubform, NameOfButton,
NameOfSubformControlOnMainForm),
so
replace these names with the actual names that you use when you
implement
this suggestion.
--

Ken Snell
<MS ACCESS MVP>




Here is the subform Record Source statement:

SELECT
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].*,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear,
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject
FROM
[qtotStateAssessmentResults-grpPerformanceLevel-InCohort]
WHERE
((([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].SchoolYear)=Forms![frmRunQueryParam-TEST].cboVictorySchoolYear)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Grade)=Forms![frmRunQueryParam-TEST].cboVictoryGrade)
And
(([qtotStateAssessmentResults-grpPerformanceLevel-InCohort].Subject)=Forms![frmRunQueryParam-TEST].cboSubject));

:

Post the SQL statement of the subform's RecordSource query so
that
we
can
better assist you.
--

Ken Snell
<MS ACCESS MVP>


Hello,
I am using a form with combo boxes in Access 2003 which
runs a
query
with
4
to 6 parameters, and I need to display the results of the
query
in a
subform.
When I add a subform in Design View and run the form, I am
immediately
prompted for the parameters before the form is displayed.
Can someone offer advice on how to link the form and
subform
together,
so
that the subform is only displayed after the user clicks
"Run
Query"
on
the
form?

The data displayed on the subform should be dependant on
the
parameters
the
user selects on the main form.

Any suggestions?