form versus query

[cvaccess]

Hello,

Something strange is going on with a query I have. I have a command button on a form that pulls from a query "RejectPercentTPSummary2". When I run the query through the form (command button) it brings up an error box stating "You cancelled the previous operation." But when I run the query, natively, through the Query Tab it runs fine. Why is this?

Thanks.

 

[RivetHed]

Could you post up the code for the buttons onclick event procedure.

 

[cvaccess]

Here it is. Thanks.

Private Sub cmdRejectSummary_Click()
On Error GoTo Err_cmdRejectSummary_Click

Dim stDocName As String

stDocName = "RejectPercentTPSummary2"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdRejectSummary_Click:
Exit Sub

Err_cmdRejectSummary_Click:
MsgBox Err.Description
Resume Exit_cmdRejectSummary_Click

End Sub

 

[RivetHed]

You haven't renamed the query at any point have you? as this would result in the sort of error message you mention, is it definately called "RejectPercentTPSummary2"? You could also just try setting up another button using the wizard and seeing if that has the same problem

 

[cvaccess]

I created another command button pulling the same query and the same error came up. I have not modified the queries at all. I believe I mentioned earlier that the query runs fine when I run it through the Queries tab. Thanks.

 

[RivetHed]

Ok try replacing the current code with just this:

Private Sub cmdRejectSummary_Click()

DoCmd.OpenQuery "RejectPercentTPSummary2"

End Sub

does this still generate an error message?

 

[cvaccess]

This bring up a similar error "Run-time error '2001': You canceled the previous operation." It give me the option to debug, end or help. The debug just highlights the do.cmd line.

 

[RivetHed]

I'm afraid I'm a bit stumped by this, that line of code is the absolute bare minimum, all it does is open a query called RejectPercentTPSummary2 I have no idea why it wouldn't be able to do that, have you had this problem anywhere else or is it just this one button on this one form?

 

[cvaccess]

Just this one.

 

[RivetHed]

do you still get the error if you copy and paste the query and then amend the code to reference the copy? This is a bit of a last ditch sort of thing cos I'm afraid I'm just about out of ideas after this.

 

[cvaccess]

Now I get another error which seems like the main reason ( I think) "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"

 

[RivetHed]

did you set up the link to the copy of the query with the wizard or do you now just have
DoCmd.OpenQuery "CopyOfQuery"?

 

[cvaccess]

I didn't even get that far I was trying to run it through the query tab first and got this error. I have 2 subqueries for this "RejectPercentTPSummary2", "RejectPercentTPSummary1" and "RejectPercentTPSummary". I think the "RejectPercentTPSummary2" is too complex.

 

[RivetHed]

pop the SQL for your query up and we might be able to get to the bottom of this

 

[cvaccess]

I am thinking of doing a 4th query to take out the where clause on summary2. This might be making it too complex currently.Here it goes:
RejectPercentTPSummary
SELECT DISTINCTROW rejected_summary.TP_NUM AS TP, Sum(rejected_summary.TOTAL) AS [Rejected Total], Sum(indiv_tp_sub.CLAIM_SUB) AS [Submitted Total], [Enter Start Date:] & " - " & [Enter End Date:] AS [Given Time Period]
FROM rejected_summary INNER JOIN indiv_tp_sub ON (rejected_summary.SCRUB_DATE = indiv_tp_sub.SCRUB_DATE) AND (rejected_summary.TP_NUM = indiv_tp_sub.CHILD_TP)
WHERE (((rejected_summary.SCRUB_DATE) Between [Enter Start Date:] And [Enter End Date:]) AND ((rejected_summary.TP_NUM)=[indiv_tp_sub].[child_tp]))
GROUP BY rejected_summary.TP_NUM
ORDER BY rejected_summary.TP_NUM;

RejectPercentTPSummary1
SELECT RejectPercentTPSummary.TP, RejectPercentTPSummary.[Rejected Total], RejectPercentTPSummary.[Submitted Total], RejectPercentTPSummary.[Given Time Period]
FROM RejectPercentTPSummary
WHERE (((RejectPercentTPSummary.TP)<>&quot;P03&quot; And (RejectPercentTPSummary.TP)<>&quot;PZJ&quot; And (RejectPercentTPSummary.TP)<>&quot;PZT&quot; And (RejectPercentTPSummary.TP)<>&quot;PZV&quot; And (RejectPercentTPSummary.TP)<>&quot;PZN&quot; And (RejectPercentTPSummary.TP)<>&quot;PZL&quot; And (RejectPercentTPSummary.TP)<>&quot;PZQ&quot; And (RejectPercentTPSummary.TP)<>&quot;PZX&quot);

RejectPercentTPSummary2
SELECT RejectPercentTPSummary1.TP, RejectPercentTPSummary1.[Rejected Total], RejectPercentTPSummary1.[Submitted Total], RejectPercentTPSummary1.[Given Time Period], [RejectPercentTPSummary1].[Rejected Total]/[RejectPercentTPSummary1].[Submitted Total] AS [Reject %]
FROM RejectPercentTPSummary1
WHERE (((([RejectPercentTPSummary1].[Rejected Total])/([RejectPercentTPSummary1].[Submitted Total]))>=IIf([Enter Reject %:]>=0,([Enter Reject %:]/100),CDbl([Enter Reject %:]))))
GROUP BY RejectPercentTPSummary1.TP, RejectPercentTPSummary1.[Rejected Total], RejectPercentTPSummary1.[Submitted Total], RejectPercentTPSummary1.[Given Time Period], [RejectPercentTPSummary1].[Rejected Total]/[RejectPercentTPSummary1].[Submitted Total]
ORDER BY RejectPercentTPSummary1.TP;

 

[cvaccess]

I noticed that it works fine if I do a small span date, such as 01/01/03-01/10/03. If I do the entire month I get that long error message.

 

[RivetHed]

can I assume that [enter reject %:] is a parameter rather than a field?

 

[cvaccess]

Right

 

[RivetHed]

Ok try replacing the parameter with a reference to a text box on your form so instead of [enter reject %:] have [forms]![myForm]![MyTextBox] input your parameter into the text box and then try and run the query (leaving the form open)

 

[cvaccess]

Here is my thing. Why does the same set of queries for another data type work fine right now for the entire month? It is the same with the exception of referring to different tables.
For example:
SELECT PendPercentTPSummary1.TP, PendPercentTPSummary1.[Pended Total], PendPercentTPSummary1.[Submitted Total], PendPercentTPSummary1.[Given Time Period], (PendPercentTPSummary1.[Pended Total])/(PendPercentTPSummary1.[Submitted Total]) AS [Pend %]
FROM PendPercentTPSummary1
WHERE (((([PendPercentTPSummary1].[Pended Total])/([PendPercentTPSummary1].[Submitted Total]))>=IIf([Enter Pend %:]>=0,([Enter Pend %:]/100),CDbl([Enter Pend %:]))))
GROUP BY PendPercentTPSummary1.TP, PendPercentTPSummary1.[Pended Total], PendPercentTPSummary1.[Submitted Total], PendPercentTPSummary1.[Given Time Period], (PendPercentTPSummary1.[Pended Total])/(PendPercentTPSummary1.[Submitted Total])
ORDER BY PendPercentTPSummary1.TP;

I really would prefer not to change the form layout as I have three other forms that work fine with this set of queries. Could it be so much data involved when running it?