Again a combobox question

[ericb]

I have 3 tables

Product with : ID_product, name, ref_orthotic
Orthotic table with : ID_orthotic, name, ref_base, ref_top, ref_middle
Material table : ID_material, name, density, color

Related like this :
One to many Orthotic to Product
One to many Material to Orthotic. ref_base, ref_top, ref_middle(3 links)

In a Product form I want a combobox to select an orthotic. It shows me the
name and numbers for ref_base, ref_top, ref_middle.

I want to see the name of the material that is ref_base, ref_top and
ref_middle point to. Not the number of the pointer.

How do I get the name of the material that ref_base points to ?

Thank you for the help

 

[Arvin Meyer [MVP]]

A combo box can only display 1 column unless it's in the Dropdown mode.
Assuming that it is your second column, make sure that your ColumnCount is
set to 2, and the ColumnWidth property is set like:

0"; 1.5"

or whatever you want the width to display.

 

[John W. Vinson]

I have 3 tables

Product with : ID_product, name, ref_orthotic
Orthotic table with : ID_orthotic, name, ref_base, ref_top, ref_middle
Material table : ID_material, name, density, color

Related like this :
One to many Orthotic to Product
One to many Material to Orthotic. ref_base, ref_top, ref_middle(3 links)

In a Product form I want a combobox to select an orthotic. It shows me the
name and numbers for ref_base, ref_top, ref_middle.

I want to see the name of the material that is ref_base, ref_top and
ref_middle point to. Not the number of the pointer.

How do I get the name of the material that ref_base points to ?

Thank you for the help

Base the combo on a Query joining Orthotic to Material; add the Material table
to the query *three times*, joining one instance to ref_base, a second to
ref_top and the third to ref_middle. Select the material name from the three
instances of the materials table.

 

[ericb]

Hi Steve,

It works very nice.

Have to admit, I'm starting to understand the syntax and how to use the
relations. I guess time does it.

Can I ask you one more ?
A record (orthotic) will not appear in the combo box if one of the 3 names
is not defined (or empty).

How can I keep that record even if the field :
ref_base or rep_top or ref_midle -material.name is empty.

Thank you

 

[John Spencer]

Change the join to an outer join - a left join or right join as appropriate

In query design view, double-click on the join line and select the option All
records in Orthotic table and only matching records in Material table.

Do that for all three joins to the material table incidents.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[ericb]

Thank you
--
eric

Change the join to an outer join - a left join or right join as appropriate

In query design view, double-click on the join line and select the option All
records in Orthotic table and only matching records in Material table.

Do that for all three joins to the material table incidents.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

.

 

[??????? ???????]

hey

Change the join to an outer join - a left join or right join as
appropriate

In query design view, double-click on the join line and select the option
All records in Orthotic table and only matching records in Material table.

Do that for all three joins to the material table incidents.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County