Prompted for "Parameter Value" multiple times - should be only once

[dougdrex]

Using Access 2010



I have a button labeled (Run BIRTHDAY Report) that runs a report called Rpt_AllBirthdays. The data source for Rpt_AllBirthdays is two queries, Qry_CBirthdayReport and Qry_SBirthdayReport.

The queries are set up to prompt for the month (1=January, 2=February, etc). For some reason, the report started prompting multiple times when it is run. I don't know if the problem is in the data, or the queries.

I've attached the db for review. Any help would be much appreciated!

Thanks!
Doug

[rpeare]

You probably shouldn't be distributing a file with real data in it, I'd suggest you remove this as quickly as possible and put up a database with garbage data in it.

[dougdrex]

Thanks for the heads up. I made a copy of it, then zipped and attached the wrong db. Attached is the correct one with limited fictional data.

[rpeare]

The reason your report is prompting you as often as it does is because your subreport is in the DETAIL section of your report but you have supplied no linking criteria. Secondly you do not need to have 'spouse' fields at all in your table, but if you do not want to consider a change in structure to this table you can simply include the spouse information in the query Qry_CBirthdatyReport this way:

SELECT tbl_Clients.ClientID, [FirstName] & " " & [LastName] AS Name, tbl_Clients.BirthDate, tbl_Clients.Address, tbl_Clients.City, tbl_States.[State Abbreviation], tbl_Clients.ZipCode, tbl_Clients.HomePhone, tbl_Clients.CellPhone, tbl_Clients.BusinessPhone, tbl_Clients.Email, tbl_Clients.SpouseBirthDate, IIf(Month([spousebirthdate])=[Enter Month Number],[SpouseFirstName] & " " & [SpouseLastName],Null) AS SpouseName, IIf(Month([spousebirthdate])=[Enter Month Number],[spousebirthdate],Null) AS SpouseDate
FROM tbl_States RIGHT JOIN tbl_Clients ON tbl_States.ID = tbl_Clients.State
WHERE (((tbl_Clients.BirthDate) Is Not Null) AND ((Month([BirthDate]))=[Enter Month number])) OR (((tbl_Clients.SpouseBirthDate) Is Not Null) AND ((Month([spousebirthdate]))=[Enter Month number]));

Then on your report just put in the spouse birth date/name and shrink the text boxes containing them down to a height of 0 but set the 'can grow' property to TRUE, then whenever there is a value to show it will show.

You can repeat the address information from the primary contact if needed but really since they are identical you shouldn't need to.

[rpeare]

Example back

note the CAN SHRINK of both the data fields and the section (detail) are both set to YES

ClientContact4 - Copy.zip

[dougdrex]

That seems to be working much better in terms of not asking for the parameter value multiple times. Thanks!

Is there a way to fix it so that only the people with birthdays in the month provided in the "Parameter Value" show up on the report? As of right now, if the client or spouse have a birthday in the specified month, they will both show up in the report (as well as the label print). Is that something that is able to be corrected?

Thanks!
Doug

[rpeare]

yes it is, you can create a union query with the exact same fields (address related) but you'd swap out the 'main' person's name and birth date for the spouse's name/birth date.

[dougdrex]

I finally got it to work (with your help of course). It took me almost a day to modify the query so that it would work, but it's finally done!!

rpeare, thanks so much for your help. I'm a self-proclaimed novice at Access, but I'm trying to learn all I can from experts like yourself. Thank you so much for your help. I appreciate it more than you know!