Display subforms depending on main form attibute value

[rosh]

Hi,

In my university database i have a member,faculty ,student and staff table.
I have a new member form and depending on which member type (attribute)i want to display the corresponding faculty, student or staff table.All other subform should be hidden.

Could you suggest some workaround

--Rosh

 

[MSAccessRookie]

Hi,

In my university database i have a member,faculty ,student and staff table.
I have a new member form and depending on which member type (attribute)i want to display the corresponding faculty, student or staff table.All other subform should be hidden.

Could you suggest some workaround

--Rosh

As long as all three of the categories are mutually exclusive, the Option Group might prove to be a useful tool here.

• Create an Option Group with three Options (Faculty, Student, Staff). One of the properties of an Option Group is that only one of the Options is allowed to be active at the same time.
• Create three sub-forms for your data entry and set the Visible Property of each of the Forms to False.

In the On Update Event of the code behind the Option Group, Set the Visible Property of the selected Form to True, and the Visible Property of each of the other two Forms to False (in case it was set True in a previous selection).

See if this gives you the effect that you want.

 

[SOS]

I'm going to squawk a bit on the design.

You have

member,faculty ,student and staff table.

So what is the definition of each and could faculty ever be a student, could staff ever be a student, and how about a member? (and so on...)

I'm thinking you need to look at this again and perhaps have only one, and possibly two, tables instead of 4.

 

[MSAccessRookie]

I'm going to squawk a bit on the design.

You have


So what is the definition of each and could faculty ever be a student, could staff ever be a student, and how about a member? (and so on...)

I'm thinking you need to look at this again and perhaps have only one, and possibly two, tables instead of 4.

I agree with you on this, and that is why I said that the advice applied if all three types were mutually exclusive.I also agree that with some design changes, this could be served by one or two tables. I was providing a suggestion that was based on the initial design.

 

[SOS]

I agree with you on this, and that is why I said that the advice applied if all three types were mutually exclusive.I also agree that with some design changes, this could be served by one or two tables. I was providing a suggestion that was based on the initial design.

Yep, understood. I just took the more "direct, bad cop" approach

 

[rosh]

Hi,

I am not able to set the On Update Event of the option group as you mentioned.
The option group has mutually exclusive options
How do i set subforms visible and not visible for each option separately


--Rosh

 

[SOS]

Hi,

I am not able to set the On Update Event of the option group as you mentioned.

I think you didn't go to the right place. You need to go find the Option Group FRAME's After Update event, not the options themselves.

 

[MSAccessRookie]

Hi,

I am not able to set the On Update Event of the option group as you mentioned.
The option group has mutually exclusive options
How do i set subforms visible and not visible for each option separately


--Rosh

I was wrong about the "On Update Event". Each of the Option Buttons in the Option Group in my test database had a "Got Focus Event" that was triggered when the Option was selected. Try that one.

 

[boblarson]

I was wrong about the "On Update Event". Each of the Option Buttons in the Option Group in my test database had a "Got Focus Event" that was triggered when the Option was selected. Try that one.

Nope, as SOS stated, you use the Option Group Frame's AFTER UPDATE event and you can get the value of the Option Group Frame.

 

[MSAccessRookie]

Nope, as SOS stated, you use the Option Group Frame's AFTER UPDATE event and you can get the value of the Option Group Frame.

Welcome back Bob.

I did not look at the Frame. I just got each Button to work the way I described. I thought it was an odd way to resolve the issue. I will have to go back and make it work with the Frame.

Followup:

The change to make it work with the Frame was exactly what I was looking for. As usual, Bob is 100% correct. SOS is on the mark with his reply.

 

[rosh]

Hi ,

I am still stuck.I feel i am near to answer but doing some mistake
I get this error :The expression On Got Focus you entered as event property setting produced the following error:The object you referenced in the Visual basic procedure as an OLE object isnt an OLE object.


Thanks so much for all your reply


---Rosh

 

[boblarson]

What are you doing using the Got Focus event? What code are you trying to use?

 

[rosh]

Hi,

I have 3 option box- faculty,student, staff.All mutually exclusive.
i have updated as per forum advice the following

1. On Focus in Faculty option : =[Forms]![Subform_Facuty].[Visible]
2. On Focus in Student option : =[Forms]![Subform_Student].[Visible]
3. On Focus in Staff option : =[Forms]![Staff_Facuty].[Visible]

I have put propery of the 3 subform: visible as NO

Still not able to do.Please suggest this naive person

---Rosh

 

[MSAccessRookie]

Hi,

I have 3 option box- faculty,student, staff.All mutually exclusive.
i have updated as per forum advice the following

1. On Focus in Faculty option : =[Forms]![Subform_Facuty].[Visible]
2. On Focus in Student option : =[Forms]![Subform_Student].[Visible]
3. On Focus in Staff option : =[Forms]![Staff_Facuty].[Visible]

I have put propery of the 3 subform: visible as NO

Still not able to do.Please suggest this naive person

---Rosh

As SOS pointed out in #7 above, My initial assessment (although it seemed to work) was incorrect. You do not want to activate the VB Code at the individual Option level, but instead do it in the Option Group FRAME's After Update event. The Frame is the Box that surrounds the Option Buttons.

 

[SOS]

If you need to, post the database and we'll help get it sorted.

 

[rosh]

hi,

I have put After Update on option group as
=[Forms]![F_AddNewMember]![Faculty subform].[Visible]=True

But it doesnt show subform

--Rosh

 

[boblarson]

hi,

I have put After Update on option group as
=[Forms]![F_AddNewMember]![Faculty subform].[Visible]=True

But it doesnt show subform

--Rosh

You are trying to put stuff in the event PROPERTY and not in the VBA window where it needs to go AND your code is not correct either.

I will upload a sample - give me a few moments.

 

[MSAccessRookie]

hi,

I have put After Update on option group as
=[Forms]! [F_AddNewMember] ![Faculty subform].[Visible]=True

But it doesnt show subform

--Rosh

I Assume that [F_AddNewMember] is the name of the Main Form in this example. Is [Faculty subform] the name of the SubForm, or the name of the Control on the Main Form that contains the Sub Form?

The Properties being referred to here would apply to the Control Name, not the SubForm Name (unless they were the same, which is probably not a good idea anyway).

 

[boblarson]

Here's a sample for you.

 

[rosh]

Thank you all for your patience and help.
God Bless you all !!!