delete first 15 records

  • Thread starter Thread starter JIM.H.
  • Start date Start date
J

JIM.H.

Hello,
I have a delete record button in my from. I want to get
it work for first 15 records in the table. How can I do
that?
Thanks
Jim.
 
Use 2 queries. Create a Select query ordered by the field you want to
determine which are the "top" records. Set the Top value for the query to
15. Create a delete query and use the query you just made as the source for
the delete query. Delete all records.

One thing about Top, is that it will return more than 15 records if there is
a tie for the 15th place.

Query1
SELECT TOP 15 Table1.*
FROM Table1
ORDER BY Table1.Field1;

Delete Query
DELETE *
FROM Query1;
 
How can I make 15 dynamic. I need to get that number from
form interface and let user click a delete button.
 
Well, the query would be dynamic. Are you wanting the user to be able to
select certain records to be deleted then delete the selected records when
the user presses a button? If so, create an additional field in the record,
perhaps called DeleteMe. Make this a Yes/No field and have the user check
the checkbox on the form when that record is to be deleted. When they click
the button, run a delete query that deletes all records where DeleteMe=True.
 
Thanks Wayne,
I may have problems if I change the table structure. I
have an auto form created from this table and one delete
button to delete record current record. Is there any other
way to delete some records from the beginning. Can I get
that delete button work for more than record?
Thanks,
Jim.
 
If the form is in continuous view you could select as many records as you
like and delete them. Another option would be to put a loop in the click
code of the button with a counter.

For i = 1 to 15
CurrentDb.Execute "DELETE * From NameOfForm'sRecordSource WHERE [ID
Field]=" & Me.txtID & ";"
Me.Recordset.MoveNext
Me.Requery
Next i

This will delete the first 15 records in the form in the order they appear
in the form. You may also want to disable the button or pop-up a message box
so that the user can't click twice in a row or you'll delete 30 records.
 
p.s.

This will actually run from which ever record you're currently on. You may
want to do a MoveFirst first to start at the first record. Also, you may
want to check for no records in case you delete the last one before the loop
is done.
 
Hello,
I used this:
' delete records
DoCmd.SetWarnings False
For numOfRecords = 1 To 5
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70
Me.Recordset.MoveNext
Me. Requery
Next numOfRecords
DoCmd.SetWarnings True
It failed at the line of Me.Recordset.MoveNext, if I do
not use this line, it is still working. Without recordset
line, it is deleting from the top, if I can get it delete
from the current records to the down, that will actually
be more helpful.
Thanks,
Jim.
 
I added the Requery after I originally typed the message and I put it in the
wrong place, it should go outside the loop. If Me.Recordset.MoveNext
generates an error you are probably using Access 97 or older (it wasn't
supported) or Access 2000 or newer without a Reference set to DAO.

If the Requery gets moved below the loop, you should be able to start the
delete from whichever record you're currently on.
 
Back
Top