Cancel Request - ODBC Call Fail

Question

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?

Thanks,

Paul

Answer 1

Paul

By "cancel a request" do you mean one not committed yet or an existing record?

It might help to see your code.

---

Bill Mosca
http://www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 2

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
    Me.Undo
 
    Cancel = True
 
    DoCmd.Close acForm, "frmEntryPurchaseRequest"
    DoCmd.OpenForm "frmMain"
    DoCmd.Maximize
   
    Else
   
    Me.cboTypePurchase.SetFocus
   
 End If
 
End Sub


Thanks,

Paul

Answer 3

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

Answer 4

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,

Paul