Adding a record to a table

[Guest]

I know this is really basic, but I can't remember how to add a record to a
table and I can't find a full example to copy.

I am keeping a log in tblReportLog of a certain report. Each time I run the
report, I want to assign it the next higher index number and store that
number (along with other fields) in a new record. I seem to have figured out
how read the ReportIndex number from the highest record already in
tblReportLog. I then have added one (1) to the ReportIndex. However, I am
frustrated that I can’t remember how to write the new record to the table.
What you see are my attempts, but they don’t work. Would someone mind solving
this for me.

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim lng As Long
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT ReportIndex FROM tblReportLog")
rst.MoveLast
lng = rst!ReportIndex + 1
rst!ReportIndex = lng
rst!ReportDate = #1/11/2005#
rst!ReportType = "I"

With rst
.AddNew
.Update
.Close
End With

Exit_Command0_Click:
Set dbs = Nothing
Set rst = Nothing
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[Guest]

Ok I think I solved this myself. Code that worked to append a record is:

With rst
.AddNew
!ReportIndex = lng
!ReportDate = #12/2/2004#
!ReportType = "I"
.Update
.Bookmark = .LastModified
End With

 

[Marshall Barton]

Verrrrry close ;-)

The only flaw is that the MoveLast might not go to the
record with the highest index. This is because the records
in the table are not guaranteed to be retrieved in the same
order that they were added (or any other order). To make
your code reliable, just add an Order By clause to the SQL
statement. You could even get rid of the MoveLast statement
if you used DESC.

Your code to add a new record is a standard approach, but
don't you want to use the Date() function instead of hard
coding a date?

 

[Guest]

Marshall,

Thanks for the tip. I had the impression -- probably incorrectly -- that if
my SELECT text begins with a field that is indexed, the file will be read via
that index, so that a MoveLast would go to the last record based on that
index. From what you wrote, I don't think that is correct.

Verrrrry close ;-)

The only flaw is that the MoveLast might not go to the
record with the highest index. This is because the records
in the table are not guaranteed to be retrieved in the same
order that they were added (or any other order). To make
your code reliable, just add an Order By clause to the SQL
statement. You could even get rid of the MoveLast statement
if you used DESC.

Your code to add a new record is a standard approach, but
don't you want to use the Date() function instead of hard
coding a date?
--
Marsh
MVP [MS Access]

Ok I think I solved this myself. Code that worked to append a record is:

With rst
.AddNew
!ReportIndex = lng
!ReportDate = #12/2/2004#
!ReportType = "I"
.Update
.Bookmark = .LastModified
End With

 

[Marshall Barton]

Thanks for the tip. I had the impression -- probably incorrectly -- that if
my SELECT text begins with a field that is indexed, the file will be read via
that index, so that a MoveLast would go to the last record based on that
index. From what you wrote, I don't think that is correct.

That does seem to happen a lot. Especially right after
compacting the database (when the records are "defragged" to
be stored in PK order).

However, AFAIK in the last several versions of Jet, the
records are retrieved in the order they are stored on disk.
This means that the order can be pretty near random after a
bunch of deletions/insertions.

But, you can not even count on that. Relational theory
clearly states that a table is an unordered "bag" of records
that can be retrieved in any way that the db implementors
think is an efficient way to do it, so it could change from
one version to the next.