form closing but openning as well.

[Andre C]

I have created my own switchboard which comprises labels which
hyperlink to the forms. On one form I am getting the following
problem:

I open it to edit. On the form is a combobox to select the record.
next to the combobox is an option button which when selected will
filter the combobox list (else the contents are too large.)

The form opens fine. If you just exit without doing anything this is
also fine. (I have a close button on the form) However if I find a
record and then close the form the form disappears but immediately the
form opens in addnewrecord mode and asks for a criteria for the option
button.

the code in the close button is

Private Sub closebttn_Click()
On Error GoTo Err_closebttn_Click

If Birthdate > Amit_date Then
MsgBox ("This child was admitted before they were born. Please
correct.")
Exit Sub
End If


DoCmd.GoToRecord , , acNewRec
DoCmd.Close

Exit_closebttn_Click:
Exit Sub

Err_closebttn_Click:
'usual error is because ICS number is null though the field is
required.
DoCmd.Close

End Sub


I do not remember why I put in DoCmd.GoToRecord , , acNewRec line. I
beleive it was to make sure the record was stored. But it makes no
difference even if I comment it out. The error still happens.

What I have noticed is that I also have a label on the switchboard to
open the form in addrecord mode above. If I open the form that way,
then close it and then open the record in normal mode I do not get the
criteria box.

Any ideas.

 

[Allen Browne]

Use the BeforeUpdate event of the Form to perform record-level validation
(such as comparing 2 fields.) Otherwise the user can save the bad record by
moving to another one, closing the form, applying a filter or a dozen other
ways.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.BirthDate > Me.Amit_date Then
Cancel = True
MsgBox "This child was admitted before they were born. Please
correct."
End If
End Sub

Now, your close button needs to force the save before closing the form. It
needs error handling: if the save fails, error 2501 is generated, and you
need to leave the form open then:

Private Sub closebttn_Click()
On Error GoTo Err_Handler

RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description
End If
Resume Exit_Handler
End Sub

Note that the explicit save is really important, so that Access does not
just lose you entry. Details of that problem in:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

 

[Andre C]

Use the BeforeUpdate event of the Form to perform record-level validation
(such as comparing 2 fields.) Otherwise the user can save the bad record by
moving to another one, closing the form, applying a filter or a dozen other
ways.

SNIP

Many thanks for that. I now need to go over all my forms and move
validation. I never really appreciated beforeUpdate event.

Cound you just explain to me what the cancel integer is doing in the
beforeupdate section. I cannot make it out from the help file.

Acc

 

[Allen Browne]

Several events provide the Cancel As Integer argument.
You can cancel the event by setting this to True.
If the BeforeUpdate event of the form is cancelled, the record is not saved.
The user must undo the change (e.g. press Esc) or fix the problem so it can
be saved.

Use the BeforeUpdate event of the control if you want to validate it at the
time the user is about to leave it (e.g. to ensure the value is in a range.)
Use the BeforeUpdate event of the form to compare values between fields, to
ensure that a field has an entry, etc.

 

[google]

many thanks.

Everything worked but I have had to expand the form with a subform
which is storing dates. Whilst the subform seems to be working fine I
am yet again getting the same message as before when I exit.

I am presuming this has something to do with closing the subform. At
present there is no button to close the subform as that is not what I
want. I just want to exit the form as a whole.