Sort Order

[Guest]

I have a form called QuoteForm that is based on a table called QuoteHeader.
The form has a subform called QuoteDetails which is based on a query called
QuoteDetailsQuery. The subform can have as many as 15-20 line items which
are entered in the order that our customer requests.

From time to time the sort order gets jumbled. Instead of printing as
entered, the items get arranged it what appears to alpha-numeric order based
on a field called ProductName. However, in thesort order field in properties
is always blank - both in the subform and the query itself.

The fix to this point has been to Compact and Repair the databased and then
delete and re-enter each line item. Is there a way to set the order based on
the enter order?

Any help would be great.

 

[Guest]

Click in the field you want to sort and then on the toolbar click the A-Z
icon. Then click the save icon.
It sould stay sorted until someone else changes the sort.

 

[Guest]

I am aware of how to sort. My problem is that the periodically the subform
gets jumbled into what appears to be a sort. Sometimes on print preview the
order is jumbled. Then when going back to the form the order is messed up.
I am looking for a way to ensure that the order remains as entered.

Thanks

 

[John Vinson]

Is there a way to set the order based on
the enter order?

Only (AFAIK) by having a field stored in the table which records the
entry order; this could be a sequential Autonumber or a Date/Time
field defaulting to Now().

Base your forms on queries sorted by this field; and use this field in
the Sorting and Grouping property of any Report that uses the table.

The order of data in a table is arbitrary and uncontrollable. Access
will present the records in whatever order the query optimizer finds
convenient, if you don't have a query or report order specified; there
is absolutely NO internal way of identifying data entry order, unless
you provide it yourself as above.

John W. Vinson[MVP]

 

[Guest]

Having and AutoNumber field called Line Item would probably work. Any ideas
on how to make the line numbers apply to each Quote#? If I just create a
field in my QuoteDetails table called LineItem, the AutoNumber will increase
the numbers infinetely.

For example, I would like the first line item for a quote to be line item
#1.

Thanks

 

[Douglas J Steele]

You can't do that using an Autonumber.

However, it shouldn't really matter. As long as your Quote# are increasing,
it should matter whether the line numbers for Quote 123 are 1, 2, 3 or 2345,
2346, 2347: they'll still sort properly.

If you want to have line numbers that you can use for other purposes, you'll
have to roll your own.

 

[John Vinson]

Having and AutoNumber field called Line Item would probably work. Any ideas
on how to make the line numbers apply to each Quote#? If I just create a
field in my QuoteDetails table called LineItem, the AutoNumber will increase
the numbers infinetely.

For example, I would like the first line item for a quote to be line item
#1.

Douglas is correct about the sorting issue; if you're not displaying
the number, then using an Autonumber and sorting by it should be fine.

If you wish to have a restarting line number for both sorting and for
display to the user, you can do so easily enough if (as you should be
doing anyway) you use a Form with a Subform for data entry. Base the
Form on the Quotes table, and the Subform on the QuoteDetails table;
put the following code in the Subform's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!LineItem = NZ(DMax("[LineItem]", "[QuoteDetails]", _
"[Quote#] = " & Me![Quote#])) + 1
End Sub


John W. Vinson[MVP]