Sorting Question

[powerpro]

I have a form in which there is a drop down box which contains the following values to choose from:

EAID
Pump
Readiness
RPIE
Unassigned

All of my equipment forms and reports are later based on one of those above identifiers.

My problem is that once all of my equipment has been added, I can only sort them A-Z or Z-A (based on the drop down box). I really need them to show up in the following order for the other forms and reports after:

RPIE
Pump
EAID
Readiness
Unassigned

Is there something that I can place in the criteria to have it sort in the specific order that I'd like it to?

Thanks, I tried to use search but the function is down for maintenance right now.

 

[randy700]

You could add a field to the table that you can use to put the items in a particular order. Then, include that field in your combo box (column width = 0). Use this field as the bound column of the combo box and sort accordingly.


Randy

 

[TheAceMan1]

How are ya powerpro . . . . .

If thats all there is to the list, set the following properties:

[ul][li]Column Count [purple]1[/purple][/li]
[li]Column Widths [purple]1[/purple][/li]
[li]Row Source Type [purple]Value List[/purple][/li]
[li]Row Source [purple]RPIE;Pump;EAID;Readiness;Unassigned[/purple][/li][/ul]
Give it a whirl and let me know . . . . .

See Ya! . . . . . .

 

[powerpro]

TheAceMan1, thanks for the response. No that doesn't work. Probably due to my poor explanion though.

I think that randy700 is headed in the right direction. I've bounded before in another database. Just not wanting to go that route just yet.

Think of it in this situation:

I have ten pieces of equipment, the form has many fields to input the various information on the equipment such as serial numbers, models, etc..., and one of the fields is the "type" (the dropdown in question) of equipment.

So, let's say the equipment breakdown is:
I have 2 RPIE, 2 EAID, 3 Pump, 2 Readiness and 1 Unassigned

I don't need the drop box itself to have sorted names within it. I need reports to display the 2 RPIE's, then the 2 Eaid's, then the 3 pumps, etc, etc...

At the same time I'd want the actual form to sort them in the order stated above when i'm going from record to record. Right now I can only sort them from A-Z or Z-A which is not the right sequence.

I don't know if that makes any sense yet. Hopefully I can get this figured out, because I'm looking at sorting about 100 pieces of equipment in reality.

 

[TheAceMan1]

Ok powerpro . . . . .

Now were playing with power! [blue]randy700[/blue] is right on target. [blue]You'll need an additional field for sorting.[/blue] Before continuing with this, [purple]backup the data base so you can come back to square one if necessary[/purple] ([blue]I doubt you'll have to[/blue]). Following is an outline of what your gonna do:
[ol][li]Add the sort field to the table.[/li]
[li]Update the sortfield for existing records! (via code)[/li]
[li]Setup a query for the forms RecordSource which includes the sort field.[/li]
[li]Add code for updating the sort field when new equipment is added.[/li]
[li]Setup a query for the Reports RecordSource which includes the sort field.[/li][/ol]
Here we go:

1) Add an additional field to the table with the following properties:
[blue]Field Name [purple]SortTag[/purple]
Data Type [purple]Number[/purple]
Field Size [purple]Integer[/purple][/blue]
Save/close the table.

2) Update existing records: In a new or existing form add a [blue]Command Button[/blue]. In the On Click event, copy/paste the following code ([blue]you[/blue] substitute the proper tablename in [purple]purple[/purple] . . . be careful of spacing):

[blue]   Dim n As Integer, SQL As String, typName As String
   
   For n = 1 To 5
      typName = Choose(n, "RPIE", "Pump", "EAID", "Readiness", "Unassigned")
   
      SQL = "UPDATE [purple][b]TableName[/b][/purple] " & _
            "SET tagSort = " & n & " " & _
            "WHERE EquipType = '" & typName & "';"
      DoCmd.RunSQL SQL
   Next[/blue]

Open the form, hit the button and verify the table has been updated.

3)Setup a query to return all the fields you currently have int form. Include the [purple]SortTag[/purple]. Set you sorting approiately. [blue]Remember sorting priority is from left to right.[/blue] I suggest a sort order of [purple]TagSort - serial number[/purple], so you'll have to position [purple]TagSort[/purple] ahead of [purple]serial number[/purple].

4) Updating TagSort for added equipment: In the After Update event of [purple]Type[/purple] (the combobox), copy/paste the following code ([blue]you[/blue] substitute the proper combobox name in [purple]purple[/purple]):

[blue]   Dim n As Integer, typName As String
   
   For n = 1 To 5
      typName = Choose(n, "RPIE", "Pump", "EAID", "Readiness", "Unassigned")
   
      If Me![purple][b]ComboboxName[/b][/purple].Column(1) = typName Then
         Me!TagSort = n
         Exit For
      End If
   Next[/blue]

I don't know the structure of you combobox, so you may have to play a little with [blue]Column([purple]?[/purple])[/blue].

5) For the Report query, do the same as in [purple]3)[/purple] only for the report.

Thats it . . . . give it a whirl & let me know . . . .

See Ya! . . . . . .

 

[TheAceMan1]

powerpro . . . . .

There's an error in the following routine. You substitute proper in [purple]purple[/purple]:

[blue]   Dim n As Integer, SQL As String, typName As String
   
   For n = 1 To 5
      typName = Choose(n, "RPIE", "Pump", "EAID", "Readiness", "Unassigned")
   
      SQL = "UPDATE [purple][b]TableName[/b][/purple] " & _
            "SET tagSort = " & n & " " & _
            "WHERE [purple][b]TypeFieldName[/b][/purple] = '" & typName & "';"
      DoCmd.RunSQL SQL
   Next[/blue]

See Ya! . . . . . .