the current code works find on a single selection in the listbox, but I get an error"No Current Record" with a multi-selection.
Private Sub cmdDeleteSelect_Click()
Dim db As DAO.Database
Dim rs, rs1, rs2 As DAO.Recordset
Dim strSQL, strSQL_1, strSQL2, strSQL3, nJG As String
Dim lngCount, lngLoop, lngLoop1, lngID, lngID2, nSRCtr As Long
Dim fld As DAO.Field
Dim varNumber As Variant
On Error GoTo Err_cmdDeleteSelect_Click
Select Case MsgBox("ARE YOU SURE YOU WANT TO DELETE THE SELECTED RECORDS?", _
vbYesNoCancel Or vbExclamation Or vbDefaultButton1, _
"Deleting the Selected Records")
Case vbYes
If lstSRTemp.ItemsSelected.Co
unt = 0 Then
MsgBox "You must select at least one SR item to be Deleted."
Exit Sub
Else:
varNumber = lstSRTemp.ItemsSelected.Co
unt
nSRCtr = 0
Set db = CurrentDb
Set rs = db.OpenRecordset("TA_SRArc
hiveData",
dbOpenDynaset)
For varNumber = IIf(Me.lstSRTemp.ColumnHea
ds, 1, 0) To Me.lstSRTemp.ListCount - 1
If nSRCtr = lstSRTemp.Column(0) Then
GoTo cont2:
Else
nSRCtr = lstSRTemp.Column(0)
nJG = lstSRTemp.Column(1)
strSQL = "SELECT top 1 * FROM TA_SR where Job_Group =" & Chr(34) & nJG & Chr(34) & ""
strSQL_1 = "SELECT Max(Job_Group) AS MaxOfJob_Group" & _
" FROM TA_SRArchiveData " & _
" ORDER BY Max(Job_Group) DESC"
Set rs1 = CurrentDb.OpenRecordset(st
rSQL_1)
Set rs2 = CurrentDb.OpenRecordset(st
rSQL)
lngID2 = rs1.Fields("MaxOfJob_Group
").Value
With rs2
If rs2.Fields("SubmittedSR").
Value = True Then
Select Case _
MsgBox("You CANNOT delete this record, due to being previously submitted. Do you wish to create a copy of this record for a new SR?", _
vbYesNo Or vbExclamation Or _
vbDefaultButton1, _
"Deleting Previously Submitted")
Case vbYes
cmdCopyNewRec_Click
Case vbNo
GoTo Exit_cmdDeleteSelect_Click
:
End Select
End If
If rs2.Fields("Job_Group").Va
lue = lngID2 Then
lngID = 0
End If
lngCount = .RecordCount
If lngCount > 0 Then
While .EOF = False
For lngLoop = 1 To lngCount
With rs
.AddNew
If lngID = 0 Then
lngID = rs2.Fields("SRctr").Value
End If
For Each fld In rs.Fields
With fld
If .Attributes And dbAutoIncrField Then
GoTo NextCont:
End If
rs.Fields(.Name).Value = rs2.Fields(.Name).Value
End With
NextCont: Next
.Update
End With
If lngLoop = lngCount Then
'GoTo cont1:
End If
Next
.MoveNext
Wend
End If
End With
strSQL2 = "SELECT * FROM tblEquipListingPerJobGroup
where Job_Group =" & Chr(34) & nJG & Chr(34) & ""
strSQL3 = "SELECT * FROM TBL_EquipList_ARCHIVE"
Set rs1 = CurrentDb.OpenRecordset(st
rSQL2)
Set rs2 = CurrentDb.OpenRecordset(st
rSQL3)
rs1.MoveLast
lngCount = rs1.RecordCount
rs1.MoveFirst
With rs1
If rs1.Fields("Job_Group").Va
lue = lngID2 Then
lngID = 0
End If
If lngCount > 0 Then
Do Until rs1.EOF
With rs2
.AddNew
For Each fld In rs1.Fields
With fld
If .Attributes And dbAutoIncrField Then
GoTo NextCont1:
End If
rs2.Fields(.Name).Value = rs1.Fields(.Name).Value
End With
NextCont1: Next
.Update
End With
lngLoop1 = lngLoop1 + 1
If lngLoop1 = lngCount Then
GoTo cont1:
End If
rs1.MoveNext
Loop
cont1: End If
End With
strSQL = "Select * from TA_SR WHERE Job_Group = " & Chr(34) & nJG & Chr(34) & ""
Set rs = CurrentDb.OpenRecordset(st
rSQL, dbOpenDynaset)
If Not rs.NoMatch Then
rs.Delete
End If
strSQL = "Select * from tblEquipListingPerJobGroup
WHERE Job_Group = " & Chr(34) & nJG & Chr(34) & ""
Set rs = CurrentDb.OpenRecordset(st
rSQL, dbOpenDynaset)
If Not rs.NoMatch Then
rs.MoveLast
lngCount = rs.RecordCount
rs.MoveFirst
With rs
If rs.Fields("Job_Group").Val
ue = lngID2 Then
lngID = 0
End If
If lngCount > 0 Then
Do Until rs.EOF
rs.Delete
lngLoop1 = lngLoop1 + 1
If lngLoop1 = lngCount Then
GoTo cont2:
End If
rs.MoveNext
Loop
End If
cont2: End With
End If
strSQL = "Select * from tblSRListTemp WHERE Job_Group = " & Chr(34) & nJG & Chr(34) & ""
Set rs = CurrentDb.OpenRecordset(st
rSQL, dbOpenDynaset)
If Not rs.NoMatch Then
rs.MoveLast
lngCount = rs.RecordCount
rs.MoveFirst
With rs
If rs.Fields("Job_Group").Val
ue = lngID2 Then
lngID = 0
End If
If lngCount > 0 Then
Do Until rs.EOF
rs.Delete
lngLoop1 = lngLoop1 + 1
If lngLoop1 = lngCount Then
GoTo cont2:
End If
rs.MoveNext
Loop
End If
End With
End If
rs.Close
db.Close
End If
Next varNumber
Set db = Nothing
Set rs = Nothing
Set rs1 = Nothing
Set rs2 = Nothing
Me.lstSRTemp.Requery
Me.FilterSub.Requery
End If
Case vbNo
GoTo Exit_cmdDeleteSelect_Click
:
Case vbCancel
GoTo Exit_cmdDeleteSelect_Click
:
End Select
Exit_cmdDeleteSelect_Click
:
Exit Sub
Err_cmdDeleteSelect_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteSelect_Click
End Sub