combo box

[Raul Sousa]

I have two questions.
I want to insert on a form a combo box. I need the source
to this combo box to be a field A from table A and field
B form table B.
Example:
I have a table for suppliers and one for clients, both
with a filed called name. I want the combo box to list
the name of all clients and suppliers. I can't do this.
Is it possible?

Another question.
The field name in my supplier table is the primary key.
When I open the supplier form it is sorted by the primary
key. I want it to be sorted by another field. I can't do
this. Is it possible?

 

[Fredg]

Raul
See comments interspersed below....
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

I have two questions.
I want to insert on a form a combo box. I need the source
to this combo box to be a field A from table A and field
B form table B.
Example:
I have a table for suppliers and one for clients, both
with a filed called name. I want the combo box to list
the name of all clients and suppliers. I can't do this.
Is it possible?

Yes it is possible. Make a query.
Place the combo box on the form.
Click on the Combo Box's RowSource property line.
Click on the button with 3 dots that will appear.
When the Query window opens, add the 2 tables to the upper secton.

Since any client can have more than one supplier, and
any client can have more than one order,
also add the table in which both tables are brought together,
i.e. the OrderDetails table.
Make sure the relationship joins between the 3 tables are correct.
Drag the wanted fields from TableA and TableB onto the lower grid.

On the SORT row, write 'ascending' onto whichever row you wish to sort by.

Next change the resulting SQL statement
(click on the View toolbutton and select SQL)
from
Select
to
Select Distinct

Save the changes. That should be all you need do.

NOTER. If you have fields named 'Name' you should change them to something
else, i.e. 'SupplierName', 'ClientName'.
Name is an Access reserved word and should not be used as a field name.
From Access Help files:

Guidlines for naming fields, controls, and objects
** snipped **

When you name a field, control, or object, it's a good idea to make sure the
name doesn't duplicate the name of a property or other element used by
Microsoft Access; otherwise, your database can produce unexpected behavior
in some circumstances. For example, if you refer to the value of a field
called Name in a table NameInfo using the syntax NameInfo.Name, Microsoft
Access displays the value of the table's Name property rather than the value
of the Name field.

* snipped **

Another question.
The field name in my supplier table is the primary key.
When I open the supplier form it is sorted by the primary
key. I want it to be sorted by another field. I can't do
this. Is it possible?

Open the form.
Highlight the field you wish to sort by.
Click on the Sort A-Z toolbutton.

 

[John Spencer (MVP)]

Yes, both are POSSIBLE.

First question:
You will need to use a union query to get the information from both tables.
You cannot build a union query using the query grid, but must type it in. Your
query would look something like:
SELECT FieldA FROM TableA
UNION
SELECT FieldB FROM TableB

The problem I see is that when you select something in this combo based on this
query, you won't know which table supplied the value. Obviously, you may need
to supply more details.

Second Question:
You need to sort the underlying query. If you are basing the form on a table,
then you need to change to a query based on that table.