deleting a record in code

Mcgrco

Registered User.
Local time
Today, 08:04
Joined
Jun 19, 2001
Messages
118
i want to store a maximum of 90 record in a table. Each day a new record will be added so i want my code to count the number of records and if it over 90, delete the first record. Im fairly new to code so any help is appreciated. the code i have so far is

Function record_count()

Dim db As Database
Dim strSQL As String
Dim rs As Recordset

Set db = CurrentDb

strSQL = "SELECT Count([Trend Analysis].[reporting date]) AS [CountOfreporting date]FROM [Trend Analysis];"

Set rs = db.OpenRecordset(strSQL)

MsgBox rs.Fields(0)

If rs.Fields(0).Value > 90 Then

******************************
End If

End Function

I cant find any code for deleting records Any suggestion appreciated
 
In Access help, look up "Delete (method)" or "Delete method" - choose the DAO topic. Look at the examples.

Basically, you have to do a few things in code:

First, open the record set in a mode that allows deletions.

Second, use the recordset's Seek method to find the record you want to delete.

Third, use the recordset's Delete method to remove that record.

Fourth, recognize that after you delete the current record, your position within the recordset is no longer well-defined. So you might have to do a MoveFirst or MoveLast to make the recordset variable properly defined.

Deleting a record doesn't actually take effect right away, depending on the details of the recordset: ODBC, amount of file sharing, etc. So don't try any tests that would look for the deleted record right away, you might get the wrong answer. At least until the buffer gets flushed. The recordset's Close operation should guarantee that the buffer will be flushed quickly.
 
OK. Then I would add a field to the table called DateEntered (Date/Time data type - default value = Now())

Then...

Dim db As DAO.Database
Dim rst As DAO.recordset
Dim intCount As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * from tblTicket ORDER BY DateEntered")

intCount = DCount("TicketID", "tblTicket")

MsgBox intCount

If intCount > 89 Then
rst.MoveFirst
rst.Delete
End If


Obviously, anywhere you see "tblTicket", you want to replace it with the table name that holds your data. And replace "TicketID" with the key field in the table.

Good luck!

[This message has been edited by scottfarcus (edited 10-15-2001).]

[This message has been edited by scottfarcus (edited 10-15-2001).]
 
The code posted by scottfarcus may not delete the record you actually want to delete. You should modify the SQL statement so that it selects the SPECIFIC record that you want to delete or use the Find or Seek method to find a particular record. As written, the posted code will delete the first row of the recordset which in fact might be the most recently entered row. Remember, recordsets without order by clauses are unordered sets of data. The rows are returned in what ever order was convienent for Jet at that particular point in time.
 
thanks guys. The first fields is a date field so can i not order on this field and save the changes. ie the first record will allways be the latest date.
 

Users who are viewing this thread

Back
Top Bottom