Report prompts for value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to try and use one report which will run on different queries.
Basically I have Exhibitors who have shown an interest in a "section" and I
now want to send out a reminder letter to those who have not yet entered.

So the query looks a bit like this:
SELECT [CurrentExhibitor&Section].Surname,
[CurrentExhibitor&Section].FirstName, [CurrentExhibitor&Section].SectionID,
SheepEntries.SheepEntryID
FROM [CurrentExhibitor&Section] LEFT JOIN SheepEntries ON
[CurrentExhibitor&Section].[Exhibitor ID] = SheepEntries.[Exhibitor ID]
WHERE ((([CurrentExhibitor&Section].SectionID)=21) AND
((SheepEntries.SheepEntryID) Is Null))

I have based the report on the CurrentExhibitor&Section query and so when I
try to run it (from a button on a menu) using:

DoCmd.OpenReport stDocName, acViewPreview, "psheepnoentriesqry", ,
acNormalPreview

I get a prompt message "Enter Parameter value" asking for the
SheepEntries.SheepEntryID - obviously this value is not on the report and is
only needed for the query.

If I just click on OK then the report runs fine - but obviously this is not
good.

Can anyone tell me either 1)how to supress the message / automatically
capture it and reply "OK"

or 2) prevent it from occuring without making a separte copy of the report
for each section?

Many thanks (and sorry for the long message!)

Helen
 
You probably have a control bound to SheepEntries.SheepEntryID or used it in
the sorting and grouping. Change the expression to only "SheepEntryID"
 
You probably have a control bound to SheepEntries.SheepEntryID or used it in
the sorting and grouping. Change the expression to only "SheepEntryID"
Click to expand...

A good thought Duane, however this isn't the problem, the report has no
grouping - it is a reminder letter to each exhibitor and only includes
information from the Exhibitors table and I can open it directly with no
problem - except it sends out a letter to every exhibitor!!

Let me try and explain it another way - I need to check the "XXXEntries"
table to check if there are any records - this shows that the exhibitor has
not entered the section.
I can do this equally by using

Where "XXXEntries.ExhibitorID" is Null

(change the XXX for each of the sections eg sheepEntries, pigEntries etc
- ExhibitorID is in the XXXEntries as a foreign key) - but I still get the
same parameter promt because the report is based only on the Exhibitors table
and not on the individual entry tables (I have about 23 different sections).

I can get round it by replicating the report for each section and basing the
report on the individual queries, but I was trying to be "clever"

Any other thoughts??

Helen
 
Why do you include the field in the SELECTed field list if you don't need it
in the report.
What is your code that opens the report? What is the Filter property of the
report? Why do you have different animals in different tables or queries?
 
Why do you include the field in the SELECTed field list if you don't need it
in the report.
Click to expand...
You're right it doesn't need to be in the selected field list, (it was in
there originally but I have since removed it) but I need to include one
field from the entries table to check that an Exhibitor doesn't have an
entry, using a left join and an "Is Null" - originally I used the key from
the Entries table ie EntriesID - I then changed it to the ExhibitorID in the
entries table.
What is your code that opens the report? What is the Filter property of the
report?
Click to expand...

stDocName = "ExhibitorsNoEntriesltr"
DoCmd.OpenReport stDocName, acViewPreview, "sheepnoentriesqry", ,
acNormalPreview

the "sheepnoentriesqry" is essentially where we started :-)
Why do you have different animals in different tables or queries?
Click to expand...

Because there is different information that I need to record for different
entries eg some need details about their dates of birth, some about milking
information etc etc and whilst this may not be the most "normalised" way of
doing it it does (mostly!) make it a lot easier to design - the idea of
haveing all the information in one enourmous table was just a bit too mind
boggling for me - (the guy that attempted this last year put each section in
a different database!!!!)

This is really the first time that I have come across this type of problem
because each of the Sections tends to work rather independently - and of
course I now realise that *perhaps* it would have been possible - but not
this year (a lot of the entries for this year have already been input)

I guess I am just a bit stumped because I can effectively produce a subset
of records, but I can't then seem to use that in a report without Access
refering back to how the set was obtained.

Would it work any better using something like OpenRecordset - or will Access
still want the "missing field" in the data for the report?

Many thanks
 
I am still confused. Is "sheepnoentriesqry" a query or a report. If it is
really a report, you should find a naming convention that names it something
like "rptsheepnoentries".

I would probably create a union query from SheepEntries, PigEntries,
CowEntries, MonkeyEntries,... You could then join this to your
CurrentExhibitor&Section query to build your report.
 
SheepNoEntriesqry is the query that finds Exhibitors, that have shown an
interest inthe Sheep section but have not yet made an entry - so have no
record in the SheepEntriestbl.

Anyhow many thanks for your help - I may have a go at creating the union
that you suggest - but this would mean there would be about 24 tables joined
- yikes!!

I think I will go back to having 23 separate reports - at least then there
is the option of making them more specific to the individual sections
 
Back
Top