Parameter prompt twice, only when using a form (1 Viewer)

rachelmillerm

New member
Local time
Today, 18:48
Joined
Jan 6, 2022
Messages
6
Hi! So. Without talking about the pros/cons of using parameter values prompts, I have a thing I can't quite resolve. I have a large crosstabs query that calls on multiple other queries. When I just open it as a query, it asks for the reporting period (the parameter input) no problem.

I made a pretty form for the output, and now when I access the query via a command button, it asks for the parameter values twice. There is nothing in the structure of the query (1 parameter only etc.) that would make it do this and again, it only happens when I call it via the form.

Any ideas of places to look?
Thank you.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:48
Joined
Jan 23, 2006
Messages
14,317
Perhaps you could show your query SQL and related code to invoke it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:48
Joined
Oct 29, 2018
Messages
18,962
Hi. Can you show us the code behind the button to call the query?

Edit: Oops, too slow...
 

rachelmillerm

New member
Local time
Today, 18:48
Joined
Jan 6, 2022
Messages
6
Sure thing. Like I said, there's no problem with the query itself, runs as it should. There's something happening when I put the form on top- when I open it directly as a form and when I use this command button to open it, the parameters input runs twice. I was trying to avoid sharing the query SQL because it is absurdly long. But here's all of it- thank you for taking a look.

First: Here's the code that opens the form whose record source is the query:

DoCmd.OpenForm "ReportNumbersSummary"
Oh- incidentally- I had tried using this line of code to call the query to the form:
Forms!FormReportNumbersSummary.RecordSource "Quarterly-Report-New"
But that returns a compile error, "invalid use of property"
I haven't figured that one out yet, I use the RecordSource property all the time exactly like this. In any case. When the RecordSource didn't work, I just set the data source in the form property.

Here is the query SQL: (actually, here is the first of 19 statements all identical except they are pulling from different queries, I run into the character limit when I tried to post it in full)
SELECT 1 as SEQ,'Participants who completed Orientation/Application Pickup' AS Title, Count([Quarterly-Report-Orientation].ParticipantID) AS Total, Count (IIF ([Ethnicity]=1,1,Null)) AS [Hispanic/Latino], Count (IIF ([Ethnicity]=0,1,Null)) AS [Non-Hispanic], Count (IIF ([Race]=1,1,Null)) AS White, Count (IIF ([Race]=2,1,Null)) AS [Black/African-American], Count (IIF ([Race]=3,1,Null)) AS Asian, Count (IIF ([Race]=4,1,Null)) AS [American Indian/Alaskan Native], Count (IIF ([Race]=5,1,Null)) AS [Native Hawaiian/Other Pacific Islander], Count (IIF ([Race]=6,1,Null)) AS [American Indian and White], Count (IIF ([Race]=7,1,Null)) AS [Asian and White], Count (IIF ([Race]=8,1,Null)) AS [Black/African-American and White], Count (IIF ([Race]=9,1,Null)) AS [American Indian and Black/African-American], Count (IIF ([Race]=10,1,Null)) AS [Other/Multi-Racial], Count (IIF ([Gender]=1,1,Null)) AS Male, Count (IIF ([Gender]=2,1,Null)) AS Female, Count (IIF ([Gender]=3,1,Null)) AS [Self-Identify], Count (IIF ([Veteran]=-1,1,Null)) AS VeteranCount, Count (IIF ([IEIncomeEligibility]=1,1,Null)) AS [IE: Extremely Low], Count (IIF ([IEIncomeEligibility]=2,1,Null)) AS [IE: Very Low], Count (IIF ([IEIncomeEligibility]=3,1,Null)) AS [IE: Low Moderate], Count (IIF ([IEIncomeEligibility]=4,1,Null)) AS [IE: Moderate], Count (IIF ([IEIncomeEligibility]= 5 OR [IEIncomeEligibility] =6,1,Null)) AS [IE: Non-Moderate], Count (IIF ([CJConviction]= -1,1,Null)) AS [Justice Involved], Count (IIF ([CJIncarcerated]= -1,1,Null)) AS [Formerly Incarcerated]
FROM [Quarterly-Report-Orientation];
UNION ALL
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:48
Joined
Oct 29, 2018
Messages
18,962
Hi. Thanks for the additional information. Just as a quick test, what happens if you open the form manually from the Nav Pane? Do you get the prompt twice also?
 

rachelmillerm

New member
Local time
Today, 18:48
Joined
Jan 6, 2022
Messages
6
Hi. Thanks for the additional information. Just as a quick test, what happens if you open the form manually from the Nav Pane? Do you get the prompt twice also?
Yup- when I open from the Navigation Pane or use the command button, the parameter dialogue runs twice. When I open the query directly from the Nav Pane, it only opens once.

I went through each of the queries the crosstabs query references last night to see if one of them was double parameterized somehow, nope. I like a puzzle, but this one is stumping me.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:48
Joined
Oct 29, 2018
Messages
18,962
Yup- when I open from the Navigation Pane or use the command button, the parameter dialogue runs twice. When I open the query directly from the Nav Pane, it only opens once.

I went through each of the queries the crosstabs query references last night to see if one of them was double parameterized somehow, nope. I like a puzzle, but this one is stumping me.
That tells me the problem is with the form. Do you have any code running in the Open or Load event? Also, take a closer look at each prompt and verify that it wasn't because of a typo.
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:48
Joined
Jan 23, 2006
Messages
14,317
Based on your query, I mocked up a table and ran the query. There was no parameter prompt. So, I think DBGuy is correct and issue is with the form.

Table:
Quarterly-Report-Orientation Quarterly-Report-Orientation

IDParticipantIDEthnicityRaceGenderVeteranIEIncomeEligibilityCJConvictionCJIncarcerated
1​
230​
1​
2​
2​
2​
2​
240​
0​
1​
1​
-1
4​
-1-1
3​
250​
0​
7​
3​
3​
4​
260​
0​
4​
1​
-1
6​
-1-1
Query:
Quarter.PNG

Query Result:
Query59 Query59

SEQTitleTotalHispanic/LatinoNon-HispanicWhiteBlack/African-AmericanAsianAmerican Indian/Alaskan NativeNative Hawaiian/Other Pacific IslanderAmerican Indian and WhiteAsian and WhiteBlack/African-American and WhiteAmerican Indian and Black/African-AmericanOther/Multi-RacialMaleFemaleSelf-IdentifyVeteranCountIE: Extremely LowIE: Very LowIE: Low ModerateIE: ModerateIE: Non-ModerateJustice InvolvedFormerly Incarcerated
1​
Participants who completed Orientation/Application Pickup
4​
1​
3​
1​
1​
0​
1​
0​
0​
1​
0​
0​
0​
2​
1​
1​
2​
0​
1​
1​
1​
1​
2​
2​
 

rachelmillerm

New member
Local time
Today, 18:48
Joined
Jan 6, 2022
Messages
6
Based on your query, I mocked up a table and ran the query. There was no parameter prompt. So, I think DBGuy is correct and issue is with the form.

Table:
Quarterly-Report-Orientation Quarterly-Report-Orientation

IDParticipantIDEthnicityRaceGenderVeteranIEIncomeEligibilityCJConvictionCJIncarcerated
1​
230​
1​
2​
2​
2​
2​
240​
0​
1​
1​
-1
4​
-1-1
3​
250​
0​
7​
3​
3​
4​
260​
0​
4​
1​
-1
6​
-1-1
Query:
View attachment 97345

Query Result:
Query59 Query59

SEQTitleTotalHispanic/LatinoNon-HispanicWhiteBlack/African-AmericanAsianAmerican Indian/Alaskan NativeNative Hawaiian/Other Pacific IslanderAmerican Indian and WhiteAsian and WhiteBlack/African-American and WhiteAmerican Indian and Black/African-AmericanOther/Multi-RacialMaleFemaleSelf-IdentifyVeteranCountIE: Extremely LowIE: Very LowIE: Low ModerateIE: ModerateIE: Non-ModerateJustice InvolvedFormerly Incarcerated
1​
Participants who completed Orientation/Application Pickup
4​
1​
3​
1​
1​
0​
1​
0​
0​
1​
0​
0​
0​
2​
1​
1​
2​
0​
1​
1​
1​
1​
2​
2​
Yup- thanks. I am aware of that. The parameter prompts are in the queries this query calls. But there is no issue with those queries either. They all run happily and as they should. I am wondering if anyone has encountered this problem or a problem like this when they call a parameterized query to a form. It's definitely, as I said, something to do with running the form on top of the query, but there's nothing I can see that would make this happen.
 

GPGeorge

Grover Park George
Local time
Today, 15:48
Joined
Nov 25, 2004
Messages
662
Yup- thanks. I am aware of that. The parameter prompts are in the queries this query calls. But there is no issue with those queries either. They all run happily and as they should. I am wondering if anyone has encountered this problem or a problem like this when they call a parameterized query to a form. It's definitely, as I said, something to do with running the form on top of the query, but there's nothing I can see that would make this happen.
Focus on the form, not on the query. That's what people are trying to get across.

It may be as simple as a filter set on the form that is still there.... But the answer will be found in the form.
 

rachelmillerm

New member
Local time
Today, 18:48
Joined
Jan 6, 2022
Messages
6
I get it- I am focused on the form- sorry- I feel like I've said that a few times. Thank you. There is no code associated with the form. There is no filter on the form. There is nothing basically on the form, except the call to the query as the data source and some basic button commands (exit, export, etc.) I asked here in case this was a thing that people experienced with forms and queries with parameter dialogues- but I am guessing no. Thanks all for looking into it with me.
 

GPGeorge

Grover Park George
Local time
Today, 15:48
Joined
Nov 25, 2004
Messages
662
It is possible that the form is calling your query twice. That would account for the repeated parameter dialog.
Is this a subform, perhaps, embedded in a main form? Does the form contain a combo or listbox with the same query as its rowsource?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:48
Joined
Oct 29, 2018
Messages
18,962
I get it- I am focused on the form- sorry- I feel like I've said that a few times. Thank you. There is no code associated with the form. There is no filter on the form. There is nothing basically on the form, except the call to the query as the data source and some basic button commands (exit, export, etc.) I asked here in case this was a thing that people experienced with forms and queries with parameter dialogues- but I am guessing no. Thanks all for looking into it with me.
Hi. Did you follow my advice to take a closer look at each prompt? Are they exactly the same? Can you post a screenshot of each one?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:48
Joined
Feb 19, 2002
Messages
36,284
Do not use embedded spaces or special characters in your object names. ONLY use a-z, A-Z, 0-9, and the underscore. Any other character requires that the name always be encased in square brackets. That may be causing the error.
 

Users who are viewing this thread

Top Bottom