Keep a table at 90 records with vba

[rikklaney1]

Hello all, I'm curious about keeping a set number of records in a table. I have two tables. One with 40000 records and one with 90. The 90 are also in the 40000. I have a button on a form to delete records, selected in a listbox form the 90 record table. I'm curious if there is a way to write a query or some vba to add the same number of records back into the 90 table after a delete so that it stays constantly at 90 records. If I delete two records from the 90 it should find the highest id in that table and add the next two highest id records from the 40000. Is there an easy way to do that?

 

[pbaldy]

In pseudo-code:

Variable = 90 - DCount("*", "TableName")

If Variable > 0 Then
  strSQL = "INSERT INTO...SELECT TOP " & Variable & " ...FROM...ORDER BY ID DESC"
  CurrentDb.Execute strSQL, dbFailOnError
End If

 

[isladogs]

The way I read the post, I'd modify that slightly.
Again this is air code and i'm on a tablet so typos likely...

Variable = 90 - DCount("*", "TableName")

Dim lngID As Long
LngID=DMax("ID","TableName") 

If Variable > 0 Then
  strSQL = "INSERT INTO...SELECT TOP " & Variable & " ...FROM...WHERE ID>lngID ORDER BY ID "
  CurrentDb.Execute strSQL, dbFailOnError
End If

 

[rikklaney1]

It's almost there but I think I have something wrong. It fails. Heres what I've got.


Variable = 90 - DCount("*", "tempschedule")

Dim lngID As Long
lngID = DMax("ID", "tempschedule")
If Variable > 0 Then
strSQL = "INSERT INTO tempschedule SELECT TOP " & Variable & " FROM tbl_adjustedschedule WHERE ID>lngID ORDER BY ID"
CurrentDb.Execute strSQL, dbFailOnError
End If

 

[pbaldy]

This should help:

http://www.baldyweb.com/ImmediateWindow.htm

Colin didn't concatenate the variable into the SQL. I'd expect to see a list of fields in both clauses.

 

[rikklaney1]

INSERT INTO tempschedule SELECT TOP 9 FROM tbl_adjustedschedule WHERE ID>lngID ORDER BY ID


I though if the fields where the same in the tables you didn't need to list fields?

 

[pbaldy]

Did you see the part about concatenating the variable? Does the SQL look correct?

 

[isladogs]

Sorry about that - said there would be typos

Concatenation for lngID variable added in red
BUT as Paul also said, you need to add the fields that should be appended or * for all fields

For example, to append 2 fields called FieldName1 & FieldName2

INSERT INTO tempschedule (FieldName1, FieldName2) SELECT TOP " & Variable & " tbl_adjustedschedule.FieldName1, tbl_adjustedschedule.FieldName2 FROM tbl_adjustedschedule
 WHERE ID >[COLOR="Red"] " & lngID & "[/COLOR] ORDER BY ID

or to append all fields

INSERT INTO tempschedule SELECT TOP " & Variable & " tbl_adjustedschedule.* FROM tbl_adjustedschedule
 WHERE ID >[COLOR="Red"] " & lngID & "[/COLOR] ORDER BY ID

So if Variable = 9 and lngID = 12345 you would get

INSERT INTO tempschedule SELECT TOP 9 tbl_adjustedschedule.* FROM tbl_adjustedschedule
 WHERE ID >12345 ORDER BY ID

Hopefully no typos this time
If it still doesn't work, use Debug.Print strSQL to see the output ... always a good idea when testing

 

[Mark_]

As a question, why not simply have a query return the first 90 that are not "Processed" or otherwise marked as "Deleted"? This would allow you to have ONE table with a "DONE" or "DELETED" flag and one query that returns the top 90 that are not flagged.

Why have two tables for the same data?

 

[rikklaney1]

It's there. The fields was what I needed. Thanks gents.

 

[isladogs]

You're welcome.
Did you use Mark's suggestion as it's much better to avoid duplicating data