Deleting a record with code

[Mark]

Hi all,

I have a form with a command button, which allows the user
to delete records based on the form's control source table
(tableNurseLogins). Basically, the use selects a record
from a pull down list (combo box: "NurseLogins") and then
clicks on the command button to delete the selected record.

My problem and question is: everything is almost working
perfectly... when the record is deleted... the data in the
record is deleted, but the record itself is not (the
record count should be one less, but it is not fully
deleting the record only the data within the record).
Below is the code that I am using. How can I get the
record to be fully deleted? Thanks for the help in
advance!!!!


Private Sub cmdDeleteInfo_Click()

Dim rst As ADODB.Recordset
Dim intCounter As Integer

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "Select * from tableNurseLogins"


intCounter = 0

Do Until rst.EOF
If rst!NurseLogins = Me.NurseLogins Then
rst.Delete
intCounter = intCounter + 1
End If

If Not rst.EOF Then
rst.MoveNext
End If
Loop

Debug.Print intCounter & " Name Deleted"

rst.Close
Set rst = Nothing

End Sub

 

[John Vinson]

How can I get the
record to be fully deleted?

I suspect it IS being deleted. Just Refresh the form after the
deletion so that the form's aware of that fact.

 

[Dirk Goldgar]

Hi all,

I have a form with a command button, which allows the user
to delete records based on the form's control source table
(tableNurseLogins). Basically, the use selects a record
from a pull down list (combo box: "NurseLogins") and then
clicks on the command button to delete the selected record.

My problem and question is: everything is almost working
perfectly... when the record is deleted... the data in the
record is deleted, but the record itself is not (the
record count should be one less, but it is not fully
deleting the record only the data within the record).
Below is the code that I am using. How can I get the
record to be fully deleted? Thanks for the help in
advance!!!!


Private Sub cmdDeleteInfo_Click()

Dim rst As ADODB.Recordset
Dim intCounter As Integer

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "Select * from tableNurseLogins"


intCounter = 0

Do Until rst.EOF
If rst!NurseLogins = Me.NurseLogins Then
rst.Delete
intCounter = intCounter + 1
End If

If Not rst.EOF Then
rst.MoveNext
End If
Loop

Debug.Print intCounter & " Name Deleted"

rst.Close
Set rst = Nothing

End Sub

In all probability the record is being deleted, but the form doesn't
know it yet, and won't until it is refreshed or requeried. May I
suggest, though, that executing a delete query to delete the record or
records you want to delete would be much more efficient than opening a
recordset for the sole purpose of deleting the records in it?