Delete Multiple Records On A Form

[busterbaxter]

I have an existing inventory database that I'm trying to make changes to. The database only has 1 table with the following fields:

PartNumber
ItemDescription
Category
Location
Owner
SerialNumber.

Currently I'm using the delete button to delete a single item.

Private Sub cmdDeleteRecord_Click()
On Error GoTo Err_cmdDeleteRecord_Click
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
 
     DoCmd.RunCommand acCmdDeleteRecord
    RunCommand acCmdSelectRecord
Exit_cmdDeleteRecord_Click:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
            Exit Sub
 
Err_cmdDeleteRecord_Click:
 If Err.Number <> 3021 Then
MsgBox Err.Description
End If
   ' If Err = 3021 Then 'No current record
   ' MsgBox "Deletion Successful"
 
'Exit Sub
 '   Else
  '  MsgBox Err.Description
   ' Resume Exit_cmdDeleteRecord_Click
'End If
 
    Resume Exit_cmdDeleteRecord_Click
 
End Sub

However this is not really efficient if I have to delete 300 disk drives. I have the multiple add working and I'd like to put in a delete. I'd like the delete to first search the 'SerialNumber' field and if it's blank then allow the delete multiple copies.

Is this possible?


Thanks,

Kevin

[wassimdaccache]

Hi man

I think you have to put all your data in rercodset and check it.

 
 
dim mysql as string 
dim db as database
dim rs as recordset 
 
 mysq = "select * from [yourtable] ;"
Set db = CurrentDb()
Set rs = db2.OpenRecordset(mysq2, dbOpenDynaset)
rs.movefirst
While Not (rs.BOF Or rs.EOF) 
if(SerialNumber="")then 
rs.delete
endif 
rs.MoveNext
Wend  
docmd.requery 
 

regards

[busterbaxter]

Hi,

Thanks for the fast response. I put the code in but I get a compile error at 'Dim db As Database' (Compile error: user-defined type not defined). I checked ?CurrentProject.Connection and it came back with Provider=Microsoft.ACE.OLEDB.12.0. I also tried DAO.Database and DAO.Recordset but I still get the same error.

Thanks,

Kevin

[DonRayner]

Hi,

Thanks for the fast response. I put the code in but I get a compile error at 'Dim db As Database' (Compile error: user-defined type not defined). I checked ?CurrentProject.Connection and it came back with Provider=Microsoft.ACE.OLEDB.12.0. I also tried DAO.Database and DAO.Recordset but I still get the same error.

Thanks,

Kevin

You don't have the reference set for Microsoft DAO Object Library.

[busterbaxter]

You don't have the reference set for Microsoft DAO Object Library.

Thanks Don,

How do I get it?

Kevin

[DonRayner]

Thanks Don,

How do I get it?

Kevin

When you are in the VBA programing screen of any module select Tools - References on the menu bar. Look for the Microsoft DAO..... and select the checkbox next to it

[busterbaxter]

Thanks for your help all,

I was wondering if my Delete can work like my Copy,

Private Sub cmdMultiCopy_Click()
Dim Copies as Integer

If Me.Dirty Then Me.Dirty = False

If Not IsNull(Me.NumberCopies) then
Copies = Me.NumberCopies
Else
Copies = 1
End If

For I = 1 To Copies
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
Next I

End Sub


My copy reads the 'NumberCopies' text box on the form. Then takes the integer and copies that many records to the database. Is there any way to get the delete to do the same. I have a text box on my form 'NumberDeleted'.

Thanks,

Kevin

[mshmyob]

Have you tried deleting with a simple SQL command like so?

DoCmd.SetWarnings False 
strSQL = "DELETE FROM yourtable WHERE yourFieldName IS NULL"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True 
 

cheers,

[busterbaxter]

Thanks for everybody's help,

but I'm using an inventory system. On the form there is a list of all the items in the database. I can not just delete all items without a serial number. The user selects a record from the list, and the form brings up the record and all of it's fields. I'd like the user to input a qty from a text box and the Delete button read that qty and then delete. Here is what I have so far.

Asset = Asset - Data

criteria = "! isnull(serial) and asset=" & asset

if ! isnull(user) then
criteria = criteria & " and user=" & user-datea
end if

for i = 1 to copies

rs.findfirst criteria

if rs.NoMatch then
msgbox "Deleted only" & i-1 & " records!"
fulldelete=false
exit for
end if

rs.delete

next i

if ! fulldelete msgbox copies & " Records Deleted!"

I'm not sure what the syntax for the criteria should be.

Thanks,

Kevin

[DonRayner]

Thanks for everybody's help,

but I'm using an inventory system. On the form there is a list of all the items in the database. I can not just delete all items without a serial number. The user selects a record from the list, and the form brings up the record and all of it's fields. I'd like the user to input a qty from a text box and the Delete button read that qty and then delete. Here is what I have so far.

Asset = Asset - Data

criteria = "! isnull(serial) and asset=" & asset

if ! isnull(user) then
criteria = criteria & " and user=" & user-datea
end if

for i = 1 to copies

rs.findfirst criteria

if rs.NoMatch then
msgbox "Deleted only" & i-1 & " records!"
fulldelete=false
exit for
end if

rs.delete

next i

if ! fulldelete msgbox copies & " Records Deleted!"

I'm not sure what the syntax for the criteria should be.

Thanks,

Kevin

I'm not realy sure what you're after here. It sounds like you bring up the record for and item which has a certain quanity and you want to adjust that quanity by the ammount that is entered into a text box.

[busterbaxter]

Hi Don,

I am pulling up a record, but each record is a quantity of 1 because most of my records have serial numbers. However we do have parts that do not have serial numbers. Each record represents 1 item in inventory. I have a text box on my form where the user can enter a qty to delete. So first the user selects the item (item without a serial number), then enters a qty in a text box, then hits the delete button on the form. The code I left there is my pseudo code.

Thanks,

Kevin

[DonRayner]

Hi Don,

I am pulling up a record, but each record is a quantity of 1 because most of my records have serial numbers. However we do have parts that do not have serial numbers. Each record represents 1 item in inventory. I have a text box on my form where the user can enter a qty to delete. So first the user selects the item (item without a serial number), then enters a qty in a text box, then hits the delete button on the form. The code I left there is my pseudo code.

Thanks,

Kevin

Ok, I get it now. So if you have 50 widgets without serial numbers, the widgets are in your db 50 times. You want to be able to bring up the record for 1 widget and using that delete x number of widgets from the db. :)

So the criteria in your code should be to match part number and serial number, step through the recordset until you have matched and deleted x number of records then exit the code.

[busterbaxter]

Thanks Don,

Can you give me the code for this?

Kevin

[DonRayner]

Thanks Don,

Can you give me the code for this?

Kevin

Try this Kevin

Replace Field1 with the name of your Part or ID field
Replace Field2 with the name of your Serial Number field
Replace YourTable with the name of your table containg the records
Change Control1 to name of part or id control
Change Contorl2 to name of serial number control
Change Control 3 to name of delete quanity control
Change YourButton to the name of your delete button

Private Sub YourButton_Click()
On Error GoTo ErrPoint
 
Dim FormPart as string, FormSer as string, FormQty as integer
FormPart = me.Control1
FormSer = me.Control2
FormQty = me.Control3
 
Dim db as dao.database, rs as dao.recordset
set db = currentdb()
set rs = db.openrecordset("YourTable")
rs.movefirst
Do While Not rs.eof and FormQty > 0
    If rs!Field1 = FormPart and rs!Field2 = FormSer then
         rs.delete
         FormQty = FormQty - 1
    End If
    rs.movenext
Loop
If rs.eof and FormQty > 0 then
    msgbox "You have attempted to delete more records than are available" _
    & vbcrlf & (me.Control3 - FormQty) & " records have been deleted"
Else
    msgobx me.Control3 & " records have been deleted"
End If
rs.close
set rs = nothing
set db = nothing
 
ExitPoint:
    Exit Sub
 
ErrPoint:
    Msgbox err.number & " " & err.description
    resume ExitPoint
 
End Sub
 

[busterbaxter]

Try this Kevin

Replace Field1 with the name of your Part or ID field
Replace Field2 with the name of your Serial Number field
Replace YourTable with the name of your table containg the records
Change Control1 to name of part or id control
Change Contorl2 to name of serial number control
Change Control 3 to name of delete quanity control
Change YourButton to the name of your delete button

Private Sub YourButton_Click()
On Error GoTo ErrPoint
 
Dim FormPart as string, FormSer as string, FormQty as integer
FormPart = me.Control1
FormSer = me.Control2
FormQty = me.Control3
 
Dim db as dao.database, rs as dao.recordset
set db = currentdb()
set rs = db.openrecordset("YourTable")
rs.movefirst
Do While Not rs.eof and FormQty > 0
    If rs!Field1 = FormPart and rs!Field2 = FormSer then
         rs.delete
         FormQty = FormQty - 1
    End If
    rs.movenext
Loop
If rs.eof and FormQty > 0 then
    msgbox "You have attempted to delete more records than are available" _
    & vbcrlf & (me.Control3 - FormQty) & " records have been deleted"
Else
    msgobx me.Control3 & " records have been deleted"
End If
rs.close
set rs = nothing
set db = nothing
 
ExitPoint:
    Exit Sub
 
ErrPoint:
    Msgbox err.number & " " & err.description
    resume ExitPoint
 
End Sub
 

Thanks Don for all of your help!

[DonRayner]

Glad I could help

Don