How to display unique choice on continuous forms

[Matthew]

Hello,

We have a contact database and I'm having a hard time displaying a person's
preferred address. Here's our setup:

tblPeople
*PersonID
PersonName

tblCompanies
*CompanyID
CompanyName
CompanyAddress

tblAffiliations
*AffiliationID
CompanyID
PersonID
AffiliationAddress
UseAffiliationAddress

This structure allows a person to work at a company yet maintain a separate
work address. When UseAffiliationAddress is checked, then their affiliation
address, not the company's address, is their preferred mailing address.

I have a form for People (frmPeople), with a subform showing a person's
affiliations (sbfrmAffiliations). On this subform, I would like to display
either the company address or the affiliation address, depending on whether
UseAffiliationAddress is checked.

My approach is to have both addresses, but only make the selected one
visible. The problem is that if a person has more than one affiliated
company (sbfrmAffiliations is set to Continuous Forms), when I check
UseAffiliationAddress for one record, all of that person's affiliated
records respond the same.

For example, say a person works for ACME, but also works for KAOS. They're
at ACME's main address, but correspondance to them for KAOS should go to
their PO BOX address.

How can I, with continuous forms, have the proper address appear for each
affiliation? If I check UseAffiliationAddress for one, both of them
respond. If I uncheck it, I only see both company addresses.

Does this make sense? I must be looking at this wrong...

Thanks!

Matthew

 

[Guest]

hi
i don't know the complex nature of your application, but the simples things
to do
in order to avoid such is to modify the tblAffiliations table and add
another field called 'PresentStatus' or so. so that you can use that field to
indicate which address is current.

hope it help

wunti

 

[Guest]

How can I, with continuous forms, have the proper address appear for each
affiliation? If I check UseAffiliationAddress for one, both of them
respond. If I uncheck it, I only see both company addresses.

Doublecheck that the form checkbox is in fact bound to the
UseAffiliationAddress field in the table. The fact that it's showing up for
all records suggests that it may be unbound.

If that's not it please post the SQL view of the subform's recordsource query.

 

[Matthew]

The checkbox on the subform is indeed bound to UseAffiliationAddress. The
subform's record source is as follows:

SELECT tblAffiliations.AffiliationID, tblCompanies.CompanyName,
tblAffiliations.CompanyID, tblAffiliations.PersonID,
tblAffiliations.AffiliationEmail, tblAffiliations.AffiliationTel,
tblAffiliations.AffAddress1, tblCompanies.CompanyAddress1,
tblAffiliations.UseAffiliationAddress
FROM tblCompanies INNER JOIN tblAffiliations ON tblCompanies.CompanyID =
tblAffiliations.CompanyID;

Just to clarify, when I said that it's showing up for all records I meant
that, for a record in frmPeople that has more than one record in
sbfrmAffiliations, whatever behavior I make dependent on
UseAffiliationAddress being checked, that behavior happens on all that
Person's affiliations.

JUst to test it out, I put a text box in sbfrmAffiliations. In the sbfrm's
OnCurrent Event (or in the textbox's AfterUpdate), I make the text box's
text read either "true" or "false". Checking or unchecking
UseAffiliationAddress changes the textbox between "true" and "false". But
when there's two (or more) records in the subform, checking/unchecking
UseAffiliationAddress affects the textbox in both (all) of that person's
affiliated company subforms.

Say Alice works for Company1 and Company2. If I check UseAffiliationAddress
in the subform for Company1, the textbox in that subform now reads "true",
but so does the text box in the subform for Company2, even though Company2's
UseAffiliationAddress is NOT checked.

I hope that's clear. I feel like the code's doing what it's supposed to;
it's my brain that's not thinking clearly.

Thanks a bunch!!

Matthew

 

[Matthew]

Thank you Wunti,

That is exactly what I've been trying to do, with the checkbox field
UseAffiliationAddress. When that is checked, I want to display the
affiliation address. When it's not, I want to display the company address.
My problem is, if there are two or more records in this continuous forms
subform, when i change the checkbox in one, it affects what gets displayed
in all the others too.

Thanks again!

Matthew

 

[Matthew]

I figured it out:

Instead of having the form react to the choice, I put the following field
into the query:

ChosenAddress:
IIf([UseAffiliationAddress]=0,[SmartCompanyAddress],[SmartAffAddress])

And then I put a text box bound to that field on the subform. Voila!!

Thanks all for your help!

Matthew