Error 2585 When Closing Form 1

[Sorrells]

Message says "this action can't be carried out when processing a form".

The line of code is:
Requery
DoCmd.Close acForm, "frmEnterNewDNCAR", acSaveNo

I have tried to close this form in many different ways, including a call to a function in a module that attempts to close the form. Note that a 'requery' is performed just before the close to write the record.

My suspicion is that the error indicates another problem that I am not addressing but I do not know what it is! B-(

The context is that a txtbox when completed performs a dLookup on the LostFocus event to see if the data has already been entered into indexed field of the table. If it has then the user is notified that their entry is duplicate data giving them a choice of CANCEL to they may re-key the entry or OK where I write the record, requery the table, close the form and return them to the main menu. But no matter what I do, this error keeps coming up! makes me wonder how any form gets closed!

Below is my code for this event. Any advice is appreciated!

Sorrells
PS: I tried t close the form in the Module where the function Delete_Record is located and still receive this error.
================================================================

Private Sub txtLogNumber_LostFocus()
Dim GetValue As String 'hold return value from dLookup function
Dim RetValue As String 'hold value for message box
Dim db_Name As String, Form_Close As String, Form_Open As String, Table_Name As String, Index_Field As String, Index_Data As String 'VARIABLES FOR DELETE_RECORD FUNCTION
' DEC-I CODING - CHECK FOR DUPLICATE LOG NUMBERS
' The code below performs a check on the LogNumber entered by the user
' to make sure that it is not a duplicate with a dLookup function.
' If it is a duplicate, the user has a choice to CANCEL if the
' LogNumber had been miskeyed, or OK if the number is correct.
' Pressing OK returns the user to the Main Menu so that they can
' contact the DNCAR Administrator to resolve the conflict.
'

txtLogNumber = Format(txtLogNumber, "000000&quot
GetValue = "" & DLookup("[PartNumber]", "DNCARHeader", "[LogNumber] = '" & txtLogNumber & "'&quot
If txtLogNumber = "DupRec" Then
GetValue = "dup rec"
End If

If GetValue = "" Or IsNull(GetValue) Then
' do nothing as no duplicate lognumber was found
Else
RetValue = MsgBox("This is a duplicate Log Number. If you miskeyed it, press CANCEL, if correct, notify the Administrator as this number cannot be used. Pres OK to return to the Main Menu", vbOKCancel)
If RetValue = vbCancel Then
txtLogNumber = "DupRec" 'this text should be replaced by the user
txtSuffix.SetFocus
txtLogNumber.SetFocus
Else
db_Name = "L:\DNCAR_db\IA-Maint\Development\Admin_Test\DNCAR_Data.mdb"
Form_Close = "frmEnterNewDNCAR" ' name of the form to be closed
Form_Open = "frmmenu" ' name of the form to be opened
txtLogNumber = "DupRec" ' provide a value to search for in fuction
Table_Name = "DNCARHeader" ' name of table to be opened to delete record
Index_Field = "LogNumber" ' name of field in table that is indexed
Index_Data = txtLogNumber ' data value in index field to search for
Requery 'force writing of the record
DoCmd.Close acForm, "frmEnterNewDNCAR", acSaveNo 'close form fails with the 2585 ERROR
' [Forms]![frmEnterNewDNCAR].Visible = False
Call Delete_Record(db_Name, Form_Close, Form_Open, Table_Name, Index_Field, Index_Data)
End If
End If

 

[jimmythegeek]

I think you are making this WAY harder than it should be. Can you please post any other code (like Delete_Record) that goes with this, thanks. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[Sorrells]

Jim,

You are right, the code is much too complicated. However, it seems like a form cannot be closed by a VBA command within a control's event (except for command buttons).

I started with a DoCmd.Close, then identified the form with a no-save. The error shows up every time. Then I became more elabrate, forcing a record to be written to the table, so that the form would be in a stable state (haha), deletign the record in VBA then closing the form. All works but for closing the form.

If I state that a form cannot be closed via vba in a text/list/combo box control, would you disagree? And if you did, why?

Thanks, Sorrells

 

[jimmythegeek]

I was able to duplicate your error. I don't know why it doesn't work on the LostFocus event, because it works on the AfterUpdate event.

Following is the cleaned up code that I got to work:

========================
Private Sub txtLogNumber_AfterUpdate()
Dim strMsg As String

txtLogNumber = Format(txtLogNumber, "000000&quot

strMsg = "You have entered a duplicate Log Number" & vbNewLine & vbNewLine _
& "If you miskeyed it, press CANCEL. If correct, notify " & vbNewLine _
& "the Administrator as this number cannot be used." & vbNewLine _
& "Press OK to return to the Main Menu"

If Not IsNull(DLookup("[PartNumber]", "DNCARHeader", "[LogNumber] = '" & txtLogNumber & "'&quot) Then
If MsgBox(strMsg, vbInformation + vbOKCancel, " Duplicate Log Number&quot = vbCancel Then
Me.txtLogNumber = "DupRec"
Me.txtSuffix.SetFocus
Me.txtLogNumber.SetFocus
Else
If Me.NewRecord Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 ' Undo the record if it a new record
End If
DoCmd.Close acForm, "frmEnterNewDNCAR"
End If
End If
End Sub
=========================

Try the AfterUpdate event, and see if that works.

Jim Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[Sorrells]

Jim,

Yes the afterupdate event will work for me. I've already moved the code over there. As you may suspect, there are other issues that now need to be resolved since I cannot use the LostFocus event but they are all resolvable.

Thank you for your interest and advice.

Sorrells

PS: I don't understand how to give you a rating on this but if I did it would be between 4 and 5 stars!

 

[jimmythegeek]

You don't rate with amounts of stars, you simply click at the bottom where it says Let ??? know this post was helpful.

Thanks Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[Sorrells]

Jim,

I must be blind but I've spent 5 minutes between here and the end of this page looking for "says Let ??? know this post was helpful" with no luck.

I will just say, yes. Thanks a lot sir!

Sorrells

 

[jimmythegeek]

No problem, glad to help.

And by the way, the hyperlink I am talking about is at the bottom of each post, not the bottom of the page.

There should be 3 hyperlinks on all the posts that are not yours:

1) Let (Whoever posted) know this post was helpful
2) Is this post offensive? If so, red flag it
3) Check out the FAQ area for this forum

Your posts should only have the last two hyperlinks I mentioned.

Hope this helps. Jim Lunde
compugeeks@hotmail.com
Custom Application Development