sorting field alphabetically in a form

[mikeder]

Hi,

I have what seems to be a simple problem but I can't seem to solve it.

I have a form (report form) that has a drop down box that lists all my clients alphabetically. The form's record source is from a query that combines multiple fields from several related tables. The purpose of this form is to create a report that lists project activity by client over a certain time period. This form also has two active x calendars that enables the end user to choose a start and end date for the project activity.

In another form (update project form), I am able to add new clients and update project information. However, when I add new clients on this form, the report form's client dropdown menu instinctively puts these clients at the bottom of the list. In the update project form, the record source is a table (as opposed to a query in the report form) and in the data sheet tab under properties, I can enter in an orderby property to order by Client_Name. However, in the report form, when I indicate to orderby Client_Name, it doesn't work and my new client entries still end up at the bottom of the drop down menu.

Can anybody tell me how to get my drop down menu in my report form to list by alphabetical order??

Thanks

 

[Elana]

It should work if you have indicated the sort order in the query that is the control source of the combobox.

If you have already done this, then post the SQL code behind the query and let's take a look at it.

 

[mikeder]

here is a screen capture

Attached is a picture of my front end interface. As you can see, in the client combo box, the last two entries after Zador Construction Services is Kinder Morgan and, Atlantic LNG. I can't seem to sort it so that when I add new entries these get sorted alphabetically.

I have also attached a screen capture of the design view (in the next post cuz i can't post 2 file at once) indicating the sort order by Client_Name as ascending. In the datasheet view of the form query, the Client_Name is sorted alphabetically. I think the problem may lay in the fact that each time I enter a new Client_Name, it gets assigned a unique ID. When I enter a Client with name ABC, it put at the bottom of the combo box because it's unique ID is autonumbered as last.

One other detail to note is that this form query is combining data from two tables, a project information table and a client information table.

Below is the SQL code copied from the form query.

[Projects].[ProjectID], [Projects].[HFPID], [Projects].[Client_ID], [Projects].[Year], [Projects].

, [Projects].[FileNumber], [Projects].[ProposalPhase], [Projects].[JobPhase], [Projects].[Active], [Projects].[Last_Modified], [HFP_Personnel].[F_Name], [HFP_Personnel].[L_Name], [Client].[Client_Name], [Projects].[WorkDescription]
FROM HFP_Personnel RIGHT JOIN (Client INNER JOIN Projects ON [Client].[Client_ID]=[Projects].[Client_ID]) ON [HFP_Personnel].[HFPID]=[Projects].[HFPID]
ORDER BY [HFP_Personnel].[F_Name] DESC , [HFP_Personnel].[L_Name] DESC;


Thanks for your help

 

[mikeder]

here is the design view screen capture

here is the design view screen capture

thanks,

 

[Elana]

It is sorting on personnel FName/LName first, then on the clientname. Try changing the order by moving the clientname field ahead of the personnel name fields.

Personally, I would change the source of the combobox to SELECT tblClient.ClientID, tblClient.ClientName FROM tblclient ORDERBY tblclient.clientname

and leave it at that.

HTH