resetting a subforms textboxes

[A10 Instructor]

I have a Form/subform that is used to allow editing of a user selected record from a table called personnel. When the form/subform is first opened, all text boxes are blank. The user selects the desired record from a combo box that uses a dropdown list to display all records of the table. When a record is selected, the contents of that record are displayed in the subform in which the user can then edit/modify. The user then clicks on a "save button" which then saves the record.

I want to use a msgbox that will notify the user that he has just updated a record and ask if he/she would like to update another record. If the answer is NO, then close form/subform. If answer is YES, I would like to clear the combo box and the subform text boxes. then the user can them start the process over by selecting the desired record from the combo box.

When I placed the msgbox coding into the "save" button's on click property, the NO portion worked fine. However, when YES is selected, the combo box would clear but the subform texboxes still show the previous record data (does not clear).

Can anyone provide some assistance?

Here is the code for the main form:


Sub SetFilter()

Dim LSQL As String

LSQL = "select * from personnel"
LSQL = LSQL & " where last = '" & cboSelected & "'"

Form_Editpersonnel_sub.RecordSource = LSQL

End Sub

Private Sub cboSelected_AfterUpdate()

'Call subroutine to set filter based on selected last name
SetFilter

End Sub

Private Sub Form_Open(Cancel As Integer)

'Call subroutine to set filter based on selected last name
SetFilter

End Sub


Here is the code for the subform "save" button:

Private Sub SAVE_Click()
On Error GoTo Err_SAVE_Click

DoCmd.RunCommand acCmdSaveRecord
DoCmd.close
If MsgBox("You have updated information on a Detachment Member. Do you wish to update another member?", vbExclamation + vbYesNo + vbDefaultButton2, "WARNING") = vbNo Then
DoCmd.OpenForm "PERSONNEL MANAGEMENT"
Else
DoCmd.close
DoCmd.OpenForm "Edit personnel"
End If


Exit_SAVE_Click:
Exit Sub

Err_SAVE_Click:
MsgBox Err.Description
Resume Exit_SAVE_Click

End Sub

 

[MarkK]

Private Sub SAVE_Click()
On Error GoTo Err_SAVE_Click

DoCmd.RunCommand acCmdSaveRecord
DoCmd.close
If MsgBox("You have updated information on a Detachment Member. Do you wish to update another member?", vbExclamation + vbYesNo + vbDefaultButton2, "WARNING") = vbNo Then
DoCmd.OpenForm "PERSONNEL MANAGEMENT"
Else
DoCmd.close
DoCmd.OpenForm "Edit personnel"
End If
...
End Sub

Should the line in red be ommitted? You've already closed something with the line in green. One thing I like to do with the DoCmd.Close is specify its parameters so there is no ambiguity about what to close, like:
DoCmd.Close acForm, Me.Name

Do you really teach people how to drive warthogs?

 

[A10 Instructor]

Re:

LagBolt,

I do better than that. I teach the maintainers on how to maintain the Hawg! A catch phrase we use that infuriates the pilots is this: "It may take a college degree to fly them, but it takes GED's to fix them."

As far as the docmd.close...I probably can get rid of them. I plan to go through later and clean up the coding.