Move datasheet to top of subform

Question

Hello,

We have a main form (frmVendorProcessingDetail) and a subform in datasheet view (fsubVendorProcessingDetail). We have a text box on the main form to accept barcodes (txtBarCode). If that field has the focus and you scan a barcode, you will go to the first record on the subform that matches that barcode. The subform shows 15 rows, but an order may have hundreds of items. So far, so good.

The problem is when you have maybe 10 orders for the same barcode. If you have a very large order, and are working in the middle of the recordset, and scan a new barcode, the new record (first) will be at the bottom of the subform (hiding 9 out of the 10 orders). What I would like to do is have my code move the records to the top of the subform (there may be many records above and below). So just the relative position is moved so that it is easier to see all the records associated with that barcode. So right after the “SetFocus” we would like to move that line to the top of the subform.

Image 1 shows the barcode being entered (bottom of main form).

Image 2 shows the results where the found line is at the bottom.

Image 3 shows where we would like the line to be.

Here is some (simplified) code for the barcode field:

Private Sub txtBarcode_AfterUpdate()
On Error GoTo eh
    Dim rs As DAO.Recordset
    Dim cWhere As String

    cWhere = "(tblInventory.UPC = '" & Me.txtBarCode & "' OR tblInventory.ISBN13 = '" & Me.txtBarCode & "' OR tblInventory.ISMN = 'M" & Right(Me.txtBarCode, 9) & "') AND tblVendorOrderItems.QuantityRecieved = 0"
    rs.FindFirst cWhere

    Me.fsubVendorProcessingDetail.Form.Bookmark = rs.Bookmark
     Forms.frmVendorProcessingDetail.fsubVendorProcessingDetail.Form.Received.SetFocus
        Me.fsubVendorProcessingDetail.SetFocus
    
End Sub

---

Albert S

Answer 1

Can't you just 'resort' it after update?

So unfinished are shown on top.

Answer 2

There are still a lot of records above the selected one, so I don't think resorting it would work.

---

Albert S

Answer 3

After someone enters a Barcode, some 'query' is being executed?

Just sort the 'query' as Barcode, ISBN, Account (if user enters Barcode) and ISBN, Account if users doesn't enter Barcode.

Answer 4

Sorry, not sure how to do that.

I have a function to resort the records, but that just moves the cursor to the first record of the entire subform. That's not what we want.

Thank you,

Albert

---

Albert S

Answer 5

OK, I solved it.

Added this code:

Me.fsubVendorProcessingDetail.Form.Recordset.MoveLast

Then:

Me.fsubVendorProcessingDetail.Form.Bookmark = rs.Bookmark

Quirky thing about the datasheet. If you go to the last record and then to the bookmark, it wil put that record at the top of the subform.

Thanks for helping me think this one through!

---

Albert S