recordset delete method

[ML]

This is most bizarre.

I have tried to delete records (in an Access database) in my project several different ways using querydefs, SQL DELETE statements and finally I wrote a function to delete a record resulting in a recordset from a SELECT statement using the rstVariable.delete method. I know all of my different attempts should have worked but none of them have. Stepping through the code, checking variables, everything is fine, the code runs without errors. It pretends to delete the record!!! But when I return to Access or end the project and try again the record is still there.

In the error handler routine for the function, if the function is successful, it exits the function.

It works every time unless I try again in the same session. Obviously, it deletes the recordset record but doesn't update the database. I know it should work!!! There is nothing wrong.

Finally in desperation, I pasted the code into a new project, invoked the function from a command button and it worked - the exact same code! It has to be something different between the 2 projects, what can I look for?

What is wrong? I cannot figure this one out -please help, it has been a week of writing and rewriting.

[Clunietp]

lets see some code!

[ML]

Clunietp

Here it is. I set the variable dblNumber as a known number from the dbs for testing but normally it would be captured from user input. Also, I used the full path of the mdb here but it would normally be the app.path.

Function DeletePatient() As Integer
Dim rstName As Recordset
Dim strSQLQuery As String
Dim dbs As Database
Dim strName As String
Dim dblNumber As Double

On Error GoTo ErrorHandler

dblNumber = 9818232
strSQLQuery = "SELECT * FROM Patient WHERE Number = " & dblNumber
Set dbs = OpenDatabase("F:\Projects\Sept 99 Build\accident.mdb")
Set rstName = dbsAccident.OpenRecordset(strSQLQuery, dbOpenDynaset)

' If no records in Patient table, exit.
If rstName.EOF Then Exit Function

rstName.MoveFirst
rstName.Delete
rstName.Close

ErrorHandler:
Select Case Err
Case 0
DeletePatient = SUCCESS
Exit Function
Case Else
MsgBox "Error " & Err & ": " & _
Error, vbOKOnly, "ERROR"
DeletePatient = FAILED
Exit Function
End Select

End Function

[Clunietp]

You were right the first time. It is most bizarre.

The only thing I can think of that would be different between projects are the project references, and if you're using a data control (doesn't look like it), those properties.

good luck!

Tom

[ML]

Hi Tom & ChuckDeb,

I goofed big time. My app is complete with splash screen, login and menu. The menu options include add new, edit existing and delete existing records from the access database.

When editing or adding new records, the user goes through a series of forms. In the menu form code, I start a session with a workspace object and use the BeginTrans method so that the user can choose to rollback if necessary. At the end of the form series, the transaction is committed.

However, when deleting, the user chooses the name or report number in a dialog box and is warned that the record is being deleted. I simply FORGOT to commit the transaction in the dialog box code and therefore, the item was deleted from the open recordset but not from the database.

Man, I feel silly. Thanks anyway for attempting to help.

Regards,
Mary Lou

[chuckdeb]

Yes, your function is running without error. Could be a problem with your statement

"If rstName.EOF Then Exit Function"

An empty recordset will return BOF not EOF.

Change it and see if this helps.

------------------