Filtered Combobox form criteria from main form in a Navigation Form

[ittechguy]

I have a filtered combobox called which displays a cabinet name and filters its values based on a textbox I have in my main form (roomsID, which I have hidden from users). To make this work I created a query for the row source of my combo box and added a roomsID field, and set the criteria for [forms]![frmMainRooms]![RoomsID]. It worked great, however when I tried to add my main form to my navigation form, it no longer worked. It asked me to enter parameter values.



I think I could get this to work perhaps if I used [forms]![Navigation Form]![frmMainRooms]![RoomsID]. However, it would then not work if I wasn't viewing it in the navigation form. And I can't simply use [RoomsID] because I'm pulling it from a different form.

How can I fix this?BuildingsSwitches3 (5).zip

[June7]

Not finding a cabinet combobox, just an unbound textbox. Also not finding a hidden roomsID textbox, there is one that is not hidden. Exactly which form and controls are involved?

[ittechguy]

If you open up my navigation form and click on the rooms tab it will open up frmMainRooms. In that form I have a subform which contains a combobox that says cabinet id. That's the combobox I'm talking about.

You can also open up my frmMainRooms form and see this.

The problem is I can link it so that it will work in navigation form, or not in navigation form... but not both.

[June7]

Okay, found it on frmSubCabinet.

Don't reference the RoomsID on the main form, reference the RoomsID on the subform.

SELECT Cabinet.CabinetID, Cabinet.CabinetName, Cabinet.RoomsID FROM Cabinet WHERE (((Cabinet.RoomsID)=[RoomsID]));

[ittechguy]

Wow! I'm stupid. Thanks.

Similar question. I'm creating popup forms to allow me to add customers to rooms and such. They're not yet in the database file I uploaded here.

There is a default value and also a where clause in my macro where I link it to the room id from my main form. I click on a button and it opens my popup form Where roomid=[forms]![FrmMainRooms]!roomid.

Obviously this doesn't work inside of a navigation form. I'd need to specify the full path and if I do that, it won't work when not in navigation form.

[June7]

I don't use macros, only VBA.

Would have a similar issue if you placed frmMainForm as a subform on a regular form.

Where is code opening popup in relation to RoomsID?

[ittechguy]

I'll show you an example tomorrow. I've done a very similar setup for my customers tab in the navigation form. (if you click on my add customers to rooms button). I just haven't set it up for rooms yet.

Basically, RoomsID is in my main form. I have a button which opens up a popup form. This form contains room ID (hidden), building name, room name, and customer's first name, last name. In the event tab for the button, on the "on click," I open up the macro builder and set it to OpenForm, and select the popup form. I set the data mode to add and under "where condition =" I type in roomid=[forms]![FrmMainRooms]![roomsid]".

At this point I can click on the button and it would open up the correct record, however the RoomsID isn't filled in. So under default value for that text box, I'd type in [forms]![FrmMainRooms]![roomsid].

The problem is this works great when using the form frmMainRooms. However if using Navigation Form, it doesn't work at all.

[June7]

If the button and RoomsID are on same form, I would do this in VBA:

DoCmd.OpenForm "formname", , , "RoomsID=" & Me.RoomsID

If you are opening form in Add mode, makes no sense to apply filter criteria.

[ittechguy]

The problem is they're not on the same form the rooms ID comes from my main form and I need it to set the default value like that because a popup form can't be a subform. I need it to act like one.

I have another idea I'm going to try though. I think I can allow a user to select a building number and room name and bound the room name to rooms Id. Then have them select a customer and bind that to customerid.