Solved Please help-subform multiple selects record and delete

U

uncleh

Member
Local time
Tomorrow, 00:30
Joined
Mar 1, 2020
Messages
31
hi, all,
I have a strange problem. I continuously select records in subform and then press button to delete, but the deleted records are not that I have selected.
ie.
i selected record no.1 to 3. will delete 1 and 3 skip 2
i selected record no.1 to 5. will delete 1,3,5,7,8 skip 2,4, Plus7,8
please see what is the problem.

thank you!
 

Attachments

Which button - keyboard DELETE?

I was getting error "A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control". Had to rebuild subform.
 
June7 said:
Which button - keyboard DELETE?

I was getting error "A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control". Had to rebuild subform.
Click to expand...
thank you for reply. there has a button on main form
 
uncleh said:
please see what is the problem.
Click to expand...
Code: 
Private Sub Btn_Delete_Click()
''''''I had to recreate the form, also added a counter type field for tracking
Dim I As Integer
If LineNo = 0 Then MsgBox "No Record Selected !": Exit Sub
If MsgBox("Confirm Delete " & LineNo & " Sizes ?", vbQuestion + vbDefaultButton2 + vbYesNo, "Confirm Delete") = vbYes Then
    Me!frm220325.Form.AllowAdditions = False
''''''they are always deleted in reverse order''
    For I = LineNo + TopLine - 1 To TopLine Step -1
    Debug.Print I; Me!frm220325.Form.QNo
        Me!frm220325.Form.SelTop = I
        DoCmd.SetWarnings False
        Me!frm220325.SetFocus
        RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
    Next
End If
Me!frm220325.Form.Requery
TopLine = 0
LineNo = 0
End Sub
 
uncleh said:
please see what is the problem
Click to expand...
There is no field with a unique record ID in the "Temp_Quotation" table
Code: 
Private Sub Form_Load()
Dim sVal$, StartNo&
Dim rst As DAO.Recordset
  
    CurrentDb.Execute "DELETE * FROM Temp_Quotation"

    sVal = "INSERT INTO Temp_Quotation ( RbSize, TTID, Qty, S_Curr, Grade ) " & vbCrLf & _
        "SELECT RbSize, 3 AS TTID, '-' AS Qty, 1 AS S_Curr, '500B' AS Grade " & vbCrLf & _
        "FROM usysRbStdSize"
    CurrentDb.Execute sVal
  
'Setting unique record ID in the "Temp_Quotation" table
    Set rst = CurrentDb.OpenRecordset("Temp_Quotation", dbOpenDynaset)
    With rst
        Do Until .EOF = True
            StartNo = StartNo + 1
            .Edit
                !RecID = StartNo
            .Update
            .MoveNext
        Loop
    End With

    rst.Close
    Set rst = Nothing
  
    Me.frmRbQuotation_Sub.Form.Requery
End Sub
Private Sub Btn_Delete_Click()
Dim I As Integer, sVal$
    If LineNo = 0 Then MsgBox "No Record Selected !": Exit Sub
    If MsgBox("Confirm Delete " & LineNo & " Sizes ?", vbQuestion + vbDefaultButton2 + vbYesNo, "Confirm Delete") = vbYes Then
        Me.frmRbQuotation_Sub.Form.AllowAdditions = False
  
        For I = TopLine To LineNo + TopLine - 1
            Me.frmRbQuotation_Sub.Form.SelTop = I

'            Wrong way:
'            DoCmd.SetWarnings False
'            Me.frmRbQuotation_Sub.SetFocus
'            RunCommand acCmdDeleteRecord
'            DoCmd.SetWarnings True
          
            sVal = sVal & ", " & Me.frmRbQuotation_Sub.Form!RecID
        Next
    End If
  
    If Len(sVal) > 2 Then
        sVal = Mid(sVal, 3)
        'Deleting selected records :
        sVal = "DELETE * FROM Temp_Quotation WHERE RecID IN (" & sVal & ")"
        CurrentDb.Execute sVal
        Me.frmRbQuotation_Sub.Form.Requery
    End If
  
    TopLine = 0
    LineNo = 0
End Sub
 

Attachments

Last edited:
you do not need public variable Topline, LineNo.
always add Autonumber field to your tables.
 

Attachments

Eugene-LS said:
There is no field with a unique record ID in the "Temp_Quotation" table
Code: 
Private Sub Form_Load()
Dim sVal$, StartNo&
Dim rst As DAO.Recordset
  
    CurrentDb.Execute "DELETE * FROM Temp_Quotation"

    sVal = "INSERT INTO Temp_Quotation ( RbSize, TTID, Qty, S_Curr, Grade ) " & vbCrLf & _
        "SELECT RbSize, 3 AS TTID, '-' AS Qty, 1 AS S_Curr, '500B' AS Grade " & vbCrLf & _
        "FROM usysRbStdSize"
    CurrentDb.Execute sVal
  
'Setting unique record ID in the "Temp_Quotation" table
    Set rst = CurrentDb.OpenRecordset("Temp_Quotation", dbOpenDynaset)
    With rst
        Do Until .EOF = True
            StartNo = StartNo + 1
            .Edit
                !RecID = StartNo
            .Update
            .MoveNext
        Loop
    End With

    rst.Close
    Set rst = Nothing
  
    Me.frmRbQuotation_Sub.Form.Requery
End Sub
Private Sub Btn_Delete_Click()
Dim I As Integer, sVal$
    If LineNo = 0 Then MsgBox "No Record Selected !": Exit Sub
    If MsgBox("Confirm Delete " & LineNo & " Sizes ?", vbQuestion + vbDefaultButton2 + vbYesNo, "Confirm Delete") = vbYes Then
        Me.frmRbQuotation_Sub.Form.AllowAdditions = False
  
        For I = TopLine To LineNo + TopLine - 1
            Me.frmRbQuotation_Sub.Form.SelTop = I

'            Wrong way:
'            DoCmd.SetWarnings False
'            Me.frmRbQuotation_Sub.SetFocus
'            RunCommand acCmdDeleteRecord
'            DoCmd.SetWarnings True
          
            sVal = sVal & ", " & Me.frmRbQuotation_Sub.Form!RecID
        Next
    End If
  
    If Len(sVal) > 3 Then
        sVal = Mid(sVal, 3)
        'Deleting selected records :
        sVal = "DELETE * FROM Temp_Quotation WHERE RecID IN (" & sVal & ")"
        CurrentDb.Execute sVal
        Me.frmRbQuotation_Sub.Form.Requery
    End If
  
    TopLine = 0
    LineNo = 0
End Sub
Click to expand...
Thank you very much! BUT if i Select 1 record, i cant be delete
 
arnelgp said:
you do not need public variable Topline, LineNo.
always add Autonumber field to your tables.
Click to expand...

arnelgp, It's great. The problem is finally solved. You help solve my problem every time. You're the best. Thank you!​

 
uncleh said:
BUT if i Select 1 record, i cant be delete
Click to expand...
Sorry - that was my negligence and oversight.
Please replace line with :
Code: 
    If Len(sVal) > 3 Then
to:
Code: 
    If Len(sVal) > 2 Then
...
Post #5 - Fixed!
 
Last edited:
Eugene-LS said:
Sorry - that was my negligence and oversight.
Please replace line with :
Code: 
    If Len(sVal) > 3 Then
to:
Code: 
    If Len(sVal) > 2 Then
...
Post #5 - Fixed!
Click to expand...
yeh!!There is another way to solve the problem. Thank you!!
 
uncleh said:
I have change to If Len(sVal) >= 1 Then....can be delete if selete 1 record only
Click to expand...
I think it doesn't make sense because the line:
Code: 
            sVal = sVal & ", " & Me.frmRbQuotation_Sub.Form!RecID
Adds at least 3 characters per each pass ...
 
You must log in or register to reply here.

Similar threads

S
Solved Second SQl delete query raises runtime error 424
Replies
8
Views
353
Pat Hartman
P
T
How do I code delete a record from a combo Box with a button
2
Replies
37
Views
1,596
Tom d
T
oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
822
oxicottin
oxicottin
T
Update record selected in a comboBox from a button
Replies
9
Views
390
Tom d
T
C
Solved No Current Record on Access Close
Replies
11
Views
839
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom