Display different subform depending upon which control is clicked

[krowe]

I am creating a database to manage multiple mobile devices. The table structure is complete and I'm trying to build a set of forms.

Simplifying things, I have tblUser, tblSim and tblDevice. I also have a tblJoin where you can select a UserID, SimID and DeviceID to create an item of inventory.

I have created a parent form (frmCreateInventory) base on tblJoin, so it has JoinID and combo boxes for UserID, SimID and DeviceID in the lefthand side.

I have also set up 3 sub forms sfrmUser, sfrmDevice and sfrmSim, based on their respective tables, which I want to display on the right hand side of frmCreateInventory.

I'd like to be able to create a subform control in frmCreateInventory, but change the subform it displays based on what combo box is clicked, e.g. if I click the UserID control, the subform displays sfrmUser, and the relevant data based on the UserID that is linked to the JoinID. if I click the DeviceID control, the sfrmDevice is displayed and the relevant info etc. etc.

Is this possible? if so, how would I go about doing this?

Thanks

Kev

 

[vbaInet]

You change the SourceObject of the subform control.

 

[krowe]

Ok, so I have re-named the control DynamicSubform.

I can see the Source Object, but I expect Link Master Fields and Link Child Fields will also need changing.

My vba is not great, but I'm guessing i'll need the on click event of my User combo box to be something like:

me.DynamicSubform.SourceObject = "sfrmUser"
me.DynamicSubform.LinkMasterFields = "User"
me.DynamicSubform.LinkChildFields = "UserID"

am I close?

Thanks

kev

 

[krowe]

I've just tried this and it seems to work.... (put it on Focus in the end, on click didn't work)

it looks a little bit too simple, have I missed anything? what are the pitfalls?

Kev

 

[vbaInet]

On Click should work.

The main pitfall is that you're constantly reloading the dataset from the source.
So why do you need to do this? Are you trying to restrict users from seeing certain forms?

 

[krowe]

I just thought it would be good a good user experience if it was dynamic like that, the alternative would be to show all the subforms at the same time (maybe in tabs), but I liked the idea of clicking the box to see the related data. Should I do tabs and have the on click changing the tab that is displayed????

it will have less than 1000 records in the tblJoin, so I'm not overly concerned about performance issues.

 

[vbaInet]

Yep that's better. If you still want to make it look dynamic you can have the subforms overlapping each other, and all you do is change Visible property to suit.

That would depend on how many users will be using it and how many times each user is clicking that button at a given time. Stress test!