conditionally visible subforms?

[David Newmarch]

I need to accommodate various many-to-many relationships between clients in
my tblClients table. I have two junction tables that enable this.

The first, tblCategoriesLink, enables clients to be assigned to various
client categories listed in the Categories field of the tblClientCategories
table (could be more than one category for a single client) . If one of the
categories for a client is "agent" then that client could have other clients
as subclients, provided that one of the categories these other clients have
been assigned to is "subclient". (Like each client wears various badges, and
those badges permit certain relationships between them - relationships which
can also change from time to time.) A second junction table, tblAgencyLink,
permits these agent/subclient relationships to be set, along with the date
the link is made. Subclients sometimes shift from one agent to another, and
the date is a way to keep track of this.

On my form for viewing and entering client particulars I have a subform
(sfrCategories - database view) for selecting the categories the client is
assigned to. If one of those categories is "agent'' then I want a second
subform (sfrSubclients - not nested) to be visible, where subclients can be
viewed or entered for that "agent" client. Correspondingly, if the client's
categories include "subclient" then I want a different subform (sfrAgencies)
to be visible, where that subclient's agent can be viewed (or entered). How
do I get the second or third subforms to display conditionally on the "agent"
or "subclient" value being present in the sfrCategories subform? And if
neither "agent" nor "subclient" is listed for that client, neither of the
additional subforms must be visible.

If I can get this to work, it seems like a neat, simple solution, but is it
feasible? Have very limited VBA skills, but I'll stretch them as far as I can.

 

[Larry Daugherty]

You may need to go further in describing the real world application
you are trying to develop. You should also extend/clarify the logic
you are trying to resolve. That is usually best done by laying out
your equations in English (or your native tongue if it isn't English).

For example:

are the categories mutually exclusive or can one be a client and an
agent and a subclient all at the same time.

Can a subclient simultaneously have more than one agent? If so, how
many?

Is there any limit on the number of subclients?

If you post back with the complete description and the answers to the
above questions, someone will pick up the thread and help you.

HTH

 

[David Newmarch]

You may need to go further in describing the real world application
you are trying to develop.

This is a database for a small journal that I manage. The "clients" here
embrace all the people or entities we communicate with: direct subscribers,
subscription agents, their subclients, non-subscriber sales customers,
booksellers, authors, editors, reviewers, pagesetters, printers, applicants
for reproduction rights. . .

are the categories mutually exclusive?

The same "client" can at various times, and simultaneously, be a subscriber,
a reviewer, a some kind of contributor, and purchase extra copies. Agencies'
subclients can also be direct purchasers of back-issue sets. A direct
subscriber can switch to subscribing through an agency, or vice versa. And
subclients who are entered through an agent do also sometimes have direct
communications with the journal. In practice, agents never occupy any of the
other roles. Likewise printers stay just printers, I guess.

can one be a client and an agent and a subclient all at the same time?

The categories that must be mutually exclusive are that subclients cannot
simultaneously be agents.

Can a subclient simultaneously have more than one agent? If so, how many?

Yes, this can happen. Some subclients are signed up through a commercial
agent even though they also get copies through another non-commercial agency.
And there can be an overlap period when subcribers switch between agencies,
and we haven't yet been notified that they have left the first agent - or
that they have ceased to be direct subscribers.

Is there any limit on the number of subclients?

No limit - we need all we can get!

If you post back with the complete description and the answers to the
above questions, someone will pick up the thread and help you.

I really do hope so! But thanks very much meantime. Your questions were
right on target.

 

[Larry Daugherty]

Thanks for the clarification. I don't believe I grasp it all but I
know a bit more than before.

You're still quite a ways from having to sweat VBA.

I believe you may have to re-think your schema and strive to resolve
the entities really in play in your application. Usually there is a
differentiation between Customers and Contacts. My friend Ray
Marchand owned and operated a home heating oil business and a service
station. I did some applications for his companies. My customers
were Ray Marchand Oil Company and Ray Marchand Service Station. My
contact for both of them was Ray Marchand. I think you already treat
brokers and individual subscribers as entities at the same level. I
don't know how you deal with the contacts at a broker company; maybe
in a note field

I can give you some guidance that will ease the many-to-many-to-many
... burden somewhat: Every attribute that is not mutually exclusive
with the value of any other attribute must be tracked in a single
field in your tblClient. For example a client is an agent or is not.
That is true or false irrespective of anything else going on. Other
attributes that get their own field in tblClient: Client, Contributor,
Editor, etc. On the other hand you can keep a simple tblClient form
frmClient with a subform (and table) for ClientRoles. tblClientRole
would be a junction table and would have an entry for each role played
by this Client with fields/controls for Start and End dates and a note
field for a particular role.

This is where things will get a little complicated and you'll
eventually get to write a line or two of VBA.

The design of the subform should be such that it is only about twice
the height of your standard text box. The objective of the design is
to be able to show several roles in the subform control on your main
form. You'll choose Continuous forms rather than datasheet even
though you want it to look very much like a datasheet.

There will be a combobox, cboRole, based on tblRole that will be used
to select the role you're entering.

There will be some controls that are only visible when the role you're
entering is SubSub (or what ever name you're going to use). I'd
probably use something like "Agent" or "Broker" with the sense of
Agent is: or Broker is:. You will create a combobox with a query on
tblClient that you'll use to find the agent who represents this
subscriber/subclient.. You will also create a normally invisible
textbox to hold the Agent/Company name. The combobox will end up with
the record ID of the agent. So, tblClientRole will have fields for
ClientRoleID, ClientID, AgentID (that's actually an alias for ClientID
in the Agent's record in tblClient), Role, StartDate, EndDate, Note.
If the role is anything other than Agent then cboAgent and txtAgent
should be invisible.

The VBA to make things visible or invisible is
Me!Mycontrol.visible=True. However, your life won't be that easy
because you'll be referencing the controls on the subform. Look it up
in Help "Referring to controls on a subfomr" or something like that.
I believe that Keri Hardwick contributed a paper on the issue on
www.mvps.org/access Poke around the site while you're there. It's
one of the best resources going for Access developers. If not there
then you might check out support.microsoft.com. And, of course, you
can try here again with that specific issue if all else fails.

Since you designed the cboAgent and txtAgent to be invisible ... In
the OnLoad event of the subform put the line of code
If ......txtRole = "Agent" then
......cboAgent.visible=true
......txtAgent.visible-true
Else
......cboAgent.visible=false
......txtAgent.visible-false
End If


on the AfterUpdate event of cboRole put the same code - just copy and
paste it. Note that you'll want to have twice the lines of code
because you'll need to change the visibility of the labels too.

The other thing is that, given the above assumptions, you'll need a
lookup table that lists every role that can exist in your application.

The above can be a load. I tried to make things clear but I've stayed
up longer than I should. The sun will be up pretty soon!

HTH

 

[David Newmarch]

The above can be a load. I tried to make things clear but I've stayed
up longer than I should. The sun will be up pretty soon!

Hi there Larry, and many thanks.

It's evening here and I've just got back to your message at the end of my
own day - looks like we're about a world apart in timezones!

Your code for conditionally hiding/displaying controls is what I have been
hunting for and I'm now going to experiment with it, while I go on digesting
your other comments.

You've understood the tangle that I'm finding most difficult to unravel (me
and at least one friend who's more Access-skilled than I am) - which is that
(a) agents/brokers are a radically distinct class of direct client, but for
various transactions we deal with them - billing them and being paid by them
- on the same level as we do with individual direct clients; yet at the same
time (b) for other transactions we deal with agency subscribers and our own
direct subscribers on a single level - like recording who is currently
subscribed for each issue and who gets our mailings.

My first attempt to handle this was to try and avoid many-to-many
relationships by instead distinguishing between Primary clients (brokers) and
Secondary clients (subclients) and then make every "direct" client also an
agent/broker representing *himself/herself* as his/her own subclient. But
that really didn't seem to work, so I thought more about junction tables and
figured they were a more rational and flexible option.

I did have a Yes/No check box for "agent" status, then dropped it because i
thought it was redundant, but you're convincing me to put it back.

My idea for the form design was to have just a single form from which you
could quickly see *either* which agent/broker a subscriber client might be
signed up with (sometimes none, sometimes more than one) *or*, if the client
was an agent/broker, then a list of the subclients they were representing.

Got about as far as I can go for today, but I look forward to trying out
your code tomorrow. And I think it's also time to find a good beginners book
on VBA.

I really appreciate the thought you have given to this.

David