Delete multiple records in a subform

[June7]

Then this is very mysterious. I must confess I did not originate this code, although I have since done some modifications but only to the stuff that isn't relevant. The core code I posted is as was given to me.



Not sure it matters but the variable N does not need to be declared in header, declare it in the Sub. Another apology for that, I missed copy/paste of the Dim line - but it was not in the header in my example. I really can't see anything else in my code that pertains.

If you want to provide db for analysis, I will look at.

[gaker10]

Maybe this will help?

Private Sub frmEntrySub_Exit(Cancel As Integer)
'sets module variables starting values for use in deleting selected records
intHeight = Me.frmEntrySub.Form.SelHeight
intTop = Me.frmEntrySub.Form.SelTop
End Sub


Private Sub butDelete_Click()


Dim N As Integer        'counter for Delete function


With Me.frmEntrySub.Form.RecordsetClone
If .RecordCount < 1 Then
    MsgBox "No records have been saved.  Delete action cancelled.", , "DeleteRecord Error"
ElseIf intHeight < 1 Then
    MsgBox "No records have been selected.  Delete action cancelled.", , "DeleteRecord Error"
ElseIf MsgBox("This action may delete any saved data.  Proceed?", vbExclamation + vbOKCancel, "Delete Record?") = vbOK Then
   For N = 1 To intHeight
      .AbsolutePosition = intTop - 1    'AbsolutePosition property is 0 based counter so must -1 to get position within the recordset
      .MoveFirst
      CurrentDb.Execute "DELETE FROM EntryFormTable WHERE ID=" & !ID
      Me.frmEntrySub.Requery
   Next
End If
End With
End Sub

I would provide DB but it has thousands of company info records so :\ Making a copy and taking that out would leave nothing hehe.

[gaker10]

Here's something interesting:



I selected about 4 or 5 records. N is being read as 2, therefore the loop is done after 1 pass. That is why it is only deleting one record regardless of my selection. I should mention that it is deleting the top record on my list.

[June7]

What are intHeight and intTop set to?

If removing only confidential data (name, address, phone, ssn, email) leaves nothing, then dummy records are okay.

[gaker10]

0 on both even after I select multiple records

[June7]

Where is the 'Delete' button? My arrangement requires Delete button on main form. The intHeight and intTop variables are set when the subform is exited when clicking the button on main form.

[gaker10]

On the main form.

[June7]

Then all looks good. I can't help any more unless I can review and debug your file.

[gaker10]

Scratch that. I did some debugging and I THINK .MoveFirst is not doing what it is supposed to. The ID is always the top record regardless of where my first selection is.

[gaker10]

intTop and intHeight are storing correctly. The first record ID in this list was 31, when it should be looking at ID 34 as the first.

[June7]

The code uses Me.frmEntrySub.Form.Recordset instead of Me.frmEntrySub.Form.RecordsetClone. However, even after change code still won't work.

This is so frustrating. Cannot get the recordset to stay open past first iteration. I even tried declaring a recordset object without success.

If you want to view and test my code, can download db from https://app.box.com/shared/r8nea07sng

Hold down shift key while opening. Reset table links then open Login form and enter your initials (always start with the Login form but should not see it again). From Menu select Manage Samples. Select record 2014A-1181 in the datasheet list then click Edit Sample Information. The subform is in lower right. Explore code behind the Remove Tests button.

[gaker10]

It's not a huge deal right now. I'll just stick to my single record deletion function.

Something else I want to automate though is the resetting of the ID autonumber. I found out that when you delete your entire table and Compact and Repair your database, your ID starts over from 1. How do I attach this procedure to a button?

[ItsMe]

You can compact a DB by opening it and employing the "Compact" switch.

C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE "C:\FolderName\FileName.accdb" /compact

Good luck with your project.

[June7]

CurrentDb.Execute "DELETE FROM tablename"

Bing: Access VBA compact and repair

http://www.access-programmers.co.uk/...d.php?t=160025

[gaker10]

Yeah it's something I need to code and avoid using pathways because I'm not the only person who will be using this file, and I don't want to have to set up everyone's path on their computer.

Another thing is if I have a part entered with up to two alts beneath it, I want the Edit function to load them into the right fields. So I want the actual part to be loaded into the main fields on the left, the \ALT beneath it to be loaded into AltA fields, and the second \ALT (if its there) to be loaded into AltB fields.

Here is my edit code right now (only meant to edit one entry at a time upon selection and load into leftmost fields). Edit handles the button and entry loading, Add has an If to handle changes:

Private Sub butEdit_Click()


'check for data existence
    If Not (Me.frmEntrySub.Form.Recordset.EOF And Me.frmEntrySub.Form.Recordset.BOF) Then
        'Load existing entry into form
        With Me.frmEntrySub
            Me.txtAssembly = !Assembly
            Me.txtComponent = !Component
            Me.txtDescription = !Description
            Me.numAssemblyQty = !AssemblyQty
            Me.txtUOM = !UOM
            Me.txtAlternateA = !AlternateA
            Me.txtAlternateB = !AlternateB
            Me.numQtyA = !QtyA
            Me.numQtyB = !QtyB
            Me.numQtyC = !QtyC
            Me.dbPriceA = !UnitPriceA
            Me.dbPriceB = !UnitPriceB
            Me.dbPriceC = !UnitPriceC
'store ID in tag in case of change
            Me.ID.Tag = !ID
'change Add button caption
            Me.butAdd.Caption = "Update"
'disable Edit button
            Me.butEdit.Enabled = False
        End With
    End If
    
End Sub

Private Sub butAdd_Click()
'Add entry to list


'Insert vs Update options


'If the part is not in the list
    If Me.ID.Tag & "" = "" Then


'add the part to the list
    
    'Add main part
    CurrentDb.Execute "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, UnitPriceA, UnitPriceB, UnitPriceC) " & _
    " VALUES ('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "','" & Me.numAssemblyQty & "','" & Me.txtUOM & "','" & Me.numQtyA & "','" & _
    Me.numQtyB & "','" & Me.numQtyC & "','" & Me.dbPriceA & "','" & Me.dbPriceB & "','" & Me.dbPriceC & "')"
    
    
    'If there is an AlternateA, Add AlternateA
    If Not (Me.txtAlternateA & "" = "") Then
    CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
    " VALUES ('" & Me.txtAlternateA & "','" & Me.txtAltADescription & " \ALT" & "','" & Me.txtAltAUOM & "')"
    End If
    
    'If there is an AlternateB, Add AlternateB
    If Not (Me.txtAlternateB & "" = "") Then
    CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
    " VALUES ('" & Me.txtAlternateB & "','" & Me.txtAltBDescription & " \ALT" & "','" & Me.txtAltBUOM & "')"
    End If
    
'update an already existing part
    Else
    CurrentDb.Execute "UPDATE EntryFormTable " & _
        " SET Assembly='" & Me.txtAssembly & "'" & _
        ", Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", AssemblyQty='" & Me.numAssemblyQty & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", QtyA='" & Me.numQtyA & "'" & _
        ", QtyB='" & Me.numQtyB & "'" & _
        ", QtyC='" & Me.numQtyC & "'" & _
        ", PriceA='" & Me.dbPriceA & "'" & _
        ", PriceB='" & Me.dbPriceB & "'" & _
        ", PriceC='" & Me.dbPriceC & "'" & _
        " WHERE ID='" & Me.frmEntrySub!ID & "'"
        
        CurrentDb.Execute "UPDATE EntryFormTable " & _
        " SET AlternateA='" & Me.txtAlternateA & "'" & _
        ", Description='" & Me.txtAltADescription & "'" & _
        ", UOM='" & Me.txtAltAUOM & "'" & _
        " WHERE ID='" & Me.frmEntrySub!ID & "'"
        
        CurrentDb.Execute "UPDATE EntryFormTable " & _
        " SET AlternateB='" & Me.txtAlternateB & "'" & _
        ", Description='" & Me.txtAltBDescription & "'" & _
        ", UOM='" & Me.txtAltBUOM & "'" & _
        " WHERE ID='" & Me.frmEntrySub!ID & "'"
    End If
    
'Clear form after add/update
    butClear_Click


'Refresh form
    frmEntrySub.Form.Requery


End Sub