Re: using combo boxes to filter records in a subform

Tina....I hope you see this message...I followed your instructions above to
add 3 combo boxes to a header of a form with the details below. Now, when I
add one combo box...it works great...as soon as I add another combo box
nothing works...no data is returned. Why when i add more than 1 combo box
does it not bring back results? Any ideas? Or if you need more from me.
Thanks much!

"tina" wrote:

use a mainform/subform setup to display multiple child (phone) records for
one parent (contact) record. if you want to see the parent records in a
continuous or data*** format, yet still see the related child records, you
have to get a little creative. one way is to use two subforms on one main
form. the first subform is bound to the parent table. add an unbound
textbox, which i'll call txtPK, to the main form, and set its' Visible
property to False. set its' ControlSource to
=[NameOfSubformControl].[Form]![NameOfPKFieldInParentTable]
the second subform is bound to the child table. in that subform control's
LinkChildFields property, enter the name of the *foreign* key field in the
child table that links back to the parent table. in the LinkMasterFields
property, enter the name of the unbound textbox on the mainform, as
[txtPK]

now the child subform is indirectly linked to the parent subform, via the
unbound textbox control on the main form. as you move from record to record
in the parent subform, the related child records will show in the child
subform. you can still use unbound combo box controls to search the parent
records; just put the controls on the main form. in the parent subform's
RecordSource SQL statement, change the criteria to refer to the main form,
as

[Forms]![MainFormName]![cboWhatever] Or [Forms]![MainFormName]![cboWhatever]
Is Null

hth


"bluemoir" <bluemoir@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:707B98C0-2828-4111-A676-2D994199B123@xxxxxxxxxxxxxxxx
Thanks again for all that tina, I've made all the adjustments you
suggested
yet I'm still fuzzy on how I will display all the data for each contact
once
I move the phone numbers into a separate table. I assume I would use a
query
to combine the information from the different tables such as the contacts
personal info, the builder's, category's and dept's name (as opposed to
ID),
and the phone numbers then from there, base my forms off the query.

First of all is this correct? And second what happens in the event of a
contactID showing up more than once in tblContactPhones as a result of
more
than 1 phone number being assigned to that person (such as direct phone#
and
cell#)?

How will the query dispaly multiple phone#'s for the same contact if I
only
want 1 record per contact?

ty4yt

Ross
"tina" wrote:

okay, i looked at your db; i'll answer you here rather than email, so
others
may benefit.
- however, you're breaking one of the rules of normalization by putting
data
(phone types, such as "direct", "mobile", "fax", "pager") into
fieldnames,
in tContacts. recommend that you move the Contacts' phone numbers into a
separate table, as

tblContactPhones
PhoneID (pk, Autonumber)
ContactID (foreign key from tblContacts)
PhoneTypeID (foreign key from tblPhoneTypes)
PhoneNumber
Notes

tblPhoneTypes
PhoneTypeID (pk)
TypeName (direct, mobile, fax, pager, etc)

email and radio number fields can be left in tContacts.
you did the same thing with the phone numbers in tBuilders. here i might
let
it slide, (though it still breaks normalization rules) if no builder
will
ever have more than one MainLine and FaxNumber (i really hate to say
"never"
when talking about any business process...), and if most builders will
have
both.

hth




.