set ADODB.Recordset = Me.Recordset 1

[dabruins]

I am working with unbound forms in an access 2002 frontend connecting to a mysql database server.

I have been able to set the recordset of the unbound form in the form_open sub and reset the recordset as required for filtering/search operations but the access delete option is causing me problems. If the user uses the access delete command on the form it prompts the user to continue the delete or cancel. If the user selects cancel the delete operation is still being carried out. So I guess I have to code for the delete bypassing access entirely. The problem is when I try to get a handle on the recordset currently in the form I am getting an error (#91 Object variable or With Block Variable not Set). The code is as follows:

Dim rst As ADODB.Recordset
Dim conn as ADODB.Connection
Set rst = Me.Recordset
set conn = rst.ActiveConnection
conn.open
rst.Open

Do Until Me.id = rst.Fields("id")
rst.MoveNext
Loop

rst.Delete

rst.Close
conn.Close
set rst = nothing
set conn = nothing

I would appreciate it someone could enlighten me as to how you get a handle and open a connection to a form recordset. I thought this would be straightforward but I am getting nowhere on this.

Thanks!

 

[Remou]

The usual way to get the recordset from a form is with DAO:

Dim rst as DAO.Recordset
Set rst = Me.RecordsetClone

I do not quite understand why the Before Del Confirm event is not working, perhaps you could post the code?

If you must use ADO, I guess it would be something like:

Dim rst As ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Set rst = CreateObject("ADODB.Recordset")

rst.Open Me.RecordSource, conn, 3, 3


rst.Find "Id =" & Me.Id

If rst.Fields("ID") = Me.Id Then
    rst.Delete
End If

rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing

 

[dabruins]

The recordset is an ADODB.recordset which was created when I opened the form. When I refer back to it I have to create a handle of the same type object which would again be the ADODB.recordset. In the unbound form the recordsource value is "".

I'm not using a DSN connection string but rather defining DSN-less connection strings when opening the connection on the database so set conn = CurrentProject.Connection woudln't work I don't think?

The code for the delete action is right out of the access box for a form delete command:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Again if the user selects "cancel" at the delete prompt the delete is still carried out on my database server. I have no reason as to why?

 

[Remou]

In this case the wizard is generating code that has since gone out of date. I suppose the code is running from a command button. How about:

Private Sub cmdDelete_Click()

    If MsgBox("Are you sure?", vbYesNo) = vbYes Then
        DoCmd.RunCommand acCmdDeleteRecord
    End If
    
End Sub

If you are using the delete ftom the menu, you can use the BeforeDelConfirm event:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    If MsgBox("Are you sure?", vbYesNo) <> vbYes Then
        Cancel = True
    End If

End Sub

I do not know what your connection string / set-up is, so I used the nearest one to hand.

 

[dabruins]

This codes results in the same prompt to the user after you answer vbYes to the initail message box prompt you have coded for here. If the user selects Cancel again at the following delete prompt the record is deleted anyway in the MySQL database. It might be that the cancel button sends a value back to the Provider that it takes as being "True" and continues the delete operation. I've encountered problems with boolean fields that are simliar to this.

I added a couple of lines to this to suppress the delete prompt warning temporarily and let the vba handle the user response. This seems to work just fine for me!

If MsgBox("Are you sure?", vbYesNo) = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
End If

I'm new to ADO and I think I'm convinced that any problem with Access means I have to code around the problems to get it to work using ADO to reset recordsets, etc. This is a good example of how to use the existing functionality within Access VBA.

You get a star just for saving the few strands of hair I have left. Thanks Remou!