How to use a query as a source for a report in MS Access 2007?

Question

I did the following in MS Access: I made a form which had a combo box and a button. You select an option from there and click on the button and it is supposed to open a report. Now, I wrote a query selecting a few fields from a table and in the where clause, gave the condition as where name=str(combo1.value) and the report source was specified as this query. Now, when I select the value and click on the button, it opens a blank report. How can I make it load only those particular values?

I am not saving the combo box value anywhere. It said that it would remember the value for later use. Am I doing the right thing by not saving it? What should I do to make this work? Please help me!

Edit: The combo box is using values from a column 1 in a table X. I've not bound the value to any field and am using the "Remember the value for later use" option provided. The combo box is essentially a list of hotels and the report is a list of people staying at the selected hotel. When I put the ID of the field (as defined in the X), it works. But the thing is, it should refer to the names of the hotels and not the ID, which I am supposed to enter in a popup that asks for it. What do I do?

Edit 2: The query is as follows:
SELECT Table_1.Hotel_Name, Table_2.Name_of_Delegate, Table_2.Address, Table_2.City, Table_2.Center, Table_2.Spouse_Present, Table_2.No_of_Children, Table_2.No_of_Guests, Table_2.No_of_Rooms FROM Table_1 INNER JOIN Table_2 ON Table_1.ID=Table_2.Hotel_of_Residence WHERE Table_1.Hotel_Name=FormName.Combo7.Text;

When I click on the button (which opens the report), it asks for the name of the hotel in a popup box. How can I avoid this? What I am doing wrong?

Answer 1

You can use a WhereCondition with the DoCmd.OpenReport Method as a "dynamic WHERE clause" for your report's record source. Use something like this in the click event of the command button which opens your report.

DoCmd.OpenReport "YourReport", , , "[name]=" & Me.combo1

Remove the WHERE clause you added, where name=str(combo1.value), from the report's query.

I surrounded name with square brackets because name is a reserved word. See Problem names and reserved words in Access

Edit: In a comment, you said this about the combo box:

"Row Source is SELECT [Table_Name].[ID], [Table_Name].[Name] FROM [Table_Name];. Bound Column is 1 (which I assume shows the names I wish to be displayed in the combobox.)"

When you refer to the value of a combo box, that value is the value of the "Bound Column". So in your case, the bound column is 1, which means the combo value will be [Table_Name].[ID]. However, you want to open your report based on the [Name] column of the combo. So change the bound column of the combo from 1 to 2.

Answer 2

To open a report using the value of your combobox, in your report query you need to do the following:

SELECT fields
FROM table
WHERE YourValue = [Form]![FormName]![ComboBox Value]

You have to specify the name of the Form, plus the value so the report query knows the value. Hope this helps.