Parameter query in subreport prompting twice! 3

[beadgirl]

Hi again,

No VB or SQL here - just using Access' built-ins. The field is Location. In the parameter query, the criteria is [Enter Store Number]. Then the qry also pulls in city and state. Works just fine.

I have a report with unrelated data from an unrelated table. I want the user to be able to just enter a store number and have the city/state pull up. So I made a subreport from the qry above. If you preview just the subreport, it works fine.

If you preview the main report, you get the "Enter Store Number" prompt two times. If you enter it twice, the report looks great. What's happening?

Thanks,
beadgirl

 

[GDGarth]

I'm assuming you only have the criteria in one query, correct? This happens to me sometimes, and I'm still not sure why. Here's what I do to get rid of it:

First, check to make sure you don't have the criteria in a report field, or something. The easiest way to do that is to run the report, and put in something entirely different in each prompt box. That may let you see if you've accidentally duplicated your criteria in a field. Also, try running just the query. It should behave the same way as the report (two prompts, if that's what you're getting)

Assuming that's not true, delete the criteria from the table and run your report. If it's not duplicated, you should no longer get the prompt (if you do, you've got that criteria listed somewhere, probably a text box on your report, or something similar). If your report runs ok without the prompt, save the query and exit Access.

Next, open access, and open the query in design view. Add your criteria back in and save it.

It seems to fix it for me after I delete the criteria and then recreate it. I'm pretty sure when it's happened to me that it's nothing in the design - since it goes away eventually.

 

[beadgirl]

Hmmm... Yes, one parameter in one query. Then that query is in a subreport, which is in the Report Header section of my main report. Running the query or subreport alone brings up only one prompt. Running the main report makes it prompt twice...

I tried your suggestion - deleting the column out of the query and saving, then putting it back in, etc. but it didn't really help. I don't know what it's thinking!

 

[Rick39]

Check the Sorting & Grouping settings of the report - that's usually where I've found my problems

 

[beadgirl]

Well, the sorting/grouping looks ok - the rest of the report runs normally. There are no sorting/grouping options shown in the main report for the Location subreport. I even went into the subreport and did a group by Location but that didn't help. I can't figure out why the subreport runs properly, and then when I incorporate it into the main report, it prompts twice. It's like it prompts once for the subreport and once for the report. Weird.

 

[beadgirl]

Hey, another interesting tidbit... When you "preview" the report, it asks twice for the Location. Then if you print it, it asks 3 more times!!! This is going to peeve my users if I can't figure it out

 

[GDGarth]

If you remove the parameter, it doesn't prompt you at all, right? Try putting parameters in a text box on a form, then click a button on a form to run the report. That way, it shouldn't prompt you for a parameter, and it can look it up as many times as it wants to.

That way, when your users need to run the report, they fill in the blanks on a form instead of being prompted for parameters.

Like I said, this problem seems to crop up now and then, but I've always been able to make it go away, eventually.

 

[PaulBricker]

The Report prompts you because of the way it formats. It formats the controls on the Main Report which includes the subreport control, and then it formats the subreport so you have two requests for parameters. Using a Form, like GDGarth's last post is the only easy work around for what you want to do. You will have to set a reference in the Query to the Form. You do this by putting
Forms!FormName!ControlName
on the criteria line for the Field you want to filter by.

Paul

 

[beadgirl]

Haven't done this before - working on it right now. I'll keep you posted - thanks!

 

[beadgirl]

Geez... I have it halfway working. I have a form with just a text box for input. Then in the query, I put it in the criteria as [Forms]![Form]![Field] - of course with my field & form names. On the form, I have tried a button to run the report, and also tried a macro "after update" to run the report when you leave the field. Neither works right. If I run the main report, I get no data in the report, including lack of column headings. If I make the form run the subreport, I get the column headers but no data. If I just run the query, I get everything I need in table view. I'm supposed to put in the location code and it's supposed to print the location code, city and state. I don't get it! Ack!

 

[PaulBricker]

What is the Record Source for the Main Report? What is the Record Source for the SubReport? Are they linked and are master field/child field linked?
I'm out of here and won't be back for a while. Heavy snows here will slow down my commute home.

Paul

 

[beadgirl]

Well... It's an "unrelated" subreport - I have a database of store layouts with part numbers, prices, etc. The main report pulls all of that together. But in the header of the report, I want to put the Store Location Number, and then from that number, automatically pull the city and state. The store location is not relevant to the store layouts etc. So there aren't any child/master relationships to speak of. This thing works fine if I don't use the form to input the location parameter - except that it asks twice. There's some disconnect between the form and the report now when I run it this new way...
I'm going home too - getting snowed in! Thanks

 

[PaulBricker]

The first thing I would try is opening the Report by hand. Put the prompt in your query, open the form and insert a Location, and then double click on the Main Report to open it. What happens. It should work no matter which way you open it. If the query gets the value from the Form and returns the correct record(s) then the report should work properly. What code are you using to open the Report?
I'll be around on and off today. The snows have everything kind of messed up.

Paul

 

[jioek]

Recreate the form or the report.Copy and paste all controls and code into a new form.

I had a form that was screwrd up and that worked for me.

 

[lameid]

Don't forget that the value in the form as to be committed before you run your report to get it to work... Simply moving focus out of the cell on the form will work. Or you could hit the record selector to save the unbound value. Typically I have a button on the report that runs whatever report I'm using the form as criteria for. That way when I click the button, the focus leaves the criteria control and everything works ok. I'm guessing that is your problem with the form.

 

[Parax]

soz if this has been stated I did'nt read all replies thoroughly:

The prompt occurs twice because of the way the Jet engine passes through the query on multiple occasions, stopping it can be as simple as removing the sort. Alternativly try setting the criteria the other way around so that the data is 'expr1:[Enter Store Number]' and the criteria is '

.[Field]' This sometimes solves the issue.

hope this has been helpful

Mike

 

[beadgirl]

Ack!
Ok...
Mike - it works switching the parameters like you said, but only when I run the query - if I run the report, it asks Enter Store Number AND qry.Locations.Location, and it asks both things twice. There is no sort on the query.
Paul - if I have no value in the form field, and just run the query, it works fine. As soon as I enter a value in the form, it pulls blank fields in both the query & the forms - maybe we're onto something there... I have set up a button to run the report (from the toolbox - no actual code writing) and also tried a macro to open the report. The macro runs upon exit from the field. Both work, but pull blanks from the query.
LameID - yes, I do commit the value in the form, either by exiting the field, hitting enter, or pressing a button that runs the report.
Thank you everybody - hope you can all keep troubleshooting with me!

 

[lameid]

Could you open your query, switch to SQL view and copy and paste the SQL here? I don't know what's wrong but it sounds like it is in the SQL because running the query like Paul suggested doesn't give you the right results.

 

[beadgirl]

Original query as Paul suggested (yes, I took the default form name "Form1" due to laziness, LOL!):

SELECT Locations.Location, Locations.City, Locations.ST
FROM Locations
WHERE (((Locations.Location)=[Forms]![Form1]![Location]));

-------

Form1 code (with button to run the report):

Private Sub OpenLayoutModulesReport_Click()
On Error GoTo Err_OpenLayoutModulesReport_Click

Dim stDocName As String

stDocName = "rptLayoutModules"
DoCmd.OpenReport stDocName, acPreview

Exit_OpenLayoutModulesReport_Click:
Exit Sub

Err_OpenLayoutModulesReport_Click:
MsgBox Err.Description
Resume Exit_OpenLayoutModulesReport_Click

End Sub

 

[lameid]

Looks ok to me. But try changing the name of the cotrol Location to something like txtLocation or cboLocation to indicate the control name and see if that helps (the real point here is to make it different than a field name). Also change it in your query.

Try running the query again with the from value committed and see if you get correct results.

If not, let us know so. I may be out of shots in the dark to try, after this...