related combo boxes in the same forms

[Francis]

Hello,
************Introduction:***********
Have a database to a company, where i store the entities contacts and
data in a table Company_Table, that links to another Table
AgentSegment_Table, that serves the pourpose to create a surrogate key
AgentSegmentID, in order to select the many to many options in the
relation Agent_Table and Segment_Table.


Basically Agent is the entity type as it works with the company,
examples of Agent: Clients, Clients&Suppliers, Supplier, Bank,
Employer.


Segment is intended mainly to marketing actions, empowering the
database usefullness, what does it store? well for Agent= Client,
Segment can be {clienttype1,clienttype2,...,clienttypen}
for Agent = Client&Supplier, Segment can be
{clienttype1,clienttype2,...,clienttypen}
for Agent = Bank, Segment can be {banktype1,...,banktype2}
etcetc


notice i made the results of segment equal on Client and
Client&Supplier, but that is not a problem since the Segment database
stores all the different instances of Segments.


The AgentSegment_Table db object has all the logic combinations of
Agents and Segments.


*******Table Structure**************
Entities_Table
pk: entitiyID
fk: agentsegmentID
(another fields of entity information that are not important to this
topic)


AgentSegment_Table
pk:agentsegmentID
fks:agentID, segmentID


Agent_Table
pk:agentID
agent (index, no duplicates, not null)


Segment_Table
pk:segmentID
segment (index, no duplicates except nulls, accept nulls)


Relations:
Entities_Table.agentsegmentID n:1 AgentSegment_Table.agentsegmentID
AgentSegment_Table.agentID n:1 Agent_Table.agentID
AgentSegment_Table.segmentID n:1 Segment_Table.segmentID


****SO FAR SO GOOD YES*******
Ok lets put this in one form and two comboboxes, with respectively the
following Properties:
Form
Name: ARM (for me it makes sense: Agent-Relationship-Management)
RecordSource: Entities_Table


ComboBox For Agent selection:
Name: comboagent
ControlSource: agentsegmentID
RowSourceType: Table/Query
RowSource: (This one is where some doubts starts rising)
SELECT DISTINCT First(AgentSegment_Table.agentsegmentID) AS
FirstOfagentsegmentID, Agent_Table.agent FROM AgentSegment_Table,
Agent_Table WHERE
(((AgentSegment_Table.agentID)=[Agent_Table].[agenteID])) GROUP BY
Agent_Table.agent;
ColumnNumber: 2
ColumnsWidth: 0cm;2cm
BoundColumn: 1
Resuming: I meant to use the field agentsegmentID to store the entity
classification towards its agent-segment values. In the query I simply
store one of the possible, in this case doesn't matter if is the
first, agentsegmentID that is related to the agent I see in the
comboagent listing in the form.


ComboBox for SegmentSelection: (now this is the complex part where I
get lots of doubts as well)
Name: combosegment
ControlSource: agentsegmentID (!!!!!!)
RowSourceType: Table/Query
RowSource: (This one returns null on Change of comboagent, clearing the

choice and requerying the listing of the combosegment)
SELECT AgentSegment_Table.agentsegmentID, Segment_Table.segment FROM
AgentSegment_Table, Segment_Table WHERE (AgentSegment_Table.segmentID =

Segment_Table.segmentID) AND (AgentSegment_Table.agentsegmentID
=[forms]![ARM]![comboAgent]) UNION SELECT distinct null, null FROM
Segment_Table ORDER BY Segment_Table.segment;
ColumnNumber: 2
ColumnsWidth: 0cm;2cm
BoundColumn: 1
Resuming: It's not perhaps a good practice to use the same field in
two different controls of the same forms, but in abstraction point of
view it makes sense. Because the combosegment is a narrow of the
comboagent.


*****EVENTS*****
Option Compare Database


Private Sub comboAgente_Change()
combosgment.Value = Null
combosgment.Requery
End Sub


Private Sub Form_Current()
combosegment.Requery
End Sub
******************************
OK I Hope someone can really help me here =) thanks in advance!!