Add extra code to close form?

J

jo

Hi guys the following code that is attached to a command button covers
certain conditons and works fine. But my problem is this: when the user opens
the form and then decides not to use the form and wants to close it the code
does not close because there is not a condition to cover that aspect. I dont
know what to add to the code to cover a form that has been opened but has not
yet created a record.(the form is totally blank until the user selects a
[TYPE]). Your help would be most appreciated..
Option Compare Database

Function boreclose()
On Error GoTo boreclose_Err

With CodeContextObject
If (.CheckChoice = 1 And .Ring2c = "Not Approved") Then
Beep
MsgBox "Not Approved select Setting", vbExclamation, "Not
Approved"
End If
If (.CheckChoice > 1 And .Ring2c = "Not Approved") Then
DoCmd.RunMacro "CloseBore", , ""
End If
If (.Ring2c = "Routine Approved") Then
DoCmd.RunMacro "CloseBore", , ""
End If
End With


boreclose_Exit:
Exit Function

boreclose_Err:
MsgBox Error$
Resume boreclose_Exit

End Function
 
T

Tom Wickerath

Hi Jo,

How about only running your boreclose function if the form is "dirty" (ie.
the user has started to edit something--pencil symbol displays in record
selector, if you are showing the record selector on the form). You should
also include text in your message instructing the person how to abort if they
do start editing a record, but then decide they don't want to finish right
now, and would rather start over later. You can add to the message statement
something to the effect of pressing the Esc button to back out, or provide
them with an Undo button.

Something like this for the first part:

Function boreclose()
On Error GoTo boreclose_Err

If Me.Dirty = True Then
With CodeContextObject
If (.CheckChoice = 1 And .Ring2c = "Not Approved") Then
Beep
MsgBox "Not Approved select Setting", vbExclamation, "Not
Approved"
End If
If (.CheckChoice > 1 And .Ring2c = "Not Approved") Then
DoCmd.RunMacro "CloseBore", , ""
End If
If (.Ring2c = "Routine Approved") Then
DoCmd.RunMacro "CloseBore", , ""
End If
End With
End If

boreclose_Exit:
Exit Function

boreclose_Err:
MsgBox Error$
Resume boreclose_Exit

End Function


By the way, the word "Type" is considered a reserved word. I recommend that
you avoid using reserved words or special characters (spaces, hyphens, etc.)
in anything that you assign a name to within Access.

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
J

jo

Hi Tom thanks for yr reply, I have added If Me.Dirty = True Then, but it
does not work?
I think I need something that says if not dirty then close? I added the
conditions because the users were ticking boxes regardless of the end result.
So to correct this a msg box will ask them to correct it but since adding
this the user can not close the form to return to the main menu if they did
not want that form.


Tom Wickerath said:
Hi Jo,

How about only running your boreclose function if the form is "dirty" (ie.
the user has started to edit something--pencil symbol displays in record
selector, if you are showing the record selector on the form). You should
also include text in your message instructing the person how to abort if they
do start editing a record, but then decide they don't want to finish right
now, and would rather start over later. You can add to the message statement
something to the effect of pressing the Esc button to back out, or provide
them with an Undo button.

Something like this for the first part:

Function boreclose()
On Error GoTo boreclose_Err

If Me.Dirty = True Then
With CodeContextObject
If (.CheckChoice = 1 And .Ring2c = "Not Approved") Then
Beep
MsgBox "Not Approved select Setting", vbExclamation, "Not
Approved"
End If
If (.CheckChoice > 1 And .Ring2c = "Not Approved") Then
DoCmd.RunMacro "CloseBore", , ""
End If
If (.Ring2c = "Routine Approved") Then
DoCmd.RunMacro "CloseBore", , ""
End If
End With
End If

boreclose_Exit:
Exit Function

boreclose_Err:
MsgBox Error$
Resume boreclose_Exit

End Function


By the way, the word "Type" is considered a reserved word. I recommend that
you avoid using reserved words or special characters (spaces, hyphens, etc.)
in anything that you assign a name to within Access.

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

jo said:
Hi guys the following code that is attached to a command button covers
certain conditons and works fine. But my problem is this: when the user opens
the form and then decides not to use the form and wants to close it the code
does not close because there is not a condition to cover that aspect. I dont
know what to add to the code to cover a form that has been opened but has not
yet created a record.(the form is totally blank until the user selects a
[TYPE]). Your help would be most appreciated..
Option Compare Database

Function boreclose()
On Error GoTo boreclose_Err

With CodeContextObject
If (.CheckChoice = 1 And .Ring2c = "Not Approved") Then
Beep
MsgBox "Not Approved select Setting", vbExclamation, "Not
Approved"
End If
If (.CheckChoice > 1 And .Ring2c = "Not Approved") Then
DoCmd.RunMacro "CloseBore", , ""
End If
If (.Ring2c = "Routine Approved") Then
DoCmd.RunMacro "CloseBore", , ""
End If
End With


boreclose_Exit:
Exit Function

boreclose_Err:
MsgBox Error$
Resume boreclose_Exit

End Function
Click to expand...
Click to expand...
 
T

Tom Wickerath

Hi Jo,
Hi Tom thanks for yr reply, I have added If Me.Dirty = True Then, but it
does not work?
Click to expand...

It should work if there were any edits made to the form. Do you have the
Record Selector displayed? If not, temporarily set it to display. What is the
indicated state (pencil symbol, solid triangle standing on pointed side) at
the time when it appears to you that Me.Dirty is not working? A Me.Dirty =
True corresponds with the pencil symbol.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
J

jo

Hi Tom sorry for the delay in answering yr reply, the form is not dirty it
has just been opened by the user via the switchboard menu but the user
decides he does not want to use the form and wants to close the form but the
code does not close the form. The form only closes if any of the conditions
are met. There is not a condition for a form that is not dirty?
 
T

Tom Wickerath

Hi Jo,

I guess I'm not understanding why you cannot simply have code that closes
the form, and conditionally calls your validation procedure if the form is
"dirty". Try something like this, for a command button named "cmdClose":

Private Sub cmdClose_Click()
On Error GoTo ProcError

'Save any changes first
If Me.Dirty = True Then
If Validate = True Then
'Save the record, requery QBF if it is open, and close this form.
Me.Dirty = False
DoCmd.Close acForm, Me.Name
Else
'Record was dirtied, but changes do not pass validation rules, so
bail out.
Exit Sub
End If
Else
'Record was opened for viewing only, but user made no changes.
DoCmd.Close acForm, Me.Name
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdClose_Click event procedure ..."
Resume ExitProc
End Sub


Modify your Boreclose function so that it returns a boolean (True or False)
value. I renamed this function to Validate, to match the above code.

Function Validate() As Boolean
On Error GoTo ProcError

If Me.Dirty = True Then
With CodeContextObject
If (.CheckChoice = 1 And .Ring2c = "Not Approved") Then
Beep
MsgBox "Not Approved select Setting", _
vbExclamation, "Not Approved"
End If

If (.CheckChoice > 1 And .Ring2c = "Not Approved") Then
DoCmd.RunMacro "CloseBore", , ""
End If

If (.Ring2c = "Routine Approved") Then
DoCmd.RunMacro "CloseBore", , ""
End If

End With
End If

Validate = True

ExitProc:
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Validate Procedure..."
Validate = False
Resume ExitProc
End Function


My advice is to replace your calls to a macro with the equivalent VBA code,
ie.
DoCmd.RunMacro "CloseBore", , ""



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
T

Tom Wickerath

PS.
Remove the comment from the code that reads:
"Save the record, requery QBF if it is open, and close this form."

or, just change it to read "Save the record and close this form."

I had copied the code from a database that includes this type of
functionality, but it also is a bit more complex, as it includes an unbound
QBF (Query By Form) form that a person can use to locate which record they
wish to edit. So, the part of the comment that reads "requery QBF if it is
open," would not make sense for your code.

You may need to trap for error number 2110 in the Validate function, and
simply disregard this error. Here is an example:

Validate = True 'If we get this far, then all validation checks passed.

ExitProc:
Exit Function
ProcError:
Select Case Err.Number
Case 2110
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Validate procedure..."
End Select

Validate = False
Resume ExitProc
End Function


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
T

Tom Wickerath

One more PS. (sorry about that)

In my effort to simplify things, I failed to provide code required in the
form's BeforeUpdate procedure to ensure that the Validation function is run
in all cases. The code I provided below would only run the Validation
function when the user clicked on a cmdClose button, to close the form. It
would not run if they simply navigated to a different (or new) record, using
the navigation buttons, if they clicked on the X in the upper right corner of
the form, etc. So, lets add a Form_BeforeUpdate procedure to prevent that
from happening:


Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError

Dim blnValidate As Boolean

blnValidate = Validate

If blnValidate = False Then
Cancel = True
Err.Number = acDataErrContinue
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_BeforeUpdate event procedure..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working outlook via vba code with xlsx - error 0
Dynamic Naming of Form Button 3
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
SUMPRODUCT and SUMIFS combined with Sumifbycolor (VBA code) 1
Crating User Form to Add Names 1
Match style 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Prompt For Replace in Selection And More... 0

Top