Record Update Form

[knowshowrosegrows]

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.

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...