Capture an entry based on a selection

[Allison]

I asked this in March but got no replies. Does that mean it's not possible?
Or should I provide greater detail?

Access 2003, XP Srvc Pack 3

I want to automatically classify an entry based on its selection.

Details so far:

tblContact (contains standard info about the contact)
tblContactType (an ID# field and description [classifications for the
contact such as vendor, employee, etc.])
tblComboContactType (contains the ID# for each combination of contact and
type. Example fldComboContactID, fldContactTypeID, fldContactID)
tblMaster (contains info about each company)

Usually to populate the tblComboContactType I tie the main form to the
"Contact" table, the subform to the "ComboContactType" table, and use the
"ContactType" as the row source for a combo box. This works fine.

I want to create an entry form to capture which contacts can authorize
purchases on behalf of a given company.

What I'd like to do on the form is select the company (I know how to do this
part), present a combo box with entries for each contact associated with that
company (pretty sure I can do this), then have the combination of
contact/"fact that they can sign" (this is a unique contact type called
"signer") populate into my tblComboContactType (this is the part I don't know
how to do).

Any suggestions or pointers to figure out how to do this?

Thank you for your help.

Allison

 

[Thomas [PBD]]

Allison,

I guess the best question to start with, is can a Contact have multiple
ContactTypes? If so, that would lead your ComboContactType table to be
mutlivariant. Or would it be that a Contact could only have one type?

With your description, I think I have mapped out the data tables correctly,
but correct me if I am wrong.

tblMaster: ID, Company Name, Address, ETC

tblContact: ID, Name, CompanyID, ETC (There is a one to many relationship
from tblMaster.ID to tblContact.CompanyID)

tblComboContactType: ID, TypeID, ContactID (Not sure of a relationship to
tblContact.ID from tblComboContactType.ContactID)

tblContactType: ID, Descripion (There is a one to many relationship from
tblContactType.ID to tblComboContactType.TypeID) (The Description field
contains a "Signer" Record)

A quick suggestion that I might take is to include the Type (even as a one
to many relationship from tblContactType to tblContact), so that you can
avoid having to create another table for this, and maybe additionally create
a "Yes/No" field for "Signer" in the tblContact. It seems irrelevant and
more difficult to create an additional table to link the two.

 

[Allison]

Thanks for attempting to understand me. Let me clarify.

I do not want to use yes/no boxes, because there are dozens of possible
"types". Instead, I am using Allen Browne's method:
http://allenbrowne.com/casu-23.html

The above method works great when I set up the "types" choices like he has
in his sample.

However, I want to be able to capture the same info to the combo table, but
using a unique form that I'll create for only the purpose to find out who can
authorize a purchase. I outlined the circumstances below.

You are correct for the Master and Contact tables. The other tables are as
outlined in Allen Browne's relational yes/nos page that I linked above.

Thanks Thomas. I hope you can help.

Allison

Allison,

I guess the best question to start with, is can a Contact have multiple
ContactTypes? If so, that would lead your ComboContactType table to be
mutlivariant. Or would it be that a Contact could only have one type?

With your description, I think I have mapped out the data tables correctly,
but correct me if I am wrong.

tblMaster: ID, Company Name, Address, ETC

tblContact: ID, Name, CompanyID, ETC (There is a one to many relationship
from tblMaster.ID to tblContact.CompanyID)

tblComboContactType: ID, TypeID, ContactID (Not sure of a relationship to
tblContact.ID from tblComboContactType.ContactID)

tblContactType: ID, Descripion (There is a one to many relationship from
tblContactType.ID to tblComboContactType.TypeID) (The Description field
contains a "Signer" Record)

A quick suggestion that I might take is to include the Type (even as a one
to many relationship from tblContactType to tblContact), so that you can
avoid having to create another table for this, and maybe additionally create
a "Yes/No" field for "Signer" in the tblContact. It seems irrelevant and
more difficult to create an additional table to link the two.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

I asked this in March but got no replies. Does that mean it's not possible?
Or should I provide greater detail?

Access 2003, XP Srvc Pack 3

I want to automatically classify an entry based on its selection.

Details so far:

tblContact (contains standard info about the contact)
tblContactType (an ID# field and description [classifications for the
contact such as vendor, employee, etc.])
tblComboContactType (contains the ID# for each combination of contact and
type. Example fldComboContactID, fldContactTypeID, fldContactID)
tblMaster (contains info about each company)

Usually to populate the tblComboContactType I tie the main form to the
"Contact" table, the subform to the "ComboContactType" table, and use the
"ContactType" as the row source for a combo box. This works fine.

I want to create an entry form to capture which contacts can authorize
purchases on behalf of a given company.

What I'd like to do on the form is select the company (I know how to do this
part), present a combo box with entries for each contact associated with that
company (pretty sure I can do this), then have the combination of
contact/"fact that they can sign" (this is a unique contact type called
"signer") populate into my tblComboContactType (this is the part I don't know
how to do).

Any suggestions or pointers to figure out how to do this?

Thank you for your help.

Allison

 

[Thomas [PBD]]

Allison,

There might have been some miscommunication in my feedback. I now see how
your tables are set up. I think there is only one real question that I have
left, which wasn't answered. Does the tblContactType contain a record for
the "Signer", if so it would be as simple as linking that to the Contact in
the subform.

My example to use a Yes/No was only meant for the "Signer" field, not for
every type. Can you have more than one "Signer" for each Company, there
might be some additional coding needed to clear that field from an additional
Contact at a Company if you can only have one at a time (per Company).

Could you describe (or provide a picture) of what your form does look like,
or what you would like it to look like? Visuals can do wonders in these
things. From what I can gather, its a Combobox for Company, Combobox for
Contacts (filtered by Company), and subform containing the Types for that
Contact. However, you said that there is an additional form that you wish to
create, for only the "Signers".

I am still confused a little by the wording "have the combination of
contact/'fact that they can sign' ... populate into my tblComboContactType".
You would like the ContactID and TypeID populate into the tblComboContactType
table? If so, this would be accomplished by the subform.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

Thanks for attempting to understand me. Let me clarify.

I do not want to use yes/no boxes, because there are dozens of possible
"types". Instead, I am using Allen Browne's method:
http://allenbrowne.com/casu-23.html

The above method works great when I set up the "types" choices like he has
in his sample.

However, I want to be able to capture the same info to the combo table, but
using a unique form that I'll create for only the purpose to find out who can
authorize a purchase. I outlined the circumstances below.

You are correct for the Master and Contact tables. The other tables are as
outlined in Allen Browne's relational yes/nos page that I linked above.

Thanks Thomas. I hope you can help.

Allison

Allison,

I guess the best question to start with, is can a Contact have multiple
ContactTypes? If so, that would lead your ComboContactType table to be
mutlivariant. Or would it be that a Contact could only have one type?

With your description, I think I have mapped out the data tables correctly,
but correct me if I am wrong.

tblMaster: ID, Company Name, Address, ETC

tblContact: ID, Name, CompanyID, ETC (There is a one to many relationship
from tblMaster.ID to tblContact.CompanyID)

tblComboContactType: ID, TypeID, ContactID (Not sure of a relationship to
tblContact.ID from tblComboContactType.ContactID)

tblContactType: ID, Descripion (There is a one to many relationship from
tblContactType.ID to tblComboContactType.TypeID) (The Description field
contains a "Signer" Record)

A quick suggestion that I might take is to include the Type (even as a one
to many relationship from tblContactType to tblContact), so that you can
avoid having to create another table for this, and maybe additionally create
a "Yes/No" field for "Signer" in the tblContact. It seems irrelevant and
more difficult to create an additional table to link the two.

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.

I asked this in March but got no replies. Does that mean it's not possible?
Or should I provide greater detail?

Access 2003, XP Srvc Pack 3

I want to automatically classify an entry based on its selection.

Details so far:

tblContact (contains standard info about the contact)
tblContactType (an ID# field and description [classifications for the
contact such as vendor, employee, etc.])
tblComboContactType (contains the ID# for each combination of contact and
type. Example fldComboContactID, fldContactTypeID, fldContactID)
tblMaster (contains info about each company)

Usually to populate the tblComboContactType I tie the main form to the
"Contact" table, the subform to the "ComboContactType" table, and use the
"ContactType" as the row source for a combo box. This works fine.

I want to create an entry form to capture which contacts can authorize
purchases on behalf of a given company.

What I'd like to do on the form is select the company (I know how to do this
part), present a combo box with entries for each contact associated with that
company (pretty sure I can do this), then have the combination of
contact/"fact that they can sign" (this is a unique contact type called
"signer") populate into my tblComboContactType (this is the part I don't know
how to do).

Any suggestions or pointers to figure out how to do this?

Thank you for your help.

Allison