On Exit - Perform Event

[warmanlord]

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.

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

[June7]

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

[warmanlord]

OK, let me try Before update first and see if I can get something rolling and I will respond here shortly.

[warmanlord]

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.

[warmanlord]

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?

[June7]

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.

[warmanlord]

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

[June7]

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.

[warmanlord]

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.

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.

[June7]

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.

[warmanlord]

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.

[warmanlord]

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?

[June7]

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)?