Reset subform using msgbox YES/NO

[A10Instructor]

Hi,

I've been struggling with this for several days and can no longer think straight.

I have a table called personnel. I created a form with a subform to edit the records of table persoonel. The main form has a combo box and the sub form has textboxes that correspond to the table.
When the main form is first opened, the combo box is blank and so are the Text boxes in the subform. When I select an individual in the combo box, its info is displayed in the subform for editing.
Here is where I'm having problems. After making the changes, I have the user click on a command button that saves the record and closes the form/subform via the on-click property. Within that coding, I have a msgbox appear asking if the user wants to edit another record. If answer is NO, switchboard is opened. If answer is YES, form/subform is reopened. The combo box is reset to blank, but the subform still displays the record previously viewed.

I can't figure it out....anyone have any ideas?

Heres the code:

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


SUB-Form 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.OpenForm "Edit personnel"
End If


Exit_SAVE_Click:
Exit Sub

Err_SAVE_Click:
MsgBox Err.Description
Resume Exit_SAVE_Click

End Sub

 

[TheAceMan1]

How are ya A10Instructor . . . . .

Try this:

[blue]Private Sub SAVE_Click()
On Error GoTo Err_SAVE_Click

   DoCmd.RunCommand acCmdSaveRecord
   If MsgBox("You have updated information on a Detachment Member.  Do you wish to update another member?", vbExclamation + vbYesNo + vbDefaultButton2, "WARNING") = vbNo Then
      DoCmd.Close
      DoCmd.OpenForm "PERSONNEL MANAGEMENT"
   Else
      Call SetFilter
      Forms![purple][b]MainFormName[/b][/purple]![purple][b]SubFormName[/b][/purple].Requery
   End If
    
Exit_SAVE_Click:
    Exit Sub

Err_SAVE_Click:
    MsgBox Err.Description
    Resume Exit_SAVE_Click
    
End Sub[/blue]

See Ya! . . . . . .

 

[A10Instructor]

TheAceMan1,

I tried what you suggested and it still didn't work. My Setfilter is on the main form not the subform if that makes a difference. Any other suggestions

 

[PHV]

And this ?
Form_Editpersonnel_sub.RecordSource = "SELECT * FROM personnel WHERE [last] Is Null"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[A10Instructor]

PHV,

Thanks for the suggestion. I placed it into the ELSE section of my MsgBox code and I still have the same issue. The form (Edit personnel) will open with the combo box blank, but the text boxes on the sub form(Editpersonnel_sub) display #NAME? unitil I select an option in the combo box. Then it works normally.

I have noticed that the process works like I want it to if I close the form (Edit personnel), open any other form then close that form. Then reopen the Edit personnel form. Here both the the main form and sub form open with blank combo/text boxes.

Still trying to figure out a way to reset the form/subform using the msgbox. Any additional help is appreciated.

A10 Instructor
"The World is My Classroom

 

[TheAceMan1]

[blue] If answer is YES, [purple]form/subform is reopened. The combo box is reset to blank[/purple] . . .[/blue]

At this point [blue]Private Sub Form_Open[/blue] runs and calls [blue]SetFilter[/blue]. Set filter in turn sets up criteria for the RowSource of the subform, the [purple]criteria of which depends on the combobox which is now blank![/purple] But according to you, the combobox would have to be set to the same value last selected! . . . . .

The best way to track this down is to set a break point in the OpenEvent and a watchpoint on the combobox. Then single step to see whats happening.

Out of curiosity, are you showing the full SQL for LSQL?

For instance, this could happen if Sort Decending was set on theprimary key!

See Ya! . . . . . .

 

[A10Instructor]

TheAceman1,

I'm new to VB coding and debugging. Not exactly sure how to go about setting break and watch points. Can you give me some pointers? This problem is really driving me crazy as there is probably a simplistic solution to it that I just can't see.

A10 Instructor
"The World is My Classroom

 

[TheAceMan1]

A10Instructor . . . .

Sorry to get back so late . . . . .

Replace the code in the [blue]save button[/blue] with the following ([purple]double check[/purple] all names in [purple]purple[/purple] are proper):

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim ctl As Control, DL As String
   
   Set ctl = Forms("[purple][b]Edit personnel[/b][/purple]")![purple][b]cboSelected[/b][/purple]
   DL = vbNewLine & vbNewLine
   
   DoCmd.RunCommand acCmdSaveRecord
   
   Msg = "You have updated information on a Detachment Member." & DL & _
         "Do you wish to update another member?"
   Style = vbExclamation + vbYesNo
   Title = "WARNING! . . ."
      
   If MsgBox(Msg, Style, Title) = vbYes Then
      ctl.SetFocus
      ctl.Text = ""
      Me.RecordSource = ""
   Else
      DoCmd.Close
      DoCmd.OpenForm "PERSONNEL MANAGEMENT"
   End If
   
   Set ctl = Nothing[/blue]

Also the following code you posted is ambiguous (since the combobox has no value on open) and should be removed"

[blue]Private Sub Form_Open(Cancel As Integer)
    
    'Call subroutine to set filter based on selected last name
    SetFilter
    
End Sub[/blue]

See Ya! . . . . . .