Pass data from a Form to a Report using a Query

[Aeolus]

First let me say I am new to this Forum and an inexperienced Access User, mainly having sets of tables with simple Queries and Reports.
With Time on my hands I decided to dive into reports but I now have hit a brick wall.
I have a database of my book library and want to simplify getting data specific to an author using Access form design features; PLEASE no VB, I'm not that good!
The situation is:



F_Select An Author
Combo_Select An Author (Unbound)
Row Source Criteria
SELECT T_Author.ID, T_Author.AUTHOR, T_Author.[AUTHOR] FROM T_Author ORDER BY T_Author.[AUTHOR];

Command Button
On Click Criteria
Open Report (R_ Select An Author From Q_Author From F_Select An Author, Print Preview,,, Normal)


R_ Select An Author From Q_Author From F_Select an Author
Record Source Criteria
Q_Author From F_Select An Author


Q_Author From F_Select An Author
Criteria for Author from T_Author
[Forms]![F_Select An Author]![Combo_Select An Author]

If I run the Query and enter “Author” Manually all’s well.
If I run the Report and enter “Author” Manually all’s well.
If I open the Form, Select an “Author” from the Combo Box and run the Report using the Command Button I get an empty Report.
It seems that “Author” is not being passed correctly, if at all, from the Combo Box to the Query via the Report.


Any & all help very, very gratefully anticipated

[pbaldy]

I wonder if the bound column of the combo is an ID field and the report is looking for the name?

[Aeolus]

John Gault was an American entrepreneur and inventor who created the encased postage stamp.

Thanks Paul. I thought that it would be something like a field mismatch.
Given that the report/Query combination works, albeit by manually entering Author, it has to be something between the Form Combo and what's passed to the Query.
I don't really know what I am doing but I will try working on the Combo Row Source and Bound Columns until I hit lucky.
Good job I'm not trying my hand at Nuclear Reactor design!
Watch this space; But don't hold your breath.

[pbaldy]

If you don't sort it, can you post the db here?

Wrong John Galt.

[Xipooo]

If you don't sort it, can you post the db here?

Wrong John Galt.

*me.shrugs*

[Aeolus]

Paul:
John Galt is a character in Ayn Rand's novel Atlas Shrugged (1957)? If not I give up.
OK I'm stuck. By post database do you mean upload? Tried this (5.5 Mb) and "Upload Failed" twice.
Regards.

[pbaldy]

That's the Galt! The phrase comes from the book.

Try doing a compact repair, then zip and see if it will attach.

[Aeolus]

Thanks for the Compact Database and Zip tip, I didn’t realise how effective this could be. Attached.

Still not got my head around it though.

This is the logic I am trying to achieve though I have seen one where the Report drives the process.

Open Form, Select Author in Combo, call report via CmdB which runs Query and shows report in PP

Running the Query ‘standalone’ manually asks ‘Enter The Parameter Value’ Forms!F_$electAnAuthor!FindAuthor and then produces the correct list in Query Datasheet form but only for “clancy, tom” who I have been using as a test. Seems stuck with him as any other author’s lists are empty. So not only am I not passing Author I am not clearing something either

Running the Report ‘standalone’ manually asks ‘Enter The Parameter Value’ Forms!F_$electAnAuthor!FindAuthor and then produces the correct list in Report form but only for “clancy, tom”…...
Running the Form, selecting an author in the Combo Box and using the Command Button to open the Report in Print Preview results in an empty Report.
Obviously my logic is wrong and Tom Clancy is, as usual, giving grief.

I really am grateful that you are willing to help a complete Access novice (idiot) like me and look forward to any response.

Hope I'm not too early for Nevada and attachment gets deleted!

[June7]

Since you are saving the Author name as foreign key in T_AllBookData, it should be the primary key in T_Author. The ID field is not used for anything. No need for it in the combobox RowSource.

I don't use dynamic parameterized queries. I prefer using WHERE CONDITION argument of OpenReport (or OpenForm). I couldn't get the embedded macro to work so changed to VBA.

Private Sub OpenReport_Click()
DoCmd.OpenReport "R_$electAnAuthor", acViewPreview, , "Author='" & Me.FindAuthor & "'"
End Sub

Went back and re-entered the parameter in query and rebuilt the embedded macro. It worked. Didn't take a close look at the parameter in query before removed, but must have been something wrong with syntax.

Should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Why $ for S in object names?

[Aeolus]

Dear June7

Just to prove I'm new to this F can you tell me how to download the updated database zip file?

I can then check it out and work my way through your logic; which lost me round about "I don't use dynamic parameterized queries...."
But, I'll learn. All I have is a limited knowledge of Access objects, enough to put one brick on another but not enough to cement them together. My VB and it's use is even poorer.

$ used instead of S to put F, Q & R at the top of their respected lists. Old ASCI code trick used in punch tape coding; told you I was an antique.

Knew about avoiding spaces and special characters/punctuation, just forgot; it happens to us wrinklies!

Once it's sorted I'll close the thread as solved, but I would also like to rate the response from Pbaldy & Yourself if this is a function of this forum.

Thanks Again to all.

[June7]

I did not attach an updated file.

I downloaded the second posted file and the report runs just fine. So I went back and downloaded the first posted file and it also runs fine now. No edits to either file.

Now I'm not sure what was happening before.


Can give Rep with the 6-point start icon at bottom of post.

[Aeolus]

I did not attach an updated file.

I downloaded the second posted file and the report runs just fine. So I went back and downloaded the first posted file and it also runs fine now. No edits to either file.

Now I'm not sure what was happening before.

Can give Rep with the 6-point start icon at bottom of post.

If the attachment is as I uploaded then it doesn't work. Running from the form/(Query or Report) only results in a blank report unless I select/(type in) Tom Clancy" who seems to stuck in the works.

This may seem stupid but I can see no way of Downloading the file or any Forum Help; would appreciate being pointed in right direction so I can get the attachment back and try it out.

-8 hour TD doesn't help

[June7]

Download what file? Yours are the only files attached in this thread. Here is what I did (again, just now).

1. downloaded file from post 10

2. opened db and opened F_SelectAnAuthor

3. selected Aldiss, Brian in the combobox

4. clicked button to open report, report shows only Aldiss, Brian records

5. closed report

6. repeated above for Asimov, Isaac - report is blank because no records in query

7. repeated above for Clancy, Tom - report shows those records

8. repeated above for Aldiss, Brian - again shows only those records


If you want all authors to show - have to change the query joins between T_Title and T_Series and T_ReportTag.

SELECT T_Author.AUTHOR, T_Title.TITLE, T_Series.SERIES, T_Title.VOLUME, T_Title.STOCK, [T_Report Tag].[Report Tag], T_Title.NOTES
FROM T_Author INNER JOIN (T_Series RIGHT JOIN ([T_Report Tag] RIGHT JOIN T_Title ON [T_Report Tag].ID = T_Title.ReportTag) ON T_Series.ID = T_Title.T_Series_ID) ON T_Author.ID = T_Title.T_Author_ID
WHERE (((T_Author.AUTHOR)=[Forms]![F_$electAnAuthor]![FindAuthor]))
ORDER BY T_Author.AUTHOR, T_Title.VOLUME;


What is T_AllBookData table for?

Advise no spaces in naming convention.

[Aeolus]

Good morning June7.

Thank you for staying with this thread and the idiot who started it.

T_AllBookData table is legacy data from the spread sheet I used to use before I ventured into Access many, many moons ago under W1.1 when Pontius was a Pilot.
Kept for posterity.

Did what you said and sure enough it works for selected authors e.g. Aldiss(Combo Record 2; T_Author ID 2), Clancy(Combo Record 20; T_Author ID 21), Feist(Combo Record 35; T_Author ID 36) and Fford(Combo Record 36; T_Author ID 37).
T_Author ID 9 does mot exist, presumably deleted.

That's as far as I went.

No pattern apparent so presumably by "If you want all authors to show" you mean I need to change the query joins.

Do I to put the whole of the SELECT statement under author in the Query design or do parts of it go elsewhere?

Understand about no spaces and will try to be good in the future but I'm scared stiff of changing things until I have a fully operational db to fall back on.

Or do I do it by Join Properties in which case is it Option 1, 2 or 3?

[June7]

What do you mean by "Do I to put the whole of the SELECT statement under author"?

What I show is the SQL for the query object. Open the query and click on the link lines and change the joins. Or switch to SQL View and copy/paste the SQL to replace the statement then switch back to Design View.