exit form using BeforeUpdate 'or' cmdButton

[ConfusedMike]

I am really hoping someone can help me because I have spent a whole day trying to resolve this one with no success

I have a simple two table database. The first table is Staff which keeps staff details including which company they work for. The second is a list of companies. The two tables are linked together using a combo containing the unique companyID number.

When adding a new member of staff the user must assign the staff member to a company using the dropdown list on the form. If the company doesn't exist yet then there is an 'Add' button next to the combo box which opens a form to add the new company details. On exiting the company form it auto populates the combo field with the new company name. This all works fine if the new company form is populated by tabbing between fields as eventually the BeforeUpdate code is called which then gives the user the usual save options yes, no and cancel. On choosing yes or no the AfterUpdate is activated which refreshes the combo before the DoCmd.close.... closes the form and returns the use to the Staff form without having to manually close the company form which would be showing a blank record.

The problem I have is that some of the users like to use a mouse and so I have had to put an cmd'Exit' button on the form so that they can exit that way. Unfortunately, when the button is pressed I get an error code of 2501 'The close action was cancelled' and the Debug then shows the DoCmd.Close line in the AfterUpdate sub as being the problem

Is there a way to close the form by using this button whilst still having the option of bypassing the new blank record stage that occurs if I remove the DoCmd.Close statement from the AfterUpdate Sub?

Here is the code for the three subs being used




Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case MsgBox("Would you like to save these details?", vbQuestion _
+ vbYesNoCancel + vbDefaultButton1, "Save New Company Details???")
Case vbYes 'saves new record
Case vbNo
Me.Undo 'undo the changes to create blank record
Me.txtCompanyName.SetFocus 'moves focus to top of form
Case vbCancel 'leaves the user to edit their changes
Cancel = True ' returns user to form with data still intact
Me.txtCompanyName.SetFocus 'moves focus to top of form
End Select
End Sub

Private Sub Form_AfterUpdate()
Me.Refresh
With Forms!frmNewStaff.cboCompanyID
.Requery
.SetFocus
.Text = Me.txtCompanyName
End With
DoCmd.Close acForm, "frmNewCompany", acSaveYes
End Sub



Private Sub cmdExit_Click()
DoCmd.Close
Exit Sub

[June7]

I have never used the form Before/After Update events, only for controls. Seems the cmdExit is executing the Close and then the AfterUpdate runs but form has already been closed so this Close line errors. Try testing if the form is open:
If CurrentProject.AllForms("frmNewCompany").IsLoaded Then
DoCmd.Close ...
End If

[Bob Fitz]

Hi

I may be wrong, but I think that if you call the "Form_AfterUpdate" sub in the click event of the button the form will close without an error.
Something like:

Private Sub cmdExit_Click()
Call Form_AfterUpdate
Exit Sub

[RuralGuy]

The usual method of adding another entry to a ComboBox is to use the NotInList Event of the cbo.

[June7]

I like Bob Fitz idea. I have done this before, calling events, should have occurred to me for this instance. Even though might not be necessary to refresh the combobox, won't hurt.

[RuralGuy]

I like Bob Fitz idea. I have done this before, calling events, should have occurred to me for this instance. Even though might not be necessary to refresh the combobox, won't hurt.

If that works, it is simply an anomoly of the code that is not guaranteed to persist with a version change. Better to do it with known code whose execution can be guaranteed to persist.

[ConfusedMike]

Thanks for the tips so far but it still doesn't work.

I tried June's idea first but it made no difference. I put a msgbox in the statement and confirmed that this DoCmd is being called no matter what way I exit.

I then tried Bobs idea but this then created a whole new list of error codes (details below). By using msgbox statement I have discovered that even when using the the call for AfterUpdate the BeforeUpdate still run first.

RESPONSES when calling AfterUpdate from cmdExit:
The exit when tabbing until that activity calls beforeUpdate still works fine but if I press the exit button (Required fields are complete but some of the optional fields are still empty) then I get the following depending upon my response to the yes, no cancel message being called in BeforeUpdate

If save is Yes-
' The expression you entered refers to an object that is closed of doesn't exist'
If Save is No-
'Type Mismatch'
If Save is Cancel-
'The test you entered isn't an item in the list'
If Save is Yes after a Cancel
'You must save the current field before you run the requery action' (this refers to the requery statement in the AfterUpdate code.

I hope that this info helps because this is driving me crazy. If you need a copy of the db then let me know how to attach it? As it is only a two table db I had thought that I could attach it to this thread and you could see the problems first hand but I have no idea how to attach it?

[June7]

Click the Go Advanced button below simple post editor. Below the advanced editor is the attachment manager.

Think I would get rid of the form Update events and find alternative.

[ConfusedMike]

The db is attached. To begin process open frmNewStaff as this one uses the Add button to access the frmNewCompany which only allows the user to enter info into only 'one' new record which is what I am trying to achieve here. The ability to add multiple new companies in one session is not the goal, that would be easy.

[ConfusedMike]

I found a solution but I have no idea what access does differently to calling the BeforeUpdate. If someone could tell me I would appreciate it.

My solution is instead of calling a event from cmdExit I would get it to simulate pressing tab whilst focus is in the last command on the form seeing as this part of my routine works fine.

I made the cmdExit button the last contol in the tab order and the put the following code in the click() event

Softkeys ("{TAB}")

Now when you click on the button it is the same as pressing the TAB key, and as it is the last control on the form it activates whatever routine Access calls in that situation. If anyone knows what that is I would love to know