Move your validation code into the BeforeUpdate event of the Form (not the
event of a control.)
That's the only way you can catch all the possible ways a record can be
saved (e.g. tabbing past last field, moving record, closing form or closing
Access, applying a filter or sort, keyboard (Shift+Enter, Ctrl+F4, Alt+F4,
....) or menu or toolbar or ribbon or ...)
Your submit button will then save the record, and move to a new one, like
this:
Private Sub cmdSubmit_Click()
If Me.Dirty Then Me.Dirty = False
RunCommand acCmdRecordsGotoNew
End Sub
Keep your error handling: the attempt to set Dirty to False will fail if the
record cannot be saved.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"knowshowrosegrows" <knowshowrosegrows[ at ]discussions.microsoft.com> wrote in
message
news:6A7B3FF6-85F4-40EC-8C14-1CADCCA97BF1[ at ]microsoft.com...
[Quoted Text] >I have a form where in we update an existing record. In the Form Header I
> have a drop down to search for the record. The row source is:
> SELECT qryEventUpdate.Event_ID, qryEventUpdate.Agency_ID,
> qryEventUpdate.Agency, qryEventUpdate.EventDate FROM qryEventUpdate ORDER
> BY
> Agency, EventDate;
>
> The Detail has the controls from the record that will show. The record
> source for this is:
> qryEventUpdate
>
> I have a button called cmdSubmit. I want this to check to be sure they
> have
> filled fields the way I want and then blank the detail section so they can
> choose a new record to update. Right now the code for that button is:
> Private Sub cmdSubmit_Click()
> On Error GoTo Err_cmdSubmit_Click
>
> If ([Agency_ID] = "999" Or Agency_ID = "888" Or Agency_ID = "777") _
> And Len([EventDescription] & vbNullString) = 0 Then
>
> MsgBox "When choosing * Other, Agency - Describe * or * Other,
> Sober House - Describe * or * Other, Reg Board/Cac - Describe *, you must
> fill out the Description field.", vbOKOnly
>
> Forms!frmEventUpdate!EventDescription.SetFocus
>
> Exit Sub
>
> End If
>
>
> Exit_cmdSubmit_Click:
> Exit Sub
>
> Err_cmdSubmit_Click:
> MsgBox Err.Description
> Resume Exit_cmdSubmit_Click
> End Sub
>
> What I don't know how to do is write code so the detail section will go
> blank until they have chosen a new record with the dropdown.
> --
> Thanks
>
> You all are teaching me so much
|