A quick question about ComboBox for the experts.

[valley]

I Need to eliminate repeat entries of Names in a Combo pull down list

I have since found out that this is possible with the DISTINCT directive in a simple query like
SELECT DISTINCT [Name] FROM Miracle_Cloth_Main ORDER BY [Name] DESC; as the source
property of the combo box.

It looks like ACCESS needs A primary Key included in the querry for the source property
of the combo for the FindFirst function in the after_update event of the combobox
to work with a code similar to

rs.FindFirst "[RecordNum] = " & Str(Nz(Me![Combo112], 0))

The Wizard generate the following code even if you don't select the Primary Key(RecordNum)
during the creation process.

SELECT Miracle_Cloth_Main.RecordNum, Miracle_Cloth_Main.Name, Miracle_Cloth_Main.Cust_ID FROM Miracle_Cloth_Main ORDER BY [Cust_ID] DESC;

Even if you add the DISTINCT Cluse to the above code
it doesn't filter the repeat data.

Is there a compromise where I can get the Filtering by DISTINCT clause to work with the code generated by the wizard so that I get the FindFirst function as well to work ?

 

[gemma-the-husky]

i would create a query in the access design grid, that gives you the columns and order you want, and base your combo box on THAT stored query. Then you know exactly what you will get. Select this query when you create your combo box.

If you are getting duplicates in a combo box, you probably aren't using the right query. The trouble with selecting distinct rows is that you can't tell which duplicates Access will use/reject.

I don't quite understand what you are trying to do after you select an item in the combo box. Which recordset are you examining?

 

[valley]

Many thanks for the reply.

I try to use the Combobox to enter New customer names and create New records from last records by pulling down the customer name .
To pull down the customer names I only need ONE
entry per customer from the recordset.
For creating New records I need to search
and go to the LAST record by using the builtin
FindFirst functionof the Combo.

As the wizard creates a source querry including the
Primary key for this, you can't effectively use DISTINCT directive in the querry.
I am very poor at explaining, but I hope you can figure out.

 

[gemma-the-husky]

yes, but if you have a combo box for customers you ought to take the source as the customers table, so you don't have duplicates.

are you using an orders table say, where you have several orders per customer - in that case, there might be some customers not included in the dropdown at all, if they have no orders.

what i am getting at, is that it seems like you have some potential normalization issues here.

you should have a customers table with one record per customer, and then say an orders table with multiple orders per customer. In this table you only need store the indexref of the customer, and nothing else, as you can get all the other details from the customer table.

 

[ansentry]

Have a look at my sample posted Here

It will fill in all controls when you created a new record. Any questions post back.

 

[valley]

Hello Gemma,

Many thanks for the reply. As I mentioned elsewhere in this Forum this application has to run on a PDA and then synchronise with a DESKTOP without errors. So I try to keep only one table and one form .Of course this necessitates, multiple entries of customer name in one column. I understand this is not the way ACCESS is upposed to work.