Parameter query in form

[Guest]

I have a form that I use as a subform for numerous forms that contains a
parameter query. All works fine.

I am creating a new form, and at load time want to open the subform
mentioned above in a separte window.

Problem is that when I execute a Docmd.Openform for the subform, I don't
know how to resolve the parameter query. It seems no event is executed
before the prompt to resolve the parameter for the query in the form is
issued.

Ideas??

 

[Ken Snell [MVP]]

Huh? I'm not understanding you, I think. Your subform has a query as its
RecordSource, and that query asks for a parameter? And you want the query to
get its own parameter value without you having to enter it? or you do want
to enter it but not when it asks for it?

A bit more detail here, please.

 

[Guest]

First, thanks for your time and reply.

Yes, my subform(lets call it subform99) has a query as its recordsouce and
it is a parameter query.

This "subform" saved as a form (subform99) that you can see in the database
window.

So, when if I use subform99 truely as a subform, I can resolve the parameter
query by putting a field on the main form with the same name as the parameter
name of the query, and everything works ok.

However, if I open the subform via the "on load" event of the main form, I
am prompted for the parameter for the subform. What I am trying to
accomplish is to resolve the parameter so the prompt does not appear.

Hope I explained the situation better, and look forward to your response!

 

[Ken Snell [MVP]]

Your main form is "loading" the subform in the main form's "Load" event?
What code are you using for this? are you assigning a Source Object to a
subform control? or something else?

Normally, when the subform is part of a main form's design, the subform is
"loaded" before the main form is loaded, so when your subform looks for the
parameter, it would not see the main form yet.

If you're using a parameter that is a control on the main form, why not link
the main form and subform together via the LinkChildFields and
LinkMasterFields properties of the subform control. That will filter the
subform for you based on the control/field on the main form that you use in
the LinkMasterFields property.

Otherwise, post the SQL statement of the subform's RecordSource and explain
the parameter in terms of what it is and what you are using it for.
--

Ken Snell
<MS ACCESS MVP>

 

[leon]

Good to see that parametersqueries as rowsource in a form seem to be
giving more people problems. I have the same kind of issue and nobody
has been able to give me a decent answer. I have a form with a
combobox and a listbox. The rowsource of the listbox is a
parameterquery. The parameter is the value in the combobox. When I
open the form it starts asking me for the parameter which I do not
want it to do. I cannot seem to solve this.

 

[Ken Snell [MVP]]

What you will need to do, leon, is to have an empty RowSource for the
listbox when the form is loaded, and then run code after the form loads to
give the listbox a RowSource; that way, the parameter will be available for
the query to use.

 

[Guest]

This is the record source for "SUBFORMA"

SELECT [To Do List].*, nz([Delivery Date],0) AS Expr1, Customers.*
FROM Customers INNER JOIN [To Do List] ON Customers.CustomerID = [To Do
List].[To Do_CustomerID]
WHERE (((nz([Delivery Date],0))<=[Delivery Date Criteria]))
ORDER BY [To Do List].[To Do_CustomerID], [To Do List].[Proj #], [To Do
List].Tseq;

If I have a field on the main form called "Delivery Date Criteria", I am not
prompted for the parameter.

However if I execute from the main form's on load event:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "SUBFORMA"
DoCmd.OpenForm stDocName, , , , acFormEdit, acWindowNormal

I am prompted for the parameter of "Deliver Date Criteria".

I am using the parameter to select a set of records based upon the date.

Thx.

 

[Ken Snell [MVP]]

What is Delivery Date Criteria parameter? is it a field in the Customer or
To Do List tables/queries? Is it supposed to be a field/control on the main
form?


--

Ken Snell
<MS ACCESS MVP>

This is the record source for "SUBFORMA"

SELECT [To Do List].*, nz([Delivery Date],0) AS Expr1, Customers.*
FROM Customers INNER JOIN [To Do List] ON Customers.CustomerID = [To Do
List].[To Do_CustomerID]
WHERE (((nz([Delivery Date],0))<=[Delivery Date Criteria]))
ORDER BY [To Do List].[To Do_CustomerID], [To Do List].[Proj #], [To Do
List].Tseq;

If I have a field on the main form called "Delivery Date Criteria", I am
not
prompted for the parameter.

However if I execute from the main form's on load event:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "SUBFORMA"
DoCmd.OpenForm stDocName, , , , acFormEdit, acWindowNormal

I am prompted for the parameter of "Deliver Date Criteria".

I am using the parameter to select a set of records based upon the date.

Thx.

Your main form is "loading" the subform in the main form's "Load" event?
What code are you using for this? are you assigning a Source Object to a
subform control? or something else?

Normally, when the subform is part of a main form's design, the subform
is
"loaded" before the main form is loaded, so when your subform looks for
the
parameter, it would not see the main form yet.

If you're using a parameter that is a control on the main form, why not
link
the main form and subform together via the LinkChildFields and
LinkMasterFields properties of the subform control. That will filter the
subform for you based on the control/field on the main form that you use
in
the LinkMasterFields property.

Otherwise, post the SQL statement of the subform's RecordSource and
explain
the parameter in terms of what it is and what you are using it for.