423,335 Members | 1,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,335 IT Pros & Developers. It's quick & easy.

Close Form if Field is Blank Gives 2585 Error

P: 5
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.
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.txtSSN) Then
  2.      strMsg = "Social Security Number Must Not Be Left Blank!" & vbCrLf
  3.      strMsg = strMsg & "Do you want to add new veteran's record?" & vbCrLf
  4.  
  5. If MsgBox(strMsg, vbQuestion + vbYesNo, "Go to Record?") = vbYes Then
  6.      Me.Undo
  7.      Me.Refresh
  8.      Me.txtSSN.SetFocus
  9.      Me.txtSSN.Value = Null
  10.      Exit Sub
  11.  
  12. Else
  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
  22.  
  23. ... The rest works.
  24.  
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.
2 Weeks Ago #1
Share this Question
Share on Google+
13 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,537
Use the BeforeUpdate Event and then Cancel the transaction.
2 Weeks Ago #2

P: 5
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.
2 Weeks Ago #3

P: 5
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.
2 Weeks Ago #4

zmbd
Expert Mod 5K+
P: 5,279
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:
Expand|Select|Wrap|Line Numbers
  1. 12.      Else
  2. Cancel = true
  3. 13.      Me.Undo
  4. 14.      Me.Refresh
  5. 15.      Me.txtSSN.SetFocus
  6. 16. 
  7. 17.      DoCmd.OpenForm "fmuMainMenu"
  8. 18. 
  9. 19.      DoCmd.Close acForm, "frmVetNewMainForm", acSaveNo
  10. 20. 
  11. 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 :-) .
2 Weeks Ago #5

P: 5
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSSN_KeyDown(KeyCode As Integer, Shift As Integer)
  2.     If IsNull(Me.txtSSN) Then
  3.  
  4.         Me.txtSSN.Value = "000000000"
  5.  
  6.     End If
  7. End Sub
  8.  
  9. Private Sub txtSSN_BeforeUpdate(Cancel As Integer)
  10.  
  11. If (Me.txtSSN) = "000000000" Then
  12. 'If IsNull(Me.txtSSN) Then
  13.       strMsg = "Social Security Number Must Not Be Left Blank!" & vbCrLf
  14.      strMsg = strMsg & "Do you want to add new veteran's record?" & vbCrLf
  15.  
  16.      If MsgBox(strMsg, vbQuestion + vbYesNo, "Go to Record?") = vbYes Then
  17.           Me.Undo
  18.           Me.Refresh
  19.           Me.txtSSN.SetFocus
  20.           Me.txtSSN.Value = Null
  21.           Exit Sub
  22.  
  23.      Else
  24.           Cancel = True
  25.           Me.Undo
  26.           Me.Refresh
  27.           Me.txtSSN.SetFocus
  28.  
  29.  
  30.           DoCmd.OpenForm "fmuMainMenu"
  31.  
  32.           DoCmd.Close acForm, "frmVetNewMainForm", acSaveNo
  33.  
  34.      End If
  35.  
  36.  
  37. Else
  38.  
  39.     If DCount("[SSN]", "tblVeteran", "[SSN] = '" & Me![txtSSN] & "'") > 0 Then
  40.  
  41.         strMsg = "Social Security Number is already in the system." & vbCrLf
  42.         strMsg = strMsg & "Do you want to go to veteran's record?" & vbCrLf
  43.  
  44.         If MsgBox(strMsg, vbQuestion + vbYesNo, "Go to Record?") = vbYes Then
  45.  
  46.             Me.Undo
  47.             DoCmd.OpenForm "frmVetMainForm", acNormal, "", "[SSN]=" & " '" & [txtSSN] & "'", , acNormal
  48.             DoCmd.Close acForm, "frmVetNewMainForm"
  49.  
  50.         Else
  51.  
  52.             DoCmd.OpenForm "fmuMainMenu"
  53.             DoCmd.Close acForm, "frmVetNewMainForm"
  54.  
  55.         End If
  56.  
  57.     End If
  58.  
  59. End If
  60.  
  61. End Sub
2 Weeks Ago #6

zmbd
Expert Mod 5K+
P: 5,279
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
Expand|Select|Wrap|Line Numbers
  1. 25.           Me.Undo
  2. 26.           Me.Refresh
  3. 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
2 Weeks Ago #7

NeoPa
Expert Mod 15k+
P: 31,004
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 :
Expand|Select|Wrap|Line Numbers
  1. With Me
  2.     Cancel = True
  3.     Call .Undo
  4. 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.
2 Weeks Ago #8

zmbd
Expert Mod 5K+
P: 5,279
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!
2 Weeks Ago #9

NeoPa
Expert Mod 15k+
P: 31,004
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.
1 Week Ago #10

zmbd
Expert Mod 5K+
P: 5,279
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.
1 Week Ago #11

NeoPa
Expert Mod 15k+
P: 31,004
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.
1 Week Ago #12

P: 5
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!
1 Week Ago #13

NeoPa
Expert Mod 15k+
P: 31,004
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.
1 Week Ago #14

Post your reply

Sign in to post your reply or Sign up for a free account.