none
Move datasheet to top of subform RRS feed

  • 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

    Thursday, December 19, 2019 7:13 PM

Answers

  • 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

    • Marked as answer by Albert S Friday, December 20, 2019 12:26 AM
    Friday, December 20, 2019 12:26 AM

All replies

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

    So unfinished are shown on top.

    Thursday, December 19, 2019 7:24 PM
  • There are still a lot of records above the selected one, so I don't think resorting it would work.

    Albert S

    Thursday, December 19, 2019 7:45 PM
  • 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.

    Thursday, December 19, 2019 8:02 PM
  • 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

    Thursday, December 19, 2019 8:18 PM
  • 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

    • Marked as answer by Albert S Friday, December 20, 2019 12:26 AM
    Friday, December 20, 2019 12:26 AM