[OBP]

Have you tried setting the "Sort Order" that you require in the Query supplying the Form's data?

[Ref2335]

It will sort properly if I only choose one of the planner codes. If I choose the entire range of planner codes for 2007, for instance, then it doesn't sort properly to the form.

[OBP]

Can you post a copy of what you have now and what you select so that I can reproduce it?

[Ref2335]

Thats a tough nut to crack as the best way to see what I'm talking about is to look at it with live data and the stuff thats in there is confidential.

[OBP]

Have you set more than one Column in the Query to sort the data, including the Planner Codes?

[Ref2335]

I have only set the planner codes columns for 2007 buy history to sort descending.

In the tsg file that you provided to me the columns that are selected for sorting are all planner code fields (B0 through BR) for range tblBuyHistory2007Cross_1.

What seems to happen is it sorts by B0 if it can, if not it will move to B1 and so on.

Where this presents an issue is if the filtered fields are focused on say, B5, but the suppliers have data in all fields, B0 through B5. This skews the sorting.

[Ref2335]

Any ideas or am I looking at Access not being able to do what I'm looking for?

[OBP]

Can you post me what you currently have with precise instructions of what to ask for and what Output you want?

[Ref2335]

OK, I've cooked up some data to put into the sample db we have been using.

What I am experiencing is that the final sort of supplier does not change regardless of how the user looks for their data. For example, lets just focus on UL/B0, NM/B1, and Tubes/B2. Feel free to choose these at your leisure, just make sure you choose the 3rd party supplier type as thats what the sample is all loaded as.

The final sort of suppliers for Ul, NM and Tubes all are the same even though the query is set to show suppliers in descending order based on spend. What I need to have is that the final sort of suppliers is in proper descending order based on the begining Modality (UL, NM, or Tube)

I hope I am explaining this well enough for you to understand...

[OBP]

Ref, I have no idea where the Query and Form are getting the Supplier Name as the Sort field from.

But I have solved your problem for you.
All we have to do is use some VBA to set the Form's Sort Order to the Chosen Planner Code and then switch on the Sort By.
I have only done one Planner Code, it is the Tube - BR and you can see the code in the Form's "On Open" Event Procedure.
Do you think you could manage to do the rest based on the Code that already exists for the On Current Event and this new Code?

[Ref2335]

Hmmm seems something is funny here. When I choose tubes/B2 and then click third part I get a parameter pop up. If I click ok and leave the parameter blank it sorts by supplier name.

[OBP]

That is odd because I have just checked and it is working fine.
I have also downloaded the version that I put on the Forum and tested that as well.

[Ref2335]

OK, I looked in the VBA on open and saw that it specified to sort on BR. Tubes is B2 so I changed it from BR to B2.

I am confused by the OrderBy with the " ' " notation in the on current section above that. Doesn't the ' notation set the command aside as programming notes?

In any event, here is what I adjusted it to and it gives the parameter pop up.

[OBP]

Ref, on your version I get a pop asking the B2 !
Is that wierd or what?
Can you do the rest of the VBA to use the other Planner Codes?

The " ' " works because it is in quotes and Access accepts it as an alternative quote to ".
So it is the accepted way of using double Quotes as 2 sets of ""

[Ref2335]

No problem, I'll add the ' statement to the remaining if statements to the in current code and will add the if orderby to the on open. Should it be just a collection of "if, then" statements there or "if then else"?

I wonder if we are getting the parameter pop up because the order by box is blank on the f_mvs suppliers properties?

Also it seems its still not sorting properly...