insert a record

[rudeman76]

Hi again,

Just wondering if there is a way that I can insert a record in between two records in a table using code.

Andrew

[ADezii]

Hi again,

Just wondering if there is a way that I can insert a record in between two records in a table using code.

Andrew

To the best of my knowledge, the internal structure of Access will not allow Record Insertions between existing Records in a Table. Access will automatically Append Records to the end of the Table. Since in a Table, Access automatically displays Records in a Table in Ascending Order by the Table's Primary Key, you can programmatically add a Record to a Table between existing Primary Key values to attain the desired result. A simple case will illustrate this point.

'Before Record Insertion 
ID    PK     LastName     FirstName
3    33        Hanks       Tom
2    123    Flintstone    Fred
1    345    Kellerman    Sally

'After Record Insertion with Primary Key ([PK]) = 49
ID    PK      LastName        FirstName
3    33         Hanks          Tom
4    49         Melvin          Harry
2    123     Flintstone       Fred
1    345     Kellerman       Sally

2 Methods for inserting Record(s) via code

Dim MySQL As String
 
DoCmd.SetWarnings False
  MySQL = "INSERT INTO tblTest (ID, PK, LastName, FirstName) Values (4,49,'Melvin', 'Harry')"
  DoCmd.RunSQL MySQL
DoCmd.SetWarnings True

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblTest", dbOpenDynaset)
 
With MyRS
  .AddNew
    ![Id] = 4
    ![PK] = 49
    ![LastName] = "Flintstone"
    ![FirstName] = "Fred"
  .Update
End With
 
MyRS.Close
Set MyRS = Nothing
 

[NeoPa]

Hi again,

Just wondering if there is a way that I can insert a record in between two records in a table using code.

Andrew

I expect ADezii's last code snippet should have added Harry Melvin rather than re-adding Freddie Flintstone, but otherwise a very full and useful answer again from (one of) our best VBA experts.
@Andrew, you left us a little short of info if what you're trying to do is add a record to a table whose PK is an autonumber (just a wild guess that that's what you're looking for here). If so then you get into much more complicated territory, but the short answer is that you can't specify a key to add to an autonumber table I'm afraid.
If that's not your problem then you could hardly find a better answer than ADezii's on how to go about doing what you need.

[rudeman76]

yes, it has to do with an autonumber. No biggie, was just wondering if it could be done. Thanks again

Andrew

[NeoPa]

No worries Andrew, but you'll find that a little extra preparation in the question will usually reap more appropriate answers and more quickly too.
I'm not trying to preach, just sharing what I've learnt from experience ;)

[ADezii]

I expect ADezii's last code snippet should have added Harry Melvin rather than re-adding Freddie Flintstone, but otherwise a very full and useful answer again from (one of) our best VBA experts.
@Andrew, you left us a little short of info if what you're trying to do is add a record to a table whose PK is an autonumber (just a wild guess that that's what you're looking for here). If so then you get into much more complicated territory, but the short answer is that you can't specify a key to add to an autonumber table I'm afraid.
If that's not your problem then you could hardly find a better answer than ADezii's on how to go about doing what you need.

I expect ADezii's last code snippet should have added Harry Melvin rather than re-adding Freddie Flintstone, but otherwise a very full and useful answer again from (one of) our best VBA experts.

Thanks NeoPa, for pointing this out to me. I guess I've been watching too many cartoons of late. (LOL).