Re: conditionally visible subforms?

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 data*** even
though you want it to look very much like a data***.

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
--
-Larry-
--

"David Newmarch" <DavidNewmarch@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:7769A7F7-5312-4214-AAA2-01EFA848F8F6@xxxxxxxxxxxxxxxx
"Larry Daugherty" wrote:

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.


.

Quantcast