Insert row in datasheet view... RRS feed

  • Question

  • I have a form/subform (order/order lines) in which the subform is a datasheet.  Is there a way (similar to Excel) to insert a record in the datasheet, rather than at the end?

    Thanks for your help!!

    Friday, February 10, 2017 3:15 PM

All replies

  • No. Access is a database and not a spreadsheet app. If you want rows in a certain order you can simply re-sort the data by a selected column after adding a new row.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, February 10, 2017 3:24 PM
  • Hi,

    As Paul already mentioned, database tables do not store data/records in any particular order. Although the data is displayed in a format similar to Excel, there is no way facility to move individual cells around. So, if you want the records displayed in a particular order, you will have to apply a "sort" order to the form or query. But, adding new data will always have to be done at the end of the list.


    Friday, February 10, 2017 3:43 PM
  • Speaking about going to the end of the list, I sometimes put a button on the form that takes the user to a new record so they don't have to scroll down. It's as simple as this:

    Private Sub cmdGoNew_Click()
        On Error GoTo err_PROC
        DoCmd.GoToRecord Record:=acNewRec
        Exit Sub
        Resume exit_PROC
    End Sub

    Bill Mosca

    Friday, February 10, 2017 3:50 PM
  • Is there a way (similar to Excel) to insert a record in the datasheet, rather than at the end?
    No, but a form, whether it is a parent form or subform, will normally show records in a defined order, usually by means of the ORDER BY clause of its RecordSource query.  This means that, while the new row will initially be the last in the form, once the form is requeried it will be returned at its correct place in the sort order.  This will happen if the form is closed and then reopened for instance, but it can be done immediately after inserting the row.  The following is an example which does this in a form bound to a query on a Transactions table which orders the rows by date.  The code requeries the form and then moves the record pointer back to the edited row:

    Private Sub Form_AfterUpdate()

        Dim lngID As Long
        lngID = Me.TransactionID
        With Me.RecordsetClone
            .FindFirst "TransactionID = " & lngID
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With
    End Sub

    On occasions it might be necessary to return a new row in a position which cannot be governed by ordering the rows on any one or more existing columns in the table.  The following code is an example of this, and is for ensuring the correct order of rows in a form based on a table of regular payments from an account.  To achieve the correct order the a PaymentIndex column of integer number data type is added to the table, incrementing by 10.  If it is necessary to position a row between two rows with current PaymentIndex values of 30 and 40 say, the new row would be given a PaymentIndex value of 35 (or any value >30 and <40).  The code then reinstates the increments of 10.  It does not move the record pointer back to the edited row, but that could easily be added with additional code similar to the latter part of the above:

    Private Sub cmdReindex_Click()

        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim strRS As String
        Dim n As Integer
        Me.Dirty = False
        strSQL = "SELECT PaymentIndex FROM RegularPayments ORDER BY PaymentIndex"
        Set rst = CurrentDb.OpenRecordset(strSQL)
        With rst
            n = 1
            Do While Not .EOF
                .Fields("PaymentIndex") = n * 10
                n = n + 1
        End With
        Set rst = Nothing

    End Sub

    Ken Sheridan, Stafford, England

    Friday, February 10, 2017 6:39 PM
  • one other note: Access offers the split form.  Using this the single form/record can display on top while the continuous list of records displays in the bottom half of the form....

    one can use that to add a new record

    ..... also another point: in datasheet view one can highlight a row with the right click, copy, paste ...it puts the new record at the bottom but then one could trigger a resort.....

    Saturday, February 11, 2017 4:50 PM