Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Invalid cancel event (Access 2000)

    Invalid cancelevent for combo boxes

    My command to cancel event if a control is not populated seems not to be valid for combo boxes and is valid only for simple controls on the form.
    Wjy is it so? And how can i make it work for combo boxes too?
    Here is my command, valid only for simple controls:

    Private Sub Form_Unload(Cancel As Integer)
    If IsNull(Me.MyControl) Then
    MsgBox "Put your message here"
    DoCmd.CancelEvent
    End If

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid cancel event (Access 2000)

    The problem must have another cause. The method is valid for combo boxes too. To make sure, I created a form with a combo box MyControl and copied your code into the Unload event of the form. It worked!

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid cancel event (Access 2000)

    Thank you very much for your reply. I am sending you the form and the 2 tables
    where this command i put has no effect. Perhaps it is only when the combo box is with the look up tables when this occurs

    Best regards
    Attached Files Attached Files

  4. #4
    Lounger
    Join Date
    Nov 2001
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid cancel event (Access 2000)

    You should use the BeforeUpdate event of the form and Cancel=True

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid cancel event (Access 2000)

    In the first place, RichUK is right. Since the user can move from record to record, you want to check whether TypeID is blank when the user leaves the record, not only when the user closes the form, so you should use the Before Update event of the form instead of the On Unload event.

    In the second place, you set the Default Value property of TypeID in the Customers table to 0, but there is no corresponding record in Types. Records with CustomerID in the range of 1327 to 1352 have TypeID = 0, so TypeID is not empty, but the combo box shows nothing because there is no corresponding description in the Types table. You could have avoided this if you had enforced Referential Integrity for the relationship between Customers and Tables. Access wouldn't have allowed 0 as value for TypeID. You will have to remove the zeros before you can set referential integrity now.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid cancel event (Access 2000)

    Thank you so much for your marveous reply.I understood what you have written to me and now my code is flying!! You always have given me a great help.
    So thank you once again for the very big help with your reply.
    I have a very small question.Sometimes on closing i receive the second message from Access saying that the record will not be saved.How can i avoid this doubling of the mesages?

    Please accept my warm regards

    Below is the code i have in the before update event

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim intMsg As Long
    If IsNull(Me.TypeID) Then
    intMsg = MsgBox("Do You Really Want To Exit This Form" & vbCrLf & _
    "Without Selecting From The Combo", vbOKCancel, "Close Form")

    Select Case intMsg
    Case vbOK
    If Err = 2501 Then
    DoCmd.Close acForm, Me.Name
    End If
    Case vbCancel
    DoCmd.CancelEvent
    Me.TypeID.SetFocus
    Exit Sub
    End Select
    End If
    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Invalid cancel event (Access 2000)

    You are still using DoCmd.CancelEvent, which is a macro action and doesn't do anything for the BeforeUpdate event of the form. To cancel the BeforeUpdate, simply set Cancel = True, which will prevent the update. If you want to avoid error messages, use a me.Undo to undo any changes to the record before you attempt to close the form.
    Charlotte

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid cancel event (Access 2000)

    Charlotte already replied, but I'd like to add a remark: RichUK and I recommended that you use the BeforeUpdate event of the form instead of the On Unload event, because otherwise you'd catch an empty combo box only when the user closes the form, not when the user moves to another record. But I don't think you should ask whether the user wants to close the form in this event.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid cancel event (Access 2000)

    Thank you for your advices i have received.Actually i would like the event to happen only when a new customer is written,and to allow the user to
    cancel if he wishes.However , in either case, i have problems if the user wishes to stay, that is to say if the user clicks Cancel since then the Accer error message appears:


    "You can't save this record at ths time.If you close this object now,the data changes you made will be lost.Do you want to close the datbase object anyway?"
    Maybe the Me.Undo is not put in the right place ?


    Here is my code up to now

    Dim intMsg As Long
    If IsNull(Me.TypeID) Then
    intMsg = MsgBox("Do You Really Want To Exit This Form" & vbCrLf & _
    "Without Selecting From The Combo", vbOKCancel, "Close Form")
    Select Case intMsg
    Case vbOK
    If Err = 2501 Then
    DoCmd.Close acForm, Me.Name
    End If
    Case vbCancel
    Cancel = True
    Me.Undo
    Me.TypeID.SetFocus
    Exit Sub
    End Select
    End If

    In principle my code is working excellent, if i click OK then the fome is closed, but if i click Cancel then the Access message appears, and Me.Undo
    is not working.
    If you do not like my code and a better suggestion i will be pleased to aply it

    Best regards

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid cancel event (Access 2000)

    Why do you want to close the form if a user cancels a new record? That would make sense if your form could only be used to create a new record, but in the database you attached earlier, users can also view existing records. I would be irritated if the form closes when I canceled a new record.

    By the way, you can test if the user is on a new record as follows:

    If Me.NewRecord Then
    ...
    End If

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid cancel event (Access 2000)

    Thank for your reply and i find that you are absolutely right.I will take that into acount.Could you please help me with the seocnd part of my code, shown
    last time? So, my code asks a question OK or Cancel.When i click OK then i leave the form, but when i click cancel, a second message appears
    from Access itself,which i dont need.It is obviuous that the record will not be made.How can i overcome the appearance of the second message.
    I have written Me.Undo, but no effect

    Best regards

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid cancel event (Access 2000)

    Why do you insist on closing the form?

    In which event do you have this code now?

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid cancel event (Access 2000)

    Sometimes the user may change his mind and return to the main form.Also, there is a button on the form which returns the user to the main form after he has been in the form Cutsomers.In that event the form Customers is closed and the main form is opened.Is it possible to have the code both in the AfterUpdate and OnOnload events? I thought in this way we might give the user better flexiblity and to change his mind.Anayway i esteem what you say and if you think the idea is not good than i will discard it

    Best regards

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid cancel event (Access 2000)

    Personnaly, I would just disallow leaving the record if the combo box is empty. If you want to give the user the option of doing so, you have to keep track of canceling the update. You can use a module-level Boolean variable for this. Set it to True if the user cancels the update, and use this to cancel unloading the form. Access will try to save the record before the On Unload event occurs, so trap the extra error in the On Error event of the form.

    I have attached the demo database I made to test this. Here is the complete form-level code.

    Private fCancel As Boolean

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim intMsg As Long

    fCancel = False

    If IsNull(Me.TypeID) Then
    intMsg = MsgBox("Do You Really Want To Exit This Record" & vbCrLf & _
    "Without Selecting From The Combo", vbOKCancel, "Close Form")
    Select Case intMsg
    Case vbOK
    ' Let Access handle it
    Case vbCancel
    ' Cancel the update
    fCancel = True
    Cancel = True
    Me.TypeID.SetFocus
    End Select
    End If
    End Sub

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    ' Error 2169 = can't save record now.
    If DataErr = 2169 Then
    Response = acDataErrContinue
    End If
    End Sub

    Private Sub Form_Unload(Cancel As Integer)
    ' Check if user canceled update
    If fCancel = True Then
    Me.TypeID.SetFocus
    Cancel = True
    fCancel = False
    End If
    End Sub
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •