Popup form for cascading combo box in datasheet

[ittechguy]

I'm trying to create a popup form that will allow me to make changes to a combobox in datasheet view.



I have found very little on this online. I'm not sure what the best approach would be.

I have a customers form which shows customers information and I have a subform which shows the buildings and rooms that they are associated with. There's a combobox for building and one for room. I am filtering rooms by the building. Obviously it doesn't work in datasheet view.

I'm thinking I'll have the subform display the records and create a button to open a popup form that will allow me to either add or remove a record. I can do add, not sure how to remove.

[ranman256]

in the table field definitions,
click the LOOKUP tab
change textbox to combo box
set the data row source

[orange]

@ittechguy

Are you keeping a list of your requirements? Do you have your test scenarios and data to "test" against your model?
What exactly are you trying to do in plain English? What is the functionality you want --forget the combo and related jargon?

[ittechguy]

Orange,

Yes, I am.

I'm trying to create a way got to search by customer name and see the rooms that they are associated with. And then I need a way for me to add or remove rooms from a customer. I'm trying to put this in a subform in datasheet because it will show multiple rooms. However cascading combobox doesn't work in datasheet.

[orange]

Forget the subform and datasheet and combo no popup---that's all related to How.
The first step is WHAT!!

So in simple English (no access jargon) it seems, you are trying
- to select a specific Customer(POC)
- identify the Rooms he/she is currently associated with (see which Rooms in which Bldgs this Customer is POC or FacilityMgr/POC
-review that list with "some new list that hasn't yet been applied"(where did this new list come from??)
- need some means to ADD associated Rooms to this Customer
- need some means to DELETE associated Rooms to this Customer (you'll have to account for the Bldg of the Room also)

- so stepping back
You are reviewing Customers. You select a specific Customer based on (name, Id, Rank...??)
Since Customers are either a POC or FacilityMgr/POC for 0,1 or more Rooms in a Bldg, and since Rooms are in Bldgs, I think you have to include Bldg in your approach.

Now, not to jump too far ahead, based on a draft database I posted (that was generated form the model I posted) this is the query to identify all POCs for Bldgs/Rooms. This is the sort of info you're looking for--basically a list of Customers and the Bldg*Room they are currently identified as POC.

SELECT Customer.LastName
    ,Customer.FirstName
    ,Building.BuildingName
    ,Room.RoomName
    ,"SpecificRoomPOC" AS ROLE
FROM (
    Building INNER JOIN Room ON Building.BuildingID = Room.BuildingID
    )
INNER JOIN (
    Customer INNER JOIN POC ON Customer.CustomerID = POC.CustomerID
    ) ON (Room.RoomID = POC.RoomID)
    AND (Room.BuildingID = POC.BuildingID)

UNION

SELECT Customer.LastName
    ,Customer.FirstName
    ,Building.BuildingName
    ,Room.RoomName
    ,"BldgPOC" AS ROLE
FROM (
    Building INNER JOIN Room ON Building.BuildingID = Room.BuildingID
    )
INNER JOIN (
    Customer INNER JOIN FacilityMgr ON Customer.CustomerID = FacilityMgr.CustomerID
    ) ON Building.BuildingID = FacilityMgr.BuildingID;

The problem at the moment is that the database I posted (tables, relationships and test data) may be totally different from yours. So we can't test/compare things.

Here is a search article that you should become familiar with --just what it does.

[ittechguy]

You are correct. I need to search for a customer and view the rooms which they are associated with. I also need to see the building name and the information about each room that they are associated with. That is, secoptions, cabinet name, and cabinet key.

I also need to see the buildings they are a facility manager for. I'm fine with that being in a separate subform.

I can easily make a query which shows me this information. It's almost as easy to make a search form which shows this information. The issue I'm having is creating a user friendly way to add new rooms to customers. As cascading combobox does not work in datasheet, seems the only choice I have left is to include a button which opens up a popup form and allows users to add a room to a building.

My idea is to create a simple form which includes just building name and room name. Both combo boxes. I would then filter the room name by building name.

I created a form and set it to popup and modal. I created the form based on a query of customer ID, room ID, and room name. I then added a button onto my main form and in the event tab, set onclick to open up my popup form. I added a where condition and set the customerID from my popup form to match the customer ID from my main form. All works great except that instead of it adding a room record in my Subform which displays building/room information, it changes the current record. Making for only one entry in my subform. Need to do more research to find out why...

[ittechguy]

Ok. I've done a lot of research, trial/error, and also went back and re-read what June had posted on my other post.

I created a form based on my customers to rooms junction table, not on a query. And then in the on click event, I changed the data mode to add. Now I am able to add new records using two comboboxes (one for building and one for rooms).

For some reason though, when I set the data mode to add, it doesn't have any information automatically filled out. As such the only way I can get it to work is if I manually input the customer ID. Wheras if I was in edit mode, it would have that automatically filled out because it gets it from my main form.

[ittechguy]

Figured it out!

By setting the default value of CustomerID in my popup box to [Forms]!frmMainCustomers]![CustomerID] it would display the customer ID of the customer I was currently viewing. Problem solved!

[orange]

How about posting a copy of your db as it is now? It will help with communication.
In real terms, how do you plan to populate your database?
Initial population: Buildings, Rooms,Customers...
When you talk of user friendly screens, it's important to focus on- what is known? what are you expecting?
is it clear to the user?

I was thinking about recording every POC (relating Customer,Bldg and Room)
For example: Regarding FMgr of Building, POC of specific room.
(Rooms only exist in Bldgs, so this has to be managed)
You could have a Customer profile, then below
A list of Buildings and a second list of Rooms (restricted by selection in Building)
And an option to check a box to make this Customer a FMgr for selected bBldg
if Box is checked-- you have to see if the selected Bldg has a POC assigned; is it this person;
if not, do you delete current assignment then add a record for this bldg and every room as
individual POC records; Some Bldgs may have room specific POV. All this to say there are several conditions
to be checked and considered with options.
This scenario doesn't necessarily have to be done via subforms. You could have unbounded combos and logic behind events. Also you could make a number of controls hidden, and only visible when they were needed(based on failing/passing a certain condition.
This is where the business processes (adding a FMgr, assigning Rooms to POC...) will change with the new system. So don't get too restrictive, too quickly. You may want to mock up a few screens with no real details behind them, and get potential users to review and comment. Better to get them on side and part of the "team" as soon as you can. It's great feedback, and they have played a part in design.
Good luck.