Error Runtime 3021 Delete 2 records different tables

  • Thread starter Thread starter Brandon Johnson
  • Start date Start date
B

Brandon Johnson

This one has got me stumped. I cant figure out this error for the life
of me. it goes through a listbox to see what index is indeed
selected... if selected then find that name in two seperate tables and
delete them. after that, reintailize the listbox to display updated
data. Any suggestions would help. thankyou.

For i = 0 To lstNames.ListCount - 1
If lstNames.Selected(i) = True Then
If chkBM.Value = True Then
cred = "[login_name] = '" & lstNames.ItemData(i) & "'"
status.FindFirst cred
user.FindFirst cred
If MsgBox("Are you sure you want to delete user: " &
status!login_name, _
vbYesNo, "Question") = vbYes Then
status.Delete 'ERROR LOCATION****************
user.Delete
lstNames.RowSource = "SELECT login_name FROM
tblLoginPWD ORDER _ BY login_name asc;"
txtNameCount = lstNames.ListCount
Else
MsgBox "User: " & user!login_name & " was NOT
deleted.", vbInformation, _ "Information"
Exit Sub
End If
End If
End If
Next
 
The error ("No current record.") implies that you're not actually finding a
record that matches your search criteria.

You need something like:

If Not status.NoMatch Then
status.Delete
End If
If Not user.NoMatch Then
user.Delete
End If
 
I have tried something like that before and found that it doesnt work.
ill will mix it up a little but another note on that is that i know for
sure that they are in there cuz i physically opened it up and found the
records that i want to delete. I will give this way a try again though.
thankyou
The error ("No current record.") implies that you're not actually finding a
record that matches your search criteria.

You need something like:

If Not status.NoMatch Then
status.Delete
End If
If Not user.NoMatch Then
user.Delete
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Brandon Johnson said:
This one has got me stumped. I cant figure out this error for the life
of me. it goes through a listbox to see what index is indeed
selected... if selected then find that name in two seperate tables and
delete them. after that, reintailize the listbox to display updated
data. Any suggestions would help. thankyou.

For i = 0 To lstNames.ListCount - 1
If lstNames.Selected(i) = True Then
If chkBM.Value = True Then
cred = "[login_name] = '" & lstNames.ItemData(i) & "'"
status.FindFirst cred
user.FindFirst cred
If MsgBox("Are you sure you want to delete user: " &
status!login_name, _
vbYesNo, "Question") = vbYes Then
status.Delete 'ERROR LOCATION****************
user.Delete
lstNames.RowSource = "SELECT login_name FROM
tblLoginPWD ORDER _ BY login_name asc;"
txtNameCount = lstNames.ListCount
Else
MsgBox "User: " & user!login_name & " was NOT
deleted.", vbInformation, _ "Information"
Exit Sub
End If
End If
End If
Next
Click to expand...
Click to expand...
 
Realistically, you'd probably be better off using SQL to delete the rows you
want.

Dim dbCurr As DAO.Database
Dim strSQL As String
Dim varItem As Variant

Set dbCurr = CurrentDb()

For Each varItem In lstNames.ItemsSelected
If MsgBox("Are you sure you want to delete user: " & _
lstNames.ItemData(varItem) & "?", vbYesNo, _
"Question") = vbYes Then
strSQL = "DELETE FROM Status " & _
"WHERE [login_name] = '" & lstNames.ItemData(varItem) & "'"
dbCurr.Execute strSQL, dbFailOnError
strSQL = "DELETE FROM User " & _
"WHERE [login_name] = '" & lstNames.ItemData(varItem) & "'"
dbCurr.Execute strSQL, dbFailOnError
Else
MsgBox "User: " & lstNames.ItemData(varItem) & _
" was NOT deleted.", vbInformation, "Information"
End If
Next varItem

Set dbCurr = Nothing



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Brandon Johnson said:
I have tried something like that before and found that it doesnt work.
ill will mix it up a little but another note on that is that i know for
sure that they are in there cuz i physically opened it up and found the
records that i want to delete. I will give this way a try again though.
thankyou
The error ("No current record.") implies that you're not actually finding a
record that matches your search criteria.

You need something like:

If Not status.NoMatch Then
status.Delete
End If
If Not user.NoMatch Then
user.Delete
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Brandon Johnson said:
This one has got me stumped. I cant figure out this error for the life
of me. it goes through a listbox to see what index is indeed
selected... if selected then find that name in two seperate tables and
delete them. after that, reintailize the listbox to display updated
data. Any suggestions would help. thankyou.

For i = 0 To lstNames.ListCount - 1
If lstNames.Selected(i) = True Then
If chkBM.Value = True Then
cred = "[login_name] = '" & lstNames.ItemData(i) & "'"
status.FindFirst cred
user.FindFirst cred
If MsgBox("Are you sure you want to delete user: " &
status!login_name, _
vbYesNo, "Question") = vbYes Then
status.Delete 'ERROR LOCATION****************
user.Delete
lstNames.RowSource = "SELECT login_name FROM
tblLoginPWD ORDER _ BY login_name asc;"
txtNameCount = lstNames.ListCount
Else
MsgBox "User: " & user!login_name & " was NOT
deleted.", vbInformation, _ "Information"
Exit Sub
End If
End If
End If
Next
Click to expand...
Click to expand...
Click to expand...
 
Back
Top