Closing form problems (continued...)

[Neil]

Hello All,

I posted this question yesturday and Jeff kindly responded with the answer
below. However, my original problem still remains. Even after making my
second form a modal dialog, if the user selects that they want to continue
ammending the record (without undoing changes) then this message box
annoyingly appears. I really need to supress this message and then catch the
errors in the main forms unload event and then cancel the unload (if
necessary). In the Error event for the form, I can set the response and I
dont get the message box. Only thing with this is the changes are
automatically undone. Anyone know how to stop this message from appearing???

TIA,

Neil.

Also, the custom pop-up form is modal so you can't interact with the main
form until the user selects an action (Undo & Close, Undo, Continue to
correct errors).

-----------------------------------------------------------------
Neil wrote:

Thanks for your input Jeff,

The form is Modal but can't be set to dialog. There is an option frame on
how to proceed and an OK button. If the form is dialog then the user cant
change anything in the option frame. Maybe my only work around is to replace
the option frame with command buttons.

Thanks,

Neil.


Jeff wrote:

Neil

Is there a chance you don't open the pop-up message form in "dialog" mode?
It sounds a little like the user can still see the main form's <Close>
button, and can still select it.

Perhaps trying to close a main form (and save a record) when the
BeforeUpdate code is still running (i.e., the pop-up isn't closed yet) is
causing your problem?

--
Good luck

Jeff Boyce
<Access MVP>

Neil originaly wrote:

Hello All,

I have a problem with Access 2002. In the BeforeUpdate event of my form, I
have a procedure which loops through all the controls on my form. If any
fields that are required are left blank, I cancel the event and bring up
another form which displays the reason why the event was cancelled and gives
the user an option to undo or return to the main form and continue. This
works great until the user clicks on the close button in the main form. I
then get the Access DataErr 2169

Microsoft Access

You can't save this record at this time.

Microsoft Access may have encountered and error while trying to save a
record.
If you close this object now, the changes you made will be lost.
Do you want to close the object anyway?

Yes No

The form that I open has the functionality of this message box (and gives a
better description of what is wrong and how to correct it) and I would like
to display that instead. I have tried coding the Form_Unload event to do
this but the message box above appears before the Unload event executes. I
have also tried coding the Form_Error event to catch this error and try to
supress the message but can't seem to get that to work either. Does anyone
have any sugestions as to how I can get this to work properly?

TIA,

Neil.

 

[Jeff Boyce]

Neil

Still not sure I understand what you're doing and what's happening, but
here's another approach to a solution -- this is just generic.

In the BeforeUpdate event, add a string variable that will hold your
"message". Step through each field and test for required/not, and if
missing, add to/modify the messagestring.

When you've cycled through all fields, check the messagestring -- if it
isn't a zero-length string, there was a problem with at least one field.

NOW, build a message box and put the messagestring there. Display the
message box (MsgBox()), only allowing an <OK> choice -- you DID say that the
trigger was missing data from required field(s), right?!

After the messagebox is displayed, cancel the update (Cancel = True).

You should be back to your main form.

If you want to get really fancy, also have the BeforeUpdate routine
determine which of the missing data fields is "first" on the form, and set
the focus back there after cancelling.

 

[Neil]

Thanks Jeff,

I suppose I am being too fancy. I have a form that I use as a general form
to display errors (blank fields are just one of the warnings). In the
BeforeUpdate event of any user input form, I use a procedure to add any
error's found into an error table. I then display the error form. This has a
listbox on it with all the field names that have been found that are left
blank. The form also has 3 option buttons (Undo & Close, Undo, Continue (and
sort out errors)). The user would either select one of the ways to proceed
and then click on my ok button (doing the necessary changes as it closes) or
they can click on a field name in the List Box and the error form will then
close and set the focus to that text box (or combobox). If the user tries to
move to another record and there are errors, this works great. As soon as
someone goes to close any user input form i get the message mentioned below
and it is basically repeating the error form that I bring up (but it is a
lot more simpler and a lot more vague). To re-create my problem, just set
Cancel = True in the BeforeUpdate event of a form and then close the form -
as long as it is dirty (using the x in the top right corner). You can select
yes to continue with close but the record is not saved or click no to return
to the form (the form doesn't close then).

I have posted my code below to add fields to a table. This can then be
called by any forms BeforeUpdate event by writing Call CheckBlanks(Me).

Thanks for your help,

Neil.

--------------------------------------------

Public Function CheckBlanks(frmTemp As Form, Optional ByVal blnShowForm As
Boolean = True) As Integer
On Error GoTo Err_CheckBlanks

Dim ctl As Control
' Set public form
Set frmBlanks = frmTemp
' Clear the blanks table
Call ClearBlankTable
' Loop through controls
For Each ctl In frmBlanks.Controls
' Only check text box's and combobox's
If TypeOf ctl Is TextBox Or ComboBox Then
' Check the back colour
If ctl.BackColor = 16777164 Then
' Check the control has a value
If ctl.Value = "" Or IsNull(ctl.Value) Then
' Set return value to cancel event
CheckBlanks = True
' Add field to table
Call AddBlank(ctl)
End If
End If
End If
Next
' Open the blanks form if blank field found
If CheckBlanks And blnShowForm Then
' Open the form
DoCmd.OpenForm "frmBlankFields"
End If

Exit_CheckBlanks:
' Exit
Exit Function

' Error handler
Err_CheckBlanks:
' Check error
Select Case Err
Case 438 ' Property not supported by control
' Resume next
Resume Next
Case Else
' Display error
MsgBox Err.Description, vbCritical + vbOKOnly, "Error: " & Err
' Exit
Resume Exit_CheckBlanks
End Select

End Function

Private Sub AddBlank(ctl As Control)

Dim rsSource As DAO.Recordset
Dim rsBlank As DAO.Recordset
' Get table data
Set rsBlank = CurrentDb.OpenRecordset("tblBlankFields", dbOpenDynaset)
Set rsSource = CurrentDb.OpenRecordset(ctl.Parent.RecordSource,
dbOpenDynaset)
' Add field name
rsBlank.AddNew
rsBlank!strName = rsSource(ctl.ControlSource).Properties("Caption")
rsBlank.Update
' Clean up
rsBlank.Close
rsSource.Close
Set rsBlank = Nothing
Set rsSource = Nothing

End Sub

Public Sub ClearBlankTable()

Dim qry As QueryDef
' Get query
Set qry = CurrentDb.QueryDefs("qryClearBlanks")
' Run query
qry.Execute
' Clean up
qry.Close
Set qry = Nothing

End Sub