Combo Box For Queries

[jesusoneez]

Hi,

I've found some similar issues by searching around here but none have been quite the same as my issue.

I have a form that display PC details. It's not set up in any special way and allows additions/edit etc. It refers to one table with all the PC details in it (tblDesktops). We're categorising the PC's by room, so I created a table (tblRooms) and created a field in tblDesktops that looks up the RoomID field in tblRooms.

I've created a combo box on the form that lists the rooms in tblRooms. I want to be able to select a room from the combo box and have it display all the PC's in that room on the current form.

What I've tried;

I created a query called qryDesktops that queries all the desktops. I set the query to look at the combo box so it know what room to look for using;

[Forms]![frmDesktops]![cmbRooms]

I went back to the form and changed its record source to qryDesktops.

From here I'm a little stuck. I run the form, select a room from combo box and obviously nothing happens. I basically don't know how to get combo box to run the query, then refresh the form to show the PC's in the room selected from the combo box.

Note that I also categorise by two other "things" excluding Rooms. There's also tblCategory and tblDepartment which I'd also like combo boxes for search purposes on the form. I'm guessing I'd need three queries for this...qryDesktopsByRoom, qryDesktopsByCategory and qryDesktopsByDepartment. In this case, I'm guessing that clicking on the related combo box would also need to change the forms record source to the needed query? That's a bit further down the line anyway...I'd be happy if it just worked with the rooms combo box at the moment!

Any help much appreciated.

Steve Swallow

 

[mcirvine]

Hi,

make a on change event for your combobox. And add the code there to refresh your form.

In the code below I refresh a form field when i change the value in the combobox called cboCcSelect. Each time the user selects another item in the combobox it executes the on change event to refresh the description field.

Private Sub cboCcSelect_Change()
    Me.DESCR_NL.Requery
End Sub

 

[jesusoneez]

McIrvine, thanks for the quick reply, but I've just this minute solved the issue (or done it a different way). What I ended up doing was creating a form with three combo boxes, referring to those combo boxes in the query and setting the record source of my form to the query. Hitting a "Search" button on my combo form simply opens the form with my data displayed.

I think having done that and with what you've given me, I can probably do it how I originally wanted it, but after looking at it, my form will look very cluttered, so I might stick with the custom parameter form...

...thanks.

Steve Swallow