Adding "All" to a combo box

[rzielins]

I have a User Interface form for a user to select an author to find all books written by an author. The combo box, cboAuthor, is unbound on the User Interface form but sources data using the following SQL:

SELECT tblPersons.AuthorID, tblPersons.AuthorName FROM tblPersons;

A report is then run based on a query that uses the value from cboAuthor to select the books for this author. This runs fine except when I want the user to be able to run the report for ALL authors.

Prob 1: How do allow the User to select "All" when "All" is not tblPersons
Prob 2: I want to be able to have the one query that the report runs off so I cannot simply remove the WHERE clause for the Author field when "All" is selected. Part of the WHERE clause in the query is:
WHERE ...((tblAbstracts.PrimaryAuthorID)=[Forms]![frmQueryData]![cboAuthor])

In lay terms I need the WHERE clause to read:
WHERE ((tblAbstracts.PrimaryAuthorID)= ALL AUTHORS

 

[Fornatian]

A workaround for this without getting into union queries is to add a dummy record in your authors table with ALL AUTHORS as the author name.

In the code which runs the report add an if statement something like:

if Me.MyListBox = "ALL AUTHORS" then
Docmd.OpenReport "AllAuthorsReport"
else
Docmd.OpenReport "SpecificAuthorReport"
end if

Ian

 

[Pat Hartman]

Change the Where clause to:

WHERE ...((tblAbstracts.PrimaryAuthorID)= [Forms]![frmQueryData]![cboAuthor]) OR ([Forms]![frmQueryData]![cboAuthor] Is Null);

 

[rzielins]

Thanks, Pat. All worked well with the additional "OR" query.