Wednesday, April 11, 2012 11:53 AM
I'm using Access 2007 and Sql 2008.
I have a purchase request database that I'm trying to setup a command button to cancel a request if the user needs to.
I've tried to undo and delete the record but each time I get the ODBC call fail error.
I suspect I'm getting the error because of the primary key field.
Is there any way to get around the ODBC error without having the user click the Ok button on the error message?
Wednesday, April 11, 2012 3:14 PM
By "cancel a request" do you mean one not committed yet or an existing record?
It might help to see your code.
Thursday, April 12, 2012 12:36 PM
I mean a user has opened the Purchase Request form then for some reason needs to cancel the request.
I'm using the following code to cancel the request but I get the ODBC call fail error when you click yes and form closes. I think it's because of the primary key field is auto populating when the form is opened. I've also tried delecting the record but I also received the same ODBC error. I just trying to find a way for a user to cancel a reqeust without getting this error.
Private Sub cmdCancel_Click()
Dim strMsg As String
Dim iResponse As Integer
strMsg = "Do you wish to cancel the Purchase Request?" & Chr(10)
strMsg = strMsg & "Click Yes to Cancel or No to continue."
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Cancel?")
If iResponse = vbYes Then
Cancel = True
DoCmd.Close acForm, "frmEntryPurchaseRequest"
Thursday, April 12, 2012 1:53 PM
Me.Undo works just fine for me. Perhaps you are doing other things in other events to save the record. The PK Identity value is not generated by the sql server until you save the record.
One other problem I see with your code, unrelated to this issue, is "Cancel=True". You have not dimensioned the variable Cancel in this procedure so I think you don't have "Option Explicit" on at the top of the module. That may cause subtle problems so please go back and add it for all modules. Then use Code Window > Tools > Options > Require Variable Declarations to make this automatic going forward.
-Tom. Microsoft Access MVP
Thursday, April 12, 2012 3:09 PM
I'm not sure what I changed but the request is cancelling now without the ODBC error.
I'll the Cancel = True problem.
Thanks for the help,