Sort based on the name selected under the combo box

[Hiro]

Hello Forum Members:

Using the Access 2000, I have one form with a drop-down combo box in which I
can choose a name. And this form is referring to the query and I selected the
sort: ascending under the design view. However, when I open the form, the
name field is not properly sorted. My table has primary key and company
names, the form seems to be sorting based on the primary keys. How can I sort
based on the name field on the form?
Any help will be appreciated.

Hiro

 

[Beetle]

Are you saying that the *form* isn't sorting correctly or the *combo box*
isn't sorting correctly? A combo box has it's own row source query which is
separate from the query that you from is based on, so you should check the
sorting in both of them.

 

[Hiro]

Hello Beetle:

Thank you for your quick responce. The combo box is sorting correctly. The
form is not sorting correctly. However, I found this behavior. If I use the
form as a stand-alone, it is sorting, but if I place this form under the tab
control, this form is not sorting correctly. It again seems to be sorting
based on the primary key on the table. Can I still sort the form based on the
field name that I choose from the combo box using the tab control form?

Hiro

 

[KARL DEWEY]

Sort the query that feeds the form.

 

[Hiro]

Hello Karl:

Thank you for looking into this.
What I did on the query is to select sort: ascending under the query design
view. However, it still does not sort the control tab form. It seems to be
sorting based on the primary key on the table. What else can I do to make
this work?

Hiro

 

[KARL DEWEY]

Post your query SQL.

 

[Hiro]

Hello Karl:

Thanks again for your follow-up.
Here is what I have for the Row Source:
{code/}
Select T_Biopsy_Company.ID, T_Biopsy_Company.CompanyName From
T_Biopsy_Company Order By T_Biopsy_Company.CompanyName;
{/code}

Hiro

 

[KARL DEWEY]

The Row Source is for the combo box sorting and not for the form sorting.
You need to sort the query that is the source of the form.
Post the SQL of the query that feeds the form.

 

[Hiro]

Hello Karl:

I am so sorry for my very poor understanding of the Access database. But
where can I find the SQL of the query? I looked in the design view of the
query, but all I see is just sort: Ascending.

Hiro

 

[John W. Vinson]

Hello Karl:

I am so sorry for my very poor understanding of the Access database. But
where can I find the SQL of the query? I looked in the design view of the
query, but all I see is just sort: Ascending.

Open the query in design view.

On the Menu select View... SQL; *or* use the dropdown of the leftmost tool in
the query design toolbar to select SQL.

 

[Hiro]

Hello John:

Thank you for leading me to the right direction. This is a learing curve for
me, but I am enjoying this. Here is what I found:
{code/}
SELECT T_Biopsy_Product.ID, T_Biopsy_Product.Company,
T_Biopsy_Product.ProductName, T_Biopsy_Product.PartNumber,
T_Biopsy_Product.JawsVolume, T_Biopsy_Product.JawsSize,
T_Biopsy_Product.JawsFenestrated, T_Biopsy_Product.SheathSize,
T_Biopsy_Product.Length, T_Biopsy_Product.Shape, T_Biopsy_Product.Formable,
T_Biopsy_Product.FEP, T_Biopsy_Product.Class, T_Biopsy_Product.ApprovalNumber
FROM T_Biopsy_Product
ORDER BY T_Biopsy_Product.Company, T_Biopsy_Product.ProductName,
T_Biopsy_Product.JawsSize;
{/code}

My sort priorities are: 1st-Company, 2nd-ProductName and 3rd-JawsSize
The Company and ProductName are selected from the combo box controls.

Hiro

 

[KARL DEWEY]

SELECT T_Biopsy_Product.ID, T_Biopsy_Product.Company,
T_Biopsy_Product.ProductName, T_Biopsy_Product.PartNumber,
T_Biopsy_Product.JawsVolume, T_Biopsy_Product.JawsSize,
T_Biopsy_Product.JawsFenestrated, T_Biopsy_Product.SheathSize,
T_Biopsy_Product.Length, T_Biopsy_Product.Shape, T_Biopsy_Product.Formable,
T_Biopsy_Product.FEP, T_Biopsy_Product.Class, T_Biopsy_Product.ApprovalNumber
FROM T_Biopsy_Product, T_Biopsy_Company
ORDER BY T_Biopsy_Product.Company, T_Biopsy_Product.ProductName,
T_Biopsy_Product.JawsSize;

 

[KARL DEWEY]

Try this ---
SELECT T_Biopsy_Product.ID, T_Biopsy_Company.CompanyName,
T_Biopsy_Product.ProductName, T_Biopsy_Product.PartNumber,
T_Biopsy_Product.JawsVolume, T_Biopsy_Product.JawsSize,
T_Biopsy_Product.JawsFenestrated, T_Biopsy_Product.SheathSize,
T_Biopsy_Product.Length, T_Biopsy_Product.Shape, T_Biopsy_Product.Formable,
T_Biopsy_Product.FEP, T_Biopsy_Product.Class, T_Biopsy_Product.ApprovalNumber
FROM T_Biopsy_Product INNER JOIN T_Biopsy_Company ON
T_Biopsy_Product.Company = T_Biopsy_Company.ID
ORDER BY T_Biopsy_Company.CompanyName, T_Biopsy_Product.ProductName,
T_Biopsy_Product.JawsSize;

 

[Hiro]

Hello Karl:

Thanks again for your kind help.
I tried your suggestion. When I opened the form, the combo box is all blank.
So, does this mean that I would need to just re-select the company name from
the combo box once again?

Hiro

 

[Hiro]

Hello Karl and John:

I was able to fix this. I just made some adjustments under the property.
Now, every thing is working good. Once again, thank you so much and I
sincerely appreciate your support and time.

Hiro