Query with Parameter

[Martyh]

Hi all,
I've got a query with a parameter in a subform and everything is alright. I get the parameter [cboSystem] from the response I make to a combo box.

I don't know if this makes a difference but the form I'm dealing with is not bound.

However once I try to make a query based on the same [cboSystem] parameter (a button that has just the DoCmd.OpenQuery command), the new query doesn't seem to know that it exists! This query is in the main form.

HELP! How can I fix this?

Regards,

Marty

 

[MarkK]

...once I try to make a query based on the same [cboSystem] parameter...

What does this mean?

 

[isladogs]

Suggest you post the query SQL. Also what is the output datatype for the combo

 

[Martyh]

Colin,

I thought I solved my problem by using DoCmd.SetParameter but now I seem to have another:

Run-time error '2766':
The object doesn't contain the Automation object 'LLAD.'

The 'LLAD.' refers to the system (all except the period at the end)...

Am I on the right track ... sorry I can't post the code... security prevents me from doing so!

Thanks ,
Marty

 

[Mark_]

If you are referencing the same control, you will need to make sure that control's parent form is open when you run the query or you will have issues.

 

[Martyh]

Mark_, Colin

I have the parent form open thru out ...

However, I think I might have found a clue... the Query is based upon an IN (SELECT ... statement) which is actually requesting the parameter ... not the query itself.

The SetParameter command requires that the method must process the request with the SetParameter IMMEDIATELY in front of it... which technically it is not.

Maybe this has something to do with it? Have you any further suggestions... ideas?

Marty

 

[Mark_]

If you post the SQL and surrounding code we can take a look.

I'd break it out as "Code that opens the query" then "SQL for query" and "Code after the query" so we can see if anything is out of order.

 

[Martyh]

OK...

Code that opens the query
Just the combo box that opens the query and then after the update of the box comes: 
Private Sub cboSystem_AfterUpdate()
Me.Requery
Me.Section(acDetail).Visible = True
'Me.Detail.Visible = True
End Sub

SQL for query that has no problem (this is the summary query that comes from a subform) There is no need to put a parameter because it's already taken care of by the combo box:

(if you run this query  by itself you will get a Parameter request)

SELECT qryAllCurrentStock.Seq AS Sequence, Count(tblNSN.NSN) AS CountOfNSN, Sum(IIf([Total Stock]>0,1,0)) AS NSNStockGTZero, Sum(qryAllCurrentStock.[Total Stock]) AS [SumOfTotal Stock], Sum(qryAllCurrentStock.[Total Value]) AS [SumOfTotal Value]
FROM relTA INNER JOIN (tblNSN INNER JOIN qryAllCurrentStock ON tblNSN.NSN_ID = qryAllCurrentStock.NSN_ID) ON relTA.TAC = tblNSN.LeadTA
WHERE (((tblNSN.Project)="MR") AND ((tblNSN.InScope)=Yes) AND ((tblNSN.NSN_ID) In (Select NSN_ID from [qryNSN_For_System];)))
GROUP BY qryAllCurrentStock.Seq
ORDER BY qryAllCurrentStock.Seq;

SQL for query that has the problem comes afterwards (this is the detail query). The query happens on a button (doCmd.openquery):
SELECT tblNSN.GroupClass, tblNSN.NSN, tblNSN.Description, tblNSN.LeadTA, tblNSN.SMC, tblNSN.IM, tblNSN.StockType, tblNSN.StockClass, tblNSN.DMC, tblNSN.DPA2, tblNSN.DPA1, qryAllCoreStock.[Total Stock] AS [Total Core SOH], qryAllCurrentStock.[Total Stock] AS [Total Current SOH], qryAllRRStock.[Total Stock] AS [Total RR SOH], tblNSN.RC, tblNSN.UOI, tblNSN.PricePerUOI, IIf([qryAllCurrentStock].[Total Value]>0,[qryAllCurrentStock].[Total Value],[PricePerUOI]*[Total Current SOH]) AS [Tot Value], tblDIR.Decision, tblDIR.Dir AS [DSP DIR], tblDIR.DIRCheck, tblDIR.Main, tblNSN.Project
FROM (((tblNSN INNER JOIN qryAllCurrentStock ON tblNSN.NSN_ID = qryAllCurrentStock.NSN_ID) INNER JOIN qryAllCoreStock ON tblNSN.NSN_ID = qryAllCoreStock.NSN_ID) INNER JOIN qryAllRRStock ON tblNSN.NSN_ID = qryAllRRStock.NSN_ID) INNER JOIN tblDIR ON tblNSN.NSN_ID = tblDIR.NSN_ID
WHERE (((tblDIR.Main)=0) AND ((qryAllCurrentStock.Seq)=4 Or (qryAllCurrentStock.Seq)=3) AND ((tblNSN.NSN_ID) In (Select NSN_ID from [qryNSN_For_System];)));

 

[isladogs]

This may be clear to you but it isn't clear at this end.

Where is the code that opens the first query?

You haven't said what the cboSystem output is.

It's not at all clear from the first query what parameter is provided by that value and therefore what the parameter request is in the second query.

What's the subquery in the IN clause?

You are going to have to help us more or we can't help you.

 

[Mark_]

I'm with Colin. How does cboSystem relate as I see no reference to it in your SQL. Also, wouldn't you normally make the detail visible FIRST, then do the requery? Trying to remember something about refreshing on an invisible portion of the screen...

 

[jdraw]

@Marty,

You have not provided sufficient context for readers to understand your issue.
Can you provide a copy of the database with a few records and some instructions for readers to get to and highlight the issue?