Results 1 to 13 of 13
  1. #1
    warmanlord is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    30

    On Exit - Perform Event

    Hello all,

    I search around and came upon some code to do some of the needed items for my form when a user clicks the close button, but i ended up with a few errors or issues with it so far. Here is what I am trying to do and I will put the code below that I have been working on. On exit, I want to make sure that if a user doesn't click the save button, it will clear the form before closing and not save the new data since the whole form wasn't filled out properly. or check to see if certain fields are populated. I don't want half data saved and also I don't want it to delete records that are being updated or changed.

    So basically on click of the close form, clear data that hasn't been saved and check to ensure two fields have been populated before closing and saving, prompting a user with a msg, stating Do you want to save this form or close, or cancel to go back and fill in the proper information.


    Code:
    Private Sub CloseRecord_Exit(Cancel As Integer)
      Dim strmsg As String
      On Error GoTo Err_cmdClose_Click
        
      strmsg = "Are you sure to close Application"
        
      If MsgBox(strmsg, vbQuestion + vbYesNo, "Close Application") = vbYes Then
          If IsNull(Me.Video_Number) Or IsNull(Me.cboModemDNSName) Then
              MsgBox ("Record will be not saved")
              DoCmd.RunCommand acCmdUndo
          End If
          DoCmd.Close acForm, "Video Form"
      Else
          If IsNull(Me.Video_Number) Or IsNull(Me.cboModemDNSName) Then
              Me.Video_Number.BorderColor = vbRed
              Me.cboModemDNSName.BorderColor = vbRed
              DoCmd.CancelEvent
          End If
      End If
        
      Exit_cmdClose_Click:
          Exit Sub
        
      Err_cmdClose_Click:
          MsgBox Err.Description
          Resume Exit_cmdClose_Click
        
      End Sub


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    A record is committed to table when close form, move to another record, run code to save. The usual place to do data validation is in form BeforeUpdate or BeforeInsert event. I have never tried the Exit event of a button.

    Exactly what errors is your code generating? What is exact message?

    Try:

    If Me.Dirty Then DoCmd.RunCommand acCmdUndo

    I've never used DoCmd.CancelEvent, instead: Cancel = True
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    warmanlord is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    30
    OK, let me try Before update first and see if I can get something rolling and I will respond here shortly.

  4. #4
    warmanlord is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    30
    Ok, so i been messing around with it and decided to delete all the code for now, what I am running into for the moment, is if I click on the Close Button, if any data is typed into the form, I get an error "The Microsoft Access database Engine cannot find a record in the table modems with Key matching field(s) 'Modem DNS Name'. So I guess I need to figure out when close the form, if any data is typed into the form you get the that error. Then I can proceed to the next step in trying to clear the form if it hasnt been saved by the user and checking for validation on the on close event or after update.

  5. #5
    warmanlord is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    30
    Is there any reason why I would get the error "The Microsoft Access database Engine cannot find a record in the table modems with Key matching field(s) 'Modem DNS Name", when closing the form that has data populated into it? Is it because it is looking for required fields and those fields are not populate so it will dump the record?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    If the issue is because required fields are not populated, I think the message would be different.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    warmanlord is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    30
    Here is the DB, I cleaned it out of confidential data. If you open up the video form, and say you type in something in the location field, if you close it you get an error, it used to just auto save into the table if you closed it. I was trying to prevent an auto save if the form wasnt filled out properly on close either with a msg to user to state you have not saved the record, you will lose all data if you continue to close the form and gives them an option to either cancel the close, hit yes to continue to delete the unsaved record or unsaved update of a record. I walked my self back to just closing the form and took out all the code that I was trying to do on exit or Afterupdate of a record, cause I was encountering so many issues, but then i ran into the issue now that no matter what you type if, it wont auto save and now gives an error. after I fix that issue then I will continue on to tackle the afterupdate clear form or save form depending on what the user chooses.


    DB.zip

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    A form can be used to enter/edit data for only one table. Why are both tables in the Video Form RecordSource? An INNER JOIN requires related records in both tables for a record to show on form.

    Form arrangement for how these tables are related should be a form bound to VideoCameraInformation and combobox to select modem.

    What is the nature of relationship - each camera can have only one modem and each modem can be associated with only one camera at a time?


    You are using the Modem DNS Name field as primary key. Many would advise against this. For one thing it is text, not number.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    warmanlord is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    30
    The form arrangement was as you stated, as a combobox to select the modems to add with the cameras. I am not sure how the record sources have both forms and an inner join, I deleted that out and it is back to normal, I havent changed anything in that box since I first created the form. I have only been messing with the code. The object of these forms, is that the video form will contain new information about the videos, while the modems form houses all the modems and the video form just recalls that information to fill into this form. I made the modem dns name as the primary field as there will only me one of each modem and they are incremental, like test1.modem, test2.modem etc. I am not sure why I did this with the primary key, but I assume I had a good reason when I did do it, but cant think of it at this time.

    Quote Originally Posted by June7 View Post
    What is the nature of relationship - each camera can have only one modem and each modem can be associated with only one camera at a time?
    That is correct, but over time each modem might be with different cameras and the modem information might change depending if a modem goes bad or needs to be changed out.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The form has a RecordSource with INNER JOIN of tables. No fields are actually pulled from the Modems table so it makes no sense to include in the form RecordSource. Bind Video form to the table. I made this edit and don't get the error you describe.

    Also, should not save all the modem info into VideoCameraInformation. Save only the primary key from Modems. Display the modem info in textboxes with expressions that reference the combobox columns by index. Index begins with zero.

    =[cboModemDNSName].[Column](1)


    Do you care about history of modem assignments? If so, will need another table.


    Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be ModemDNSName or Modem_DNS_Name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    warmanlord is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    30
    History of modems have not come up in our discussions, doubt it will be, but something I will keep in mind. I deleted that inner join as well and assigned it back to just VideoCameraInformation and it was working without giving an error. If you check out the code the combobox is setup that way with column and such I believe unless I am mistaken.

  12. #12
    warmanlord is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2015
    Posts
    30
    what would you recommend to do for the video form, to ensure that a partial record isnt save automatically when the form is closed, should I do some sort of validation or just clear the any data that is in the form if it isn't saved before exiting and give the user a msg stating such, that the form has not been saved and any data entered will be cleared?

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    One option is to set fields as required in table then let Access nag the users when they start a record but don't input all data. They will eventually learn not to.

    Otherwise, need substantial amount of code to validate the record. How many fields - all 10 (after eliminating the 7 unnecessary modem info fields)?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. best event for general use...keydown or on exit
    By gangel in forum Programming
    Replies: 8
    Last Post: 09-06-2015, 12:51 AM
  2. Help with Validation code on exit event on a form
    By thebionicredneck2003 in forum Programming
    Replies: 2
    Last Post: 05-17-2013, 06:04 AM
  3. Replies: 2
    Last Post: 12-20-2012, 03:06 PM
  4. OnClose event and exit/quit buttons
    By atran in forum Access
    Replies: 1
    Last Post: 07-24-2012, 04:14 PM
  5. How do I capture Access DB exit event
    By jscriptor09 in forum Access
    Replies: 3
    Last Post: 09-21-2011, 09:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums