Using a recordset to delte a record within access

[snagrat]

I am having problems using a recordset to find and delete a record. The finding of the record is ok, which is then displayed in the relevant txt boxes, but the delete part does not work.
Here is the code........

Option Compare Database

Private Sub Command40_Click() 'find record button
Dim FirstName As String
Dim Surname As String
Dim Address As String
Dim Postcode As String
Dim TelephoneNumber As String
Dim CustomerID As String
Dim Job As String
Dim JobID As String
Dim Paid As Boolean
Dim DBS As Database
Dim RST As Recordset
Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("Customers")
Surname = InputBox("Which Name Are You Searching For")
Found = False
RST.MoveFirst
Do
If Surname = RST!Surname Then

txtFirstName = RST!FirstName
txtSurname = RST!Surname
txtCustomerID = RST!CustomerID
txtAddress = RST!Address
txtPostCode = RST!Postcode
txtTelephoneNumber = RST!TelephoneNumber
txtJob = RST!Job
txtJobID = RST!JobID
txtPaid = RST!Paid
Found = True
Else
RST.MoveNext
End If
Loop Until Found = True Or RST.EOF
If RST.EOF Then
MsgBox "No Such Record"
End If
End Sub

Private Sub Command41_Click() ' delete record button
Dim DBS As Database
Dim RST As Recordset
Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("Customers")

RST.Edit
RST!FirstName = ""
RST!Surname = ""
RST!CustomerID = ""
RST!Address = ""
RST!Postcode = ""
RST!TelephoneNumber = ""
RST!Job = ""
RST!JobID = ""
RST!Paid = ""
'Found = True
RST.Update
End Sub

IF ANYONE CAN HELP I WOULD BE MOST APPRIECIATIVE!!
THANKS

 

[Mile-O]

Private Sub Command41_Click() ' delete record button
    Dim DBS As Database
    Dim RST As Recordset
    Set DBS = CurrentDb
    Set RST = DBS.OpenRecordset("Customers")

    RST.Edit
    RST!FirstName = ""
    RST!Surname = ""
    RST!CustomerID = ""
    RST!Address = ""
    RST!Postcode = ""
    RST!TelephoneNumber = ""
    RST!Job = ""
    RST!JobID = ""
    RST!Paid = ""
    'Found = True
RST.Update
End Sub

Snagrat, variables declared within a private sub are specific only to that subroutine.

The part that I have quoted can be simplified to:

Dim DBS As Database
Dim RST As Recordset
Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("Customers")
RST.Delete
RST.Close

The only problem is: because you have the delete on a different subroutine from the part where the record to delete is found, it doesn't know which record you are trying to find.

 

[Mile-O]

This is where I suggest you use a RecordsetClone and store a Bookmark in a form-level variant.

 

[Mile-O]

Dim FirstName As String 
Dim Address As String 
Dim Postcode As String 
Dim TelephoneNumber As String 
Dim CustomerID As String 
Dim Job As String 
Dim JobID As String 
Dim Paid As Boolean

Another thing, you have dimensioned all these variables and have not used them - you can delete these lines.

I also notice that you have not put Option Explicit at the top of your module - this will enforce you to declare variables you use in code as I see you haven't dimensioned Found but use it as a boolean.

 

[snagrat]

Thanks Very much
that seems to have cleared things up nicely!!