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 programmaticall y add a Record to a Table between existing Primary Key values to attain the desired result. A simple case will illustrate this point.
[CODE=text]'Before Record Insertion
ID PK LastName FirstName
3 33 Hanks Tom
2 123 Flintstone Fred
1 345 Kellerman Sally[/CODE]
[CODE=text]'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[/CODE]
2 Methods for inserting Record(s) via code
[CODE=vb]Dim MySQL As String

DoCmd.SetWarnin gs False
MySQL = "INSERT INTO tblTest (ID, PK, LastName, FirstName) Values (4,49,'Melvin', 'Harry')"
DoCmd.RunSQL MySQL
DoCmd.SetWarnin gs True[/CODE]
[CODE=vb]Dim MyDB As DAO.Database, MyRS As DAO.Recordset

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecord set("tblTest", dbOpenDynaset)

With MyRS
.AddNew
![Id] = 4
![PK] = 49
![LastName] = "Flintstone "
![FirstName] = "Fred"
.Update
End With

MyRS.Close
Set MyRS = Nothing
[/CODE]

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