..yet another MDB to ADP conversion issue

[embryo]

This event procedure worked fine in Access 2000, but now that it's in an ADP, not. It is deleting the first record in the recordset when I click the DeleteButton rather than the selected value in the Combo0 combo control. How do I get it to delete the selected value from the combo box?

------------------------------------------------------------
Sub DeleteButton_Click()
On Error GoTo Err_DeleteButton_Click
Dim db As Database
Dim rs As Recordset
Dim dgdef As String
Dim msg As String

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Set db = CurrentDb

db.Execute "Delete * from TblDebtors Where DebtorID = " & Combo0.Column(0) & " "


Combo0.Requery


Exit_DeleteButton_Click:
Exit Sub

Err_DeleteButton_Click:
MsgBox Err.Description
Resume Exit_DeleteButton_Click
Combo0.SetFocus


End Sub
------------------------------------------------------------


Thanks-

Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[lupins46]

I don't do Adp's so I'm not really sure about this but surely currentdb is a Jet (mdb if you like) concept so will never work in an adp.

 

[cmmrfrds]

CurrentProject.Connection is the connection to the SQL Server database and it has an Execute method.

CurrentProject.Connection.Execute "Delete * from TblDebtors Where DebtorID = " & Combo0.Column(0) & " "

You can set up other connections if you want. For example.
Dim cn as New ADODB.Connection, recsAffected as integer
set cn = CurrentProject.Connection

cn.Execute "Delete * from TblDebtors Where DebtorID = " & Combo0.Column(0) & " ", recsAffected

Debug.Print "deleted "; recsAffected; " records"

If you set up any object such as a connection then you need to destroy when done or it is retained in memory. If you leave the sub or function before destroying then it will continue to use memory since you cannot get back to destory it - memory leak. A compact will get rid of any stray memory.

cn.Close
Set cn = Nothing '- destroys the object.

 

[embryo]

Thanks lupins46...got me on the right track...evidentally i was indeed using old DAO stuff...i have just been successful with the following ADO code:

----------------------------------------------------------
Sub DeleteButton_Click()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As ADODB.Parameter
Dim msg As String

msg = MsgBox("Are You Sure That You Want To Delete This Debtor: " & Combo0.Column(1), vbYesNo, "Delete Debtor!")
If msg = vbYes Then

Set cnn = CurrentProject.Connection
Set cmd.ActiveConnection = cnn

Set param1 = cmd.CreateParameter("DebtorID", adSmallInt, adParamInput)
cmd.Parameters.Append param1
param1.Value = Me.Combo0.Column(0)
cmd.CommandText = "dbo.DeleteDebtor"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute
set cnn=nothing
Combo0.Requery

End If

End Sub
-----------------------------------------------------------


Thanks cmmrfrds...good catch-
Is it necessary to also destroy recordsets, or just the connections?


Steve
---------------------------------------
IF WebApplicationProgrammer = True Then
ElectronicSheepHerder = True
End If

 

[cmmrfrds]

Any Object created with the New keyword is going to retain memory until you get rid of it.
Set rst = Nothing

The recordset object can grow to be quite large - lots of memory.