How to sort a forms's record

  • Thread starter Thread starter G Lam
  • Start date Start date
G

G Lam

Hi, I have a data input form with subform. I want to sort the displayed
records in the subform, but I am unable to do that right. The subform is in
continuous form.
The subform has four fields, OrdNbr, BoxNbr, SkuNbr and Qty.
The subform's orderby property was set to BoxNbr at the forms set up.
Because some times the user missed a record and later wanted to add it back,
so I wanted to insert the record in the right order.
I added an insert button with VBA codes to insert a record to the subform
and then sort the reocrds. The insert record part is fine. For sorting the
record part, the two lines of codes like this:
Forms!FrmPack!fsfrPack.Form.OrderBy = BoxNbr
Forms!FrmPack!fsfrPack.Form.OrderByOn = True
It pop up a window asking for "Enter Parameter Value". No matter what value
I entered in this window, the added record would be add to the first one on
the subform and all the old records were only pushed down by this newly
added record but not sorted. Also, the subform's orderby property would
change to [1] instead of the preset BoxNbr. If I inserted a record with
Box#1, then inserted another one with box#2, then the displayed records
would the second one (Box#2) on top of the first (Box#1) and all other old
records pushed down by these two records.
If I comment out the OrderByOn = True line, then there would be no pop up
window and the added record would be on the bottom and all else unchanged.
How can I do it right?
Thank you.
Gary
 
the added record would be add to the first one on
the subform and all the old records were only pushed down by this newly
added record but not sorted.
Click to expand...

I don't know quite how you get new records inserted at the top of a
continuous-forms form, but it's a neat trick if you can. Still, they are
not going to be re-sorted without requerying the recordset; in other words,
you need to do a

Forms!FrmPack!fsfrPack.Form.Requery

somewhere, perhaps behind a command button on the main part of the form.

In addition, I would probably base the subform form on a query rather than
a whole table:

SELECT OrdNbr, BoxNbr, SkuNbr, Qty
FROM MyTable
ORDER BY OrdNbr, BoxNbr

if the OrdNbr is the LinkField, then it should sort before the one you want
to see because you'll only ever see one OrdNbr at a time anyway.

HTH


Tim F
 
Tim, thank you for your reply. I finally got it to work.
I don't know quite how you get new records inserted at the top of a
continuous-forms form, but it's a neat trick if you can.
Click to expand...
Well, if the subform was set to DataEntry = yes, then this would happen.
After I set it to No. The sorting worked and there was no popup window any
more.

Gary
 
Back
Top