Close Form if Field is Blank Gives 2585 Error

[tjkalb]

On my form, I have a SSN and name field. If the SSN is blank, in the On Got Focus on the name field, it looks to see if SSN exists (did it on next field per suggestion from another person). If it exists, it asks the user if they want to go to that record. Yes opens another form, No closes current form and goes to main menu (this works fine). If the SSN is NULL, it tells the user they must enter, and asks do you want to add? Yes - goes back to SSN field (works fine). For No, I want it to close the current form. Problem, I am getting a run-time error 2585. I've been searching for days on how to fix this error. From what I understand, a process on the form is still running. I can't figure out how to stop it. Here is my code (Yes - I have tried many variations without luck, but this is the last one). The code that's getting the 2585 error is DoCmd.Close acForm, "frmVetNewMainForm", acSaveNo.

If IsNull(Me.txtSSN) Then
     strMsg = "Social Security Number Must Not Be Left Blank!" & vbCrLf
     strMsg = strMsg & "Do you want to add new veteran's record?" & vbCrLf
 
If MsgBox(strMsg, vbQuestion + vbYesNo, "Go to Record?") = vbYes Then
     Me.Undo
     Me.Refresh
     Me.txtSSN.SetFocus
     Me.txtSSN.Value = Null
     Exit Sub
 
Else
     Me.Undo
     Me.Refresh
     Me.txtSSN.SetFocus
 
     DoCmd.OpenForm "fmuMainMenu"
 
     DoCmd.Close acForm, "frmVetNewMainForm", acSaveNo
 
End If
 
... The rest works.
 

I want it to check right away to see if the SSN exists, not when the user closes the form because they would be adding info that could already be in the system if the SSN already exists. So, I don't want to do AfterUpdate because if SSN already exists, it's unnecessary to fill in first name. I don't want to do it on form Close because it may be unnecessary to fill in all the fields on the form. As a safe guard, it does also check that SSN exists when user clicks Close button, and this works fine.

[twinnyfo]

Use the BeforeUpdate Event and then Cancel the transaction.

[tjkalb]

Tried that but because I'm not entering anything into name (it's NULL), there is no update on field so BeforeUpdate and AfterUpdate do not work. If I do enter something into name, it does work. The same goes if I move the code to SSN (it's what I'm checking to see if it's NULL), BeforeUpdate and AfterUpdate do not work because you aren't updating anything - it's NULL.

[tjkalb]

I did try to put 000-00-0000 into SSN number and check for that, but still getting 2585 error. I had to look at the order of events to figure out what works before Before Update. On Key Down runs before Before Update. FYI - Just because others have asked, just in case a user clicks on other fields, I do have it checking to make sure there's a SSN when they click the Close button.

[zmbd]

If I am reading this correctly at line12 in your posted code starts the point where you are attempting to close the form; however, the record is in a state of limbo at this point. Access needs to know that you are canceling the pending change to the record before you can close the form. Have you tried:

12.      Else
 Cancel = true
13.      Me.Undo
14.      Me.Refresh
15.      Me.txtSSN.SetFocus
16. 
17.      DoCmd.OpenForm "fmuMainMenu"
18. 
19.      DoCmd.Close acForm, "frmVetNewMainForm", acSaveNo
20. 
21. End If

I'm also wondering why your are not receiving an error at some point around line7 - strange that, usually you have to cancel the pending change. Personally, I'd put a STOP command in at the start of your Before_Update code so that you can step through the code and see exactly which line is triggering the 2585-RTErr; I am 90% sure that the error is occurring at Line19 when the code is attempting to close - but it could be at some other point in your code as you only posted a small snippet :-) .

[tjkalb]

Yes (and I tried it again) - no luck. I've tried so many variations I'm getting myself confused lol. I also tried DoCmd.CancelEvent and If Me.Dirty Then Me.Dirty = False, including trying to run the code from both txtSSN and the name field. The error is happening on line 19 on the code above. There has to be a way to do this don't you think??

Now it fills in the 000000000 but doesn't close the form if you answer No. Here's all the code as it is right now:

Private Sub txtSSN_KeyDown(KeyCode As Integer, Shift As Integer)
    If IsNull(Me.txtSSN) Then
 
        Me.txtSSN.Value = "000000000"
 
    End If
End Sub
 
Private Sub txtSSN_BeforeUpdate(Cancel As Integer)
 
If (Me.txtSSN) = "000000000" Then
'If IsNull(Me.txtSSN) Then
      strMsg = "Social Security Number Must Not Be Left Blank!" & vbCrLf
     strMsg = strMsg & "Do you want to add new veteran's record?" & vbCrLf
 
     If MsgBox(strMsg, vbQuestion + vbYesNo, "Go to Record?") = vbYes Then
          Me.Undo
          Me.Refresh
          Me.txtSSN.SetFocus
          Me.txtSSN.Value = Null
          Exit Sub
 
     Else
          Cancel = True
          Me.Undo
          Me.Refresh
          Me.txtSSN.SetFocus
 
 
          DoCmd.OpenForm "fmuMainMenu"
 
          DoCmd.Close acForm, "frmVetNewMainForm", acSaveNo
 
     End If
 
 
Else
 
    If DCount("[SSN]", "tblVeteran", "[SSN] = '" & Me![txtSSN] & "'") > 0 Then
 
        strMsg = "Social Security Number is already in the system." & vbCrLf
        strMsg = strMsg & "Do you want to go to veteran's record?" & vbCrLf
 
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Go to Record?") = vbYes Then
 
            Me.Undo
            DoCmd.OpenForm "frmVetMainForm", acNormal, "", "[SSN]=" & " '" & [txtSSN] & "'", , acNormal
            DoCmd.Close acForm, "frmVetNewMainForm"
 
        Else
 
            DoCmd.OpenForm "fmuMainMenu"
            DoCmd.Close acForm, "frmVetNewMainForm"
 
        End If
 
    End If
 
End If
 
End Sub

[zmbd]

Just as I thought, the record is still in limbo from the before_update event.

However one more try:
In the code in post#6 Comment out

25.           Me.Undo
26.           Me.Refresh
27.           Me.txtSSN.SetFocus 

LEAVE the Cancel=True
As you are going to close the form... you shouldn't need to undo anything, refresh the form, nor set the focus to any control.
See if this will release the pending transaction.

If that doesn't work, then I see one of four solutions - cancel the change, open the frmMainMenu and then ONE if the following:
In the On_Load event of frmMainMenu do one:
+ When frmMainMenu opens it checks to see if frmVerNewMainForm is open and closes it.
+ Use the open arguments property of the frmMainMenu to pass a flag to close the frmVetNewMainForm
DoCmd.OpenForm FormName:="fmuMainMenu",OpenArgs:="Close_frmVetNew MainForm"
+ Use the Tempvars collection to set a flag that the frmMainMenu checks to close the frmVetNewMainForm
or
+ Use the frmVetNewMainForm on_error event to trap the error and allow the form to close

[NeoPa]

You do understand that there can be _BeforeUpdate() event procedures for both the Control and the Form?

Your earlier comments indicate you tried to use Form_BeforeUpdate(), but what was being suggested, albeit not specifically, was the {Control}_BeforeUpdate(). This would trigger once you'd entered the SSN number and then moved on to another control either by pressing the TAB key or clicking on another control.

In your {Control}_BeforeUpdate() event procedure you'd include :

With Me
    Cancel = True
    Call .Undo
End With

When both of these lines have executed then your update is still active until the event procedure has completed. You may get away with calling for the form to close at this point. Try it and see.

If not then you'll need to use the technique I illustrated recently in Change Sequence of Remaining Records After Updating Number of Another, where you use the Form_Timer() event procedure to run (only) once to finish off the code once the update is no longer active.

[zmbd]

NeoPa is quite correct - from your first post I was under the impression that you were using the Form's Before_Update event. It doesn't tend to choke on the type of code you posted in #1, but stranger things have happened and with a snippet hard to tell if there was something else locking the record; however, when you posted the revised code in #6 that's when I got the cue that you were still in the control and to suggest that commenting out Lines 25,26, and 27 would allow the code to complete.
- I have had a chance to test that recommendation and it appears to be a viable solution in my test databases, but your mileage may vary.

NeoPa
where you use the Form_Timer() event procedure to run (only) once to finish off the code once the update is no longer active.

I'll have to keep that in mind. I don't have occasion to use that event very often and I forget about it!

[NeoPa]

ZMBD:
I have had a chance to test that recommendation and it appears to be a viable solution in my test databases, but your mileage may vary.

That's curious. I recently did a test myself and found that the {Control}.Undo() and {Form}.Undo() calls were both necessary in many circumstances, otherwise the record remained locked. The latter would only be necessary if the record were Dirty prior to the update in the current Control, or essemtially if the record were Dirty by more than simply a change in the current Control, but I couldn't find any situation where the Control's .Undo() was omitted where the record didn't stay locked - and therefore preclude any extraneous work on any part of that record.

[zmbd]

NeoPa that is curious...
I'll have to try this on one of my large DB at work to see if there's something with table relationships or other Gremlin(s) mucking up the works.

[NeoPa]

Hi Zmbd.

Setting Cancel = True is equivalent to going back to the point just prior to tabbing or clicking away from the Control. IE. The value before you changed it may have been 23 but you just typed in 45. So, you're now back at the point where you've type 45 but haven't yet clicked or tabbed anywhere out of the Control. Only by calling {Control}.Undo() is the data reverted back to how it was found on disk (23) so before that the record is still Dirty, unquestionably.

The {Control}.Undo() is equivalent to pressing the Escape key while still in the Control. Now, if that Control is the only one changed then the record will be non-Dirty after that point, but if another Control on the same record has also been changed then another Escape is required to unlock the record. That second Escape is equivalent to a call of {Form}.Undo().

While both .Undo() calls are processed immediately I'm not so sure about the Cancel = True, as that is only processed by the calling code of the Event Procedure. It may be possible to execute extraneous code to run before the Event Procedure completes if any necessary .Undo() calls have been made, but I doubt it. That's the one area I'm not 100% on.

[tjkalb]

Thanks everyone for all your ideas, but unfortunately nothing has worked. Due to time constraints, I have decided to just tell the user SSN is required and have it go back to SSN field. From there, the user can click Cancel to get out of form. One more click for user. Everyone came up with great ideas but nothing worked. I really appreciate your input!

[NeoPa]

It's hard to understand that nothing worked. Did you have any difficulty implementing the suggestions? The suggestions certainly do work in the scenario you've described.

If there's any particular bit you're having difficulty with let us know and we'll see what we can do to help.

A look at exactly how you've implemented what was suggested would be a good place to start. That way we can see where you've failed to implement the changes correctly and quickly fix any problems.