insert row into table

mmcdonal · December 6th, 2006, 08:42 AM

Just update the existing record. For example, if there is an autonumber PK, then replace the existing values in the other fields with your new values. This will keep the order.

Since you say the new record would take over the old record, this is a text book UPDATE.

For example (pseudo code):

Open recordset on table with:
SELECT * FROM myTable WHERE [PK] = 1
  update record:
  rs("Field1) = newvalue
  rs("Field2") = newvalue
  ...
  rs.Update

You are not doing an insert, but an Update.

Did that help?

mmcdonal

ocsfla · June 16th, 2007, 08:32 AM

Hi Dave,

Here is a scenario I think applies - you have a form based on a table with an autonumber ID field and a seperate sort order field to keep the record results in check, but you need to insert a record somewhere before the end. You now have to re-number all the subsequent sort order ID entries to make some space for your record.

Here is how I would do it:

Create a table named MyTable with an Autonumber field named ID, an integer field named SortID and a text field named stuff.

Create a query as:
SELECT MyTable.ID, MyTable.SortID, MyTable.Stuff
FROM MyTable
ORDER BY MyTable.SortID;

Create your form based on the query so the results are sorted by the
SortID column.

Add a button to your form that you will use to insert the record and name it InsertRecordBtn. You need an afterinsert event on the form to give the SortID field a value. So here is the code for the whole form.

Code:

Option Compare Database

Private Sub Form_AfterInsert()
    'Set the SortID to the same value as the autonumber column
    Me.SortID = Me.ID
End Sub

Private Sub InsertRecordBtn_Click()
On Error GoTo Err_InsertRecordBtn_Click
Dim CurrentSortID
Dim LastID
Dim rs As DAO.Recordset

'Get the value of the SortID at the current position
CurrentSortID = Me.SortID
'Get the value of the highest ID in the table
LastID = DMax("[ID]", "MyTable")

Set rs = Me.Recordset.Clone
   With rs
      .MoveFirst
      'Step through the records and add 1 to every SortID from the current position on
      Do While Not .EOF
       If !SortID >= CurrentSortID Then
            .Edit
            !SortID = !SortID + 1
            .Update

       End If
      .MoveNext
      Loop
      'Create a new record and set the SortID to the value where you started.
      .AddNew
      !SortID = CurrentSortID
      .Update
   End With
Me.Requery

'Take the form to the inserted record so you can add data to it
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & LastID + 1
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Exit_InsertRecordBtn_Click:
    Set rs = Nothing
    Exit Sub

Err_InsertRecordBtn_Click:
    MsgBox Err.Description
    Resume Exit_InsertRecordBtn_Click

End Sub

Hope this is of use to you.

Dave
www.orlandocomputersupport.com