combo boxes

[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!!

 

[TC]

What is your question?

TC

 

[TC]

PS. Francis, please read-up on the terms "multi posting" and "cross
posting" to see why multiposting is BAD and makes everyone MAD!

HTH,
TC

 

[Francis]

Ok

Well the problem is i want the 2 combo boxes working.

 

[TC]

Well, I want to win the lottery - but that ain't gonna happen either!

Seriously - there is a reason why no-one else has answered your post.
You've posted a great wad of information, which is quite difficult to
understand (in my humble opinion), but you haven't actually asked a
clear question. "I want the 2 combo boxes working", is not a clear
question.

I'm just trying to help you get an answer. You're free to ignore this
if you choose to.

HTH,
TC

 

[Francis]

Well i welcome all the Help and i just wanted to be little specific, i
have build up a sample of the Database

http://www.luxmagna.pt/New folder/sample.rar

If it's possible i would appreciate a lot if you can put it working and
send it to me by email

If anyone is willing to assist here it really would be very nice and i
am most thankfull
Thanks in Advance

 

[John Vinson]

Well i welcome all the Help and i just wanted to be little specific, i
have build up a sample of the Database

http://www.luxmagna.pt/New folder/sample.rar

If it's possible i would appreciate a lot if you can put it working and
send it to me by email

If anyone is willing to assist here it really would be very nice and i
am most thankfull
Thanks in Advance

That is a rather rude request, Francis. The people who "work" here are
all volunteers, donating time to help people answer specific
questions.

Redoing your entire database structure is WAY beyond what is
considered normal support here. If you wish to hire a consultant to
set up your database for a fee, you can use one of the many employment
or contracting services websites to find one.

John W. Vinson[MVP]

 

[TC]

Sure, no problems.

But first, I have a 500 page document that I need some help with. My
publishers have told me that they need an actual, old style typed copy.
Just let me know your email address, so I can send you the file. As
soon as you've posted a typed copy back to me, I will get your database
working for you.

Looking forward to receiving the email address to send the file to,
TC

 

[TC]

.... which is a joke - just in case you did not get it!

John said it better than I could. These newsgroups are great for
getting anwers to specific questions. They are not a source of people
who will do your work for free!

I suggest you find a local Access person who can help you. But
understand that they will charge you a fee, to cover their time &
expertise.

HTH,
TC

 

[Francis]

I dont know if you were calling me rude but if thats the case i think
you are the rude one and i add very rude so get a grip fellow. If thats
not the case it ok.

Anyway i tryed to be specific, it didnt worked, so i tryed to easy the
task to people who volunteer, if i was a volunteer, which i can be in
certain topics that i see here bcose i know the answers, i wouldnt mind
to try and give it a go. It's ok i dont think it is a big issue but try
to be more polite, as considering it's my first posts so im seeing if
this works or not.
ok thanks

 

[John Vinson]

I dont know if you were calling me rude but if thats the case i think
you are the rude one and i add very rude so get a grip fellow. If thats
not the case it ok.

Anyway i tryed to be specific, it didnt worked, so i tryed to easy the
task to people who volunteer, if i was a volunteer, which i can be in
certain topics that i see here bcose i know the answers, i wouldnt mind
to try and give it a go. It's ok i dont think it is a big issue but try
to be more polite, as considering it's my first posts so im seeing if
this works or not.
ok thanks

Francis, I'm just trying to convey the spirit and customs of this
volunteer newsgroup. Posting the RowSource and other properties of a
combo box, saying "I'm having thus and thus a problem with it", and
asking for help is appropriate.

Saying "Download my database, spend a half hour or hour fixing it up
so it works right, and mail it to me" is not appropriate.

We'll be glad to help you with your problem *if you post the problem*
in a comprehensible way, and if you work at *solving the problem*
rather than expecting an unpaid volunteer to do your work for you.

John W. Vinson[MVP]

 

[Francis]

Ok, thanks for explaining me how this works will try to be more
explicit and specific, whereas it's impossible to use properly this
groups feature

Well i have shorten my questions lists to the following:
*What are the advantages/disadvantages to just add every table (and its
fields) of the database to the Form's record source. Normally i have
all relationships with cascading and enforced integrity.
*I often do querys with rowsource property of comboboxes, that output 2
fields (SELECT statement) one is the IDcolumn - normally bound column
and invisible - the second is the column with the listing i want users
to see - i manage this by defining the column width property to 0cm;2cm
for example. The problem i often find is that i cant filter full
records where there are duplicate entrys in the 2nd column, i suppose
SELECT DISTINCT is what i need. But the problem is SELECT DISTICNT only
works if you have only one column, it won't browse all the columns to
check if there are duplicates in that column, and filter it. :/

Well goes

Thanks in Advance
Francisco (Portugal)

 

[John Vinson]

Ok, thanks for explaining me how this works will try to be more
explicit and specific, whereas it's impossible to use properly this
groups feature

I'm not sure what "this groups feature" might be but I'll try to help.

Well i have shorten my questions lists to the following:
*What are the advantages/disadvantages to just add every table (and its
fields) of the database to the Form's record source. Normally i have
all relationships with cascading and enforced integrity.

If you create a Query joining multiple tables, this query will very
often not be updateable. If it IS updateable, it will often be very
inconvenient for the user, since the data in the "one" side of a one
to many relationship will be repeated for every record in the "many"
side; and the user will be obliged to enter data into at least one
field of every one of the tables involved in order to create a new
record in any of them.

*I often do querys with rowsource property of comboboxes, that output 2
fields (SELECT statement) one is the IDcolumn - normally bound column
and invisible - the second is the column with the listing i want users
to see - i manage this by defining the column width property to 0cm;2cm
for example. The problem i often find is that i cant filter full
records where there are duplicate entrys in the 2nd column, i suppose
SELECT DISTINCT is what i need. But the problem is SELECT DISTICNT only
works if you have only one column, it won't browse all the columns to
check if there are duplicates in that column, and filter it. :/

Please give an example, with a few recors showing the distinction. It
may help to also post the SQL view of the combo box's RowSource query,
its bound column, and what you are trying to do with the selected
value.

John W. Vinson[MVP]

 

[Francis]

Ok.

I'm not sure what "this groups feature" might be but I'll try to help.

With using groups feature i meant using the groups in order to help
volunteers with answers, giving them the needed information, thus
helping the poster also.

Please give an example, with a few recors showing the distinction. It

may help to also post the SQL view of the combo box's RowSource query,
its bound column, and what you are trying to do with the selected
value.
There are tables with the following structure and relationsits
related to the example i give in first place so here it goes)

Agent_Table
pk:agentID
agent (index, no duplicates, not null)
-RECORDS-
agentID|agent
1|Client
2|Supplier
3|Client&Supplier
4|Bank

Segment_Table
pk:segmentID
segment (index, no duplicates except nulls, accept nulls)
-RECORDS-
segmentID|segment
1|segment1
2|segment2
3|segment3
4|segment4

AgentSegment_Table
pk:agentsegmentID
fk:agentID (m:1 Agent_Table.agentID)
fk:segmentID (m:1 Segment_Table.segmentID)
agentsegmentID|agentID|segmentID
1|1|1
1|1|2
1|1|3
1|2|1
1|2|2
1|3|4
1|4|4

I have a Form with two related Comboboxes, but i only need to store the
combination value, that is agentsegmentID, in Entities_Table, that has
agentsegmentID as foreign key (and i want the user to choose first the
agent, and after the agent, then segment, remember the segment query
takes information from the agent query, and lists all the segments for
that agent using the Agentsegment_Table).

Because normally i only add one main table per Form, so i dont have
cascading issues as you spoken that exist when people add related
fields of different Tables to form. And this main table
(Entities_Table)has only one field that is agentsegmentID, but the
problem is i have one field, and 2 combo boxes, agent combo box, and
segment combo box.

Combobox name: cmbagent
controlsource: null (unbound)
rowsource:
SELECT DISTINCT Agent_Table.agentID, Agent_Table.agent
FROM Agent_Table, AgentSegment_Table
WHERE (((Agent_Table.agentID)=[AgentSegment_Table].[agentID]) AND
((AgentSegment_Table.agentsegmentID) Is Not Null)) OR
(((Agent_Table.agentID)=[Agent_Table].[agentID]) AND
((AgentSegment_Table.agentsegmentID) Is Null))
ORDER BY Agent_Table.agent;
columnswidth:0cm;2cm
boundcolumn:1

It's easy to create a listing of Agent_Table unbounded, the problem is,
i need to retrieve agentID value each time i change record in form's
view, and that is difficult because the field is unbounded. Even if i
use vb, on the Form's Current Event.

Combobox name:cmbsegment
controlsource:agentsegmentID
rowsource:
SELECT AgentSegment_Table.agentsegmentID, Segment_Table.segment FROM
AgentSegment_Table, Segment_Table WHERE (AgentSegment_Table.segmentID =
Segment_Table.segmentID) AND
AgentSegment_Table.agentID=[forms]![ARM]![cmbagent] UNION SELECT
DISTINCT NULL, null FROM Segment_Table ORDER BY
Segment_Table.segment;
columnwidths: 0cm;2cm
boundcolumn:1

RESUMING: I Have 2 choices (one is related to the first), i store the
value in one field, so i just have to communicate doing this to the
form respectively.

Having spent so much effort to get in here im not giving up someone
help is very appreciate

Thanks in advance
Francisco(Portugal)

 

[John Vinson]

There are tables with the following structure and relationsits
related to the example i give in first place so here it goes)

Francis, I'm sort of tied up this weekend, I'll try to work through
your long post tomorrow or Monday.

John W. Vinson[MVP]

 

[Francis]

Ok thanks

 

[Francis]

Ok thanks

 

[Francis]

Ok thanks

Francisco (Portugal)

 

[Guest]

Mr John Vinson,,, is there any way I can email you my questions directly to
your email address? I would really appreciate it.

G I Maria

 

[Francis]

sorted it out sry for so long to answer
thanks anyway