Using a combo box to open a report

[Bill Casanova]

I have books in 8 different rooms. All books are recorded in an Access database.

I have a report called ‘Books in Room?’. This is based on a query with the same name: ‘Books in room?’. In the query the criteria for ‘Room’ is ‘[Which room?]’. Consequently each time I open the report a small window pops up asking ‘Which room?’.

I have to fill in the name of the room exactly as it is written in a table called ‘Rooms’ or else I get an empty report. This works for me, but not for others who don’t know exactly how the rooms are spelled or abbreviated.

I have tried to solve this by making a form called ‘Which Room?’. It has an unbound combo box displaying the name of the rooms in the table ‘Rooms’. I want to select a room from the combo box, and then press an OK button to open the report for that room.

Similar problems have been discussed on this forum, but they make no sense to me as I have no knowledge of Visual Basic or Macro programming. This is rather frustrating. I guess there are only a few lines of codes required!



Can somebody please show me how to do it?

[pbaldy]

One line of code required (after taking the criteria out of the query):

BaldyWeb wherecondition

If you've never used code before:

http://www.baldyweb.com/FirstVBA.htm

[alansidman]

These visuals may help you:
First a search form for your query.

http://www.datapigtechnologies.com/f...earchform.html

Now combo boxes.

http://www.datapigtechnologies.com/AccessMain.htm

Scroll down to the tutorial on combo boxes.

Alan

[Bill Casanova]

I have striped my database down to the basic essentials:

· Table "Tbl Book" with 154 books
· Table "Tbl Room" with only 2 rooms.
· Query "Que Which Room" with Authors names, book titles and room name.
· Form "Frm Which Room?" with an unbound ComboBox displaying the content of "Tbl Room", and an action button titled "OK".
· Report ”Rpt Books in room?” displaying all books in both rooms.

I want to select a room from the ComboBox and press the OK button to open the report displaying only books in the selected room.

The OK button has the following code attached to it, but does not open the report:

Private Sub OK_Click()
DoCmd.OpenReport "Rpt Books in room?", , , "Room = '" & Me.OK & "'"
End Sub

I select a room and press the button, but nothing happens.
What’s wrong?

[pbaldy]

Is any code running? The database must be in a trusted location, or code must be explicitly enabled to run. Can you post the db here?

[Bill Casanova]

Is any code running? No, not as far as I know.
Trying to RUN the code in design view brings up an empty window called Macros with no macro names for this db.


The database must be in a trusted location, or code must be explicitly enabled to run. Sorry, I do not understand what this means.


Can you post the db here? I will be happy to do so, but it is 2336kB and too large to be posted as an attachment, even after deleting most of the books in Tbl Book.
I notice that zip files can be larger, but do not know how to convert it.
Is there any other way to send it to you?

[orange]

Is any code running? No, not as far as I know.
Trying to RUN the code in design view brings up an empty window called Macros with no macro names for this db.


The database must be in a trusted location, or code must be explicitly enabled to run. Sorry, I do not understand what this means.


Can you post the db here? I will be happy to do so, but it is 2336kB and too large to be posted as an attachment, even after deleting most of the books in Tbl Book.
I notice that zip files can be larger, but do not know how to convert it.
Is there any other way to send it to you?

I'm trying to follow what you are doing.

I have a table Booksx and a table Rooms.
I have a query that links Booksx and Rooms and has a prompt for [Which Room]
This query limits the output to books only in the requested Room.

I have a Form with an Unbounded Combobox, which I have called combo1, and I have supplied a value list of room numbers. The form also has a Button called OK.

The OK button has the following code attached to it, but does not open the report:

Private Sub OK_Click()
DoCmd.OpenReport "Rpt Books in room?", , , "Room = '" & Me.OK & "'"
End Sub

When you click a button, you can run an On Click event, but the button per se has no value. So Me.ok does not have any meaningful data.

I think what you were trying to do

I want to select a room from the ComboBox and press the OK button to open the report displaying only books in the selected room.

is select a value from the combobox and open a report

In my case I'm using RoomNumber, so this is the code of the On Click in my test

NOTE: The debug.print is to show what value was selected from combo1. It is a useful debugging approach.
I have also opened the report in preview mode for debugging purposes ( no sense in wasting paper)

Private Sub OK_Click()
Debug.Print "Room selected was " & Me.Combo1.Value
DoCmd.OpenReport "Rpt Books in room?", acViewPreview, , "RoomNumber = '" & Me.Combo1 & "'"
End Sub

I hope this is helpful. No one is saying that events and vba is trivial. It takes time and practice. Good luck with your project.
Also, the free videos at Datapig, as Alan has suggested, are excellent. And are great refreshers from time to time.

[pbaldy]

To make sure code is enabled, go to

Office Button/Access Options/Trust Center/Trust Center Settings/Trusted Locations

and make sure the path to your database is there.

[Bill Casanova]

To make sure code is enabled, go to

Office Button/Access Options/Trust Center/Trust Center Settings/Trusted Locations

and make sure the path to your database is there.

Thank you.
I have looked it up and find that trusted location is:
C:\Program Files (x86)\Microsoft Office\Office12\ACCWIZ\, Standard location for Access.
This has not been changed since the program was installed, so I guess this is OK?

[Bill Casanova]

I finally managed to strip my db suffichently to be able to attach it.

The db is limited to 2 tabels, 1 query, 1 form and 1 report.
The report is based on the query, showing all books in all rooms.
The form has a combobox showing all rooms and an OK button.

I want to use this form to select a room from the combobox and klick the button to open the report, showing ONLY the books in the selected room.

There must be something wrong with the codes attached to the OK button. Can you please look at it and correct it if possible?

[orange]

I don't have acc2007 so can not open the accdb.
I can look at mdb if you want to post a version in acc 2003 format.

[Bill Casanova]

Attach acc 2003 format.

[orange]

Bill,
I opened it in Acc2003 and it seems to work for me.

After making selection in combo, I clicked the button, and the report opened.

Same for the other choice in combo.

I checked the Books table and filtered by room and got same count as in reports.

All seems well.

Am I missing something??

[pbaldy]

Thank you.
I have looked it up and find that trusted location is:
C:\Program Files (x86)\Microsoft Office\Office12\ACCWIZ\, Standard location for Access.
This has not been changed since the program was installed, so I guess this is OK?

That's okay if that's the folder your database is stored in. Otherwise, code is not running. I assume that isn't where your database is stored, so that location needs to be added.

[Bill Casanova]

My Access program is stored in C:\Program Files (x86)\Microsoft Office\Office12\ACCWIZ\. I am not allowed to store my db there.

My db is stored in C:/User/Bill/My documents.

I do not understand what you mean by "..., so that location needs to be added."

I attached an Access 2003 version to Orange. It worked fine for him.

I am attaching an Access 2007 version to this mail. Perhaps you can have a look at it and see if you can preview the report showing only the room chosen from the ComboBox in the form "Which room?"

If it works for you then there must be something wrong with the way my Access 2007 is set up.