Making textbox source depend on listbox selection (or option group)

[Matthew]

Hello,

I have a contact database tracking people, companies, and their
affiliations. An address may be stored in any of three tables, tblPeople,
tblCompanies and tblAffiliations.

tblAffiliations has a field where one can select which address is that
person's main address. If MailngAddress = 1, I want the mailing address to
be their company address, 2 = affiliation address, and 3 = home address.

In my frmPeople there's a sbfrmAffiliations. I have a list box to select
main address as Company, Affiliation or Home.

I can get it so that the proper address appears, concatenated, in an unbound
textbox on the subform, by doing something like this:

if me.lbSelect = 1 then
me.txtMailingAddress.ControlSource = "CompanyAddress"
end if

if me.lbSelect = 2....and so on..

The problem is, the sbfrmAffiliations is set to show Continuous Forms. If a
person has more than one affiliation, the listbox selection affects each of
those records.

My objective is for the user to select which address should be this
affiliation's mailing address, then see the concatenated address displayed
right there.

I'm not completely confident in my structure - I had heard something about
keeping all addresses in their own table, but that seemed more difficult to
me.

Any suggestions would be most welcome!

Thanks,

Matthew

 

[Guest]

"I'm not completely confident in my structure - I had heard something about
keeping all addresses in their own table, but that seemed more difficult to
me."

So they should be!

Difficulty is not important. Getting the design correct is more beneficial
in the long run.

 

[Matthew]

Okay, I've rethought the design, and I am now more confident in this
structure. It might not seem efficient to store addresses in each of
tblPeople, tblCompanies and tblAffiliations, but it serves my client's need.
Thanks for the advice, though.

I am still stumpted on how to affect a textbox's display using a listbox in
a subform, set to continuous forms, and have the setting only affect that
particular record. As described previously..

Thanks!

Matthew

 

[Guest]

The thing with continuous forms is that it does affect every record.

1. So what tables and relationships do you have now?
2. I don't quite understand how you have set up your forms.

 

[Matthew]

tblCompanies
*CompanyID
CompanyName
CompanyAddress

tblPeople
*PersonID
HomeAddress

tblAffiliations
*AffiliationID
CompanyID
PersonID
AffiliationAddress
MailingAddress (1=Company, 2= Affiliation, 3=Home)

frmPeople
Record Source = qryPeople

sbfrmAffiliations
Record Source = qryAffiliations

qryPeople and qryAffiliations are essentially tblPeople and tblAffiliations,
with concatenated address fields added.

On sbfrmAffiliations, I want to be able to select, using an option group or
a listbox, which address to use as the main mailing address for this
particular affiliation. When that selection is made, I want the user to see
a concatenated version of their selection.

Thanks for your help!!

Matthew

 

[Guest]

I think I see a flaw in your dastardly design! From that design it seems you
have a many-to-many relationship between companies and people. Can a person
belong to more than one company? Who are you referring to?

 

[Matthew]

Yes, that is by design! We want a many-to-many relationship between
companies and people. One person can have a connection to multiple
companies and one company can have multiple people.

We have instances where one person represents more than one company. We
also want to keep track of a person's job history. I also use this model to
track a person's event attendance. If they attended an event of ours, years
ago while working at a different job, we want to see who they represented at
that time.

Thanks!

Matthew

 

[Guest]

I am wondering whether it might be better to put the mailing address in with
tblepeople since it is unique information to that person.

If a person has a company as their mailing address then you need a location
to store their current company address.

What is "affiliation" ??

I can now see why you are having a problem and it does require some further
thought!

I hope this helps.

 

[Matthew]

My intention was as follows:

tblPeople holds people and their home addresses.
tblCompanies holds companies and their company addresses.
tblAffiliations keeps track of a person working at a company, and has their
affliation address.

To illustrate this model, consider Bill Gates. He works at Microsoft.
Microsoft probably has a global headquarters, at an address different from
Gates' office. Gates also works for the Gates Foundation, another company
at another location.

In tblCompanies, there's a record for Microsoft, with its main headquarters
as an address. And there's a record for the Gates Foundation.

In tblPeople, there's one record for Bill Gates.

In tblAffiliations, there would be two records. One record would have Bill
working at Microsoft, with his specific office location, phone, fax, email,
etc. The other record would have Bill's address at the Gates Foundation.

Now suppose that Bill likes to get his Microsoft-related mail sent to his MS
office, but he wants his Gates Foundation mail sent directly to his home.
In tblAffiliations, in the Gates-Microsoft record, we'd specify to send all
mail to this record's affiliation address. But suppose he doesn't go in to
his Gates Foundation office frequently, and prefers to receive that mail at
home. In his record in tblAffiliations affiliating him with Gates Found.,
we'd specify to use his home address as mailing address.

Now suppose I have a form for people, with a subform for Affiliations.
Looking at Bill Gates' record. Two affiliations there, in continuous
subforms. First one, Microsoft, says please send mail to that affiliation
address. Second one, Gates Found, says please send mail to his home
address.

I'd like to be able to see what those addresses are, in handy-dandy
concatenated formats. I can't seem use a control because in every instance
of the form in continuous forms, the control does the same thing.

I made three text boxes, all not visible. Depending on which option was
checked (company, affiliation or home) the right text box became visible,
displaying the appropriate address. However, that same text box was the
only one visible on the next record too.

That's why I'm stumpted!

Any thoughts are most welcome!!

Thanks!!

Matthew

 

[Guest]

Sorry I haven't responded lately, I have been busy with other things. How
are you getting on?