Combo Box Problem

[magmike]

I have a combo box on a form (ItemsSent.SentTo) that I want to call
into itself for selection, the contacts (SQL field: Contacts:
[ContactFirstName] & " " & [ContactLastName]) from the contacts table
that belong to the prospect (ProspectTable.ID that has been stored in
ItemsSent.CompanyID) that this collateral is being sent to and store
the SQL field result (i.e. "John Smith") in the ItemsSent.SentTo field
(which is also used on letters (reports)). I don't want it limited to
items in the list. I am having trouble figuring this out. I have tried
multiple options, so don't want to post the code here, but if you want
to see an example of the SQL (i've actually been using design view in
query of the combo box) I can pick one out and post.

Does anyone know how I could do this, only displaying in the combo
box, the contacts that relate to the company the item is being sent
to?

Let me introduce the players in this production:

Tables:
Table: ProspectTable (main table)
Field: ProspectTable.ID (autonumber and the "id" of the
company)

Table: ItemsSent (tracks collateral sent to
Field: ItemSent.ItemSentID (autonumber and the "id" of this
particular sending of info)
Field: ItemSent.CompanyID ((ProspectTable.ID for company this
collateral was sent to)
Field: ItemSent.SentTo (to show the name of the person
collateral was sent to)

Table: Contacts (keeps information on different people at the Prospect
record)
Field: Contact.ContactID (autonumber and the "id" of the
contact)
Field: Contact.ContactCompany (ProspectTable.ID for company
this contact belongs to)
Field: Contact.ContactFirstName
Field: Contact.ContactLastName

The form in question: ItemsSent
DataSource: ItemsSent table.
Combo Box in question: SentTo

Thanks in advance!
magmike

 

[Boyd Trimmell aka HiTechCoach via AccessMonster.co]

I would suggest that you look at the cascading combo box example at
http://www.utterangel.com to get some idea of how to handle this.

I have a combo box on a form (ItemsSent.SentTo) that I want to call
into itself for selection, the contacts (SQL field: Contacts:
[ContactFirstName] & " " & [ContactLastName]) from the contacts table
that belong to the prospect (ProspectTable.ID that has been stored in
ItemsSent.CompanyID) that this collateral is being sent to and store
the SQL field result (i.e. "John Smith") in the ItemsSent.SentTo field
(which is also used on letters (reports)). I don't want it limited to
items in the list. I am having trouble figuring this out. I have tried
multiple options, so don't want to post the code here, but if you want
to see an example of the SQL (i've actually been using design view in
query of the combo box) I can pick one out and post.

Does anyone know how I could do this, only displaying in the combo
box, the contacts that relate to the company the item is being sent
to?

Let me introduce the players in this production:

Tables:
Table: ProspectTable (main table)
Field: ProspectTable.ID (autonumber and the "id" of the
company)

Table: ItemsSent (tracks collateral sent to
Field: ItemSent.ItemSentID (autonumber and the "id" of this
particular sending of info)
Field: ItemSent.CompanyID ((ProspectTable.ID for company this
collateral was sent to)
Field: ItemSent.SentTo (to show the name of the person
collateral was sent to)

Table: Contacts (keeps information on different people at the Prospect
record)
Field: Contact.ContactID (autonumber and the "id" of the
contact)
Field: Contact.ContactCompany (ProspectTable.ID for company
this contact belongs to)
Field: Contact.ContactFirstName
Field: Contact.ContactLastName

The form in question: ItemsSent
DataSource: ItemsSent table.
Combo Box in question: SentTo

Thanks in advance!
magmike