Insert row in datasheet view...

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!!

Answer 1

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)

Answer 2

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.

Cheers!

Answer 3

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_PROC:
    Exit Sub

err_PROC:
    Beep
    Resume exit_PROC
    
End Sub

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 4

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
    Me.Requery
        
    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
        .MoveLast
        n = 1
        .MoveFirst
        Do While Not .EOF
            .Edit
            .Fields("PaymentIndex") = n * 10
            .Update
            n = n + 1
            .MoveNext
        Loop
    End With
    Set rst = Nothing
                    
    Me.Requery

End Sub

---

Ken Sheridan, Stafford, England

Answer 5

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.....