Using VBA to Close Form After Requery

[Reese]

I have a form that is meant to add a new facilitator to the FacilitatorTable. It can be opened directly from a menu page.

However, sometimes it will be opened from a form that is used to link facilitators to events that they facilitate. The combo box's row source is based on a query that is based on the FacilitatorTable. When a new facilitator is added, the query has to be refreshed for the name to appear in the combo box.

I have tied this requery to the VBA code in the "close" button on the new facilitator form. That part works great. The problem is that now, for some reason, the close part of the VBA isn't working. Here is what I have:

Private Sub CloseCmd_Click()

On Error GoTo errHandler

Dim CmdCloseMsg As String
CmdCloseMsg = MsgBox("Save new facilitator and close?", vbOKCancel + vbDefaultButton1, "Close form?")
If (CmdCloseMsg = vbOK) Then
    DoCmd.RunCommand acCmdSaveRecord
    'If this form was opened from the ZooMobile Ed Update form, EdFormTxt control is set to "ZM"
    If Me.EdFormTxt = "ZM" Then
        'Refreshes the facilitator combo list source in the subforms of the ZooMobile Ed Update form
        DoCmd.Requery Forms![ZooMobile Eduction Update Form]!FacilitatorSubform.Form!DocentTxt
        DoCmd.Requery Forms![ZooMobile Eduction Update Form]!ShadowSubform.Form!ShadowTxt
    End If
    DoCmd.Close acForm, "New Facilitator Form", acSaveNo
End If

errHandler:
If Err.Number = 2046 Then
  Resume Next
End If

End Sub

The DoCmd.Close line works fine when the If statement with the requery commands is removed. It also works fine when put before the requery commands, but that of course stops the entire VBA code and the requery commands aren't triggered.

Does anyone have any ideas? Thanks.

 

[MarkK]

Does it make a difference if you requery the controls directly, like . . .

With Forms![ZooMobile Eduction Update Form]
   !FacilitatorSubform.Form!DocentTxt.Requery
   !ShadowSubform.Form!ShadowTxt.Requery
End With

. . . rather than using DoCmd?

Also, if you are closing the form in which code is running, you can do . .

DoCmd.Close acForm, [COLOR="Blue"]Me.Name[/COLOR]

. . . because sometimes you change the name of a form.

 

[Reese]

Thank you for the suggestions. I have never used the With commands and I have no idea how they work or what they do. I did experiment and copy/paste your suggested code, replacing the DoCmd lines and that worked fine, just the same as the DoCmd technique.

It didn't solve the close command issue, though. Neither did using Me.Name, unfortunately.

 

[sneuberg]

On a different topic I noticed you have the line

DoCmd.Close acForm, "New Facilitator Form", acSaveNo

If you think that discards any changes made, I think you'll find that it doesn't. I've found that closing a bound form always saves any changes. The exception to this is if a required field is left blank. In that case it doesn't save and doesn't warn that it doesn't. I think that acSaveNo refers to the form itself (close a form from design view) and not the data. When we want to get the effect of discarding changes we put in the following.

If Me.Dirty Then Me.Undo

Edited:

You can disregard this at least for this case, I now notice that you save the record earlier anyway.

 

[Reese]

Yeah, I know that acSaveNo doesn't negate saving record changes and to use Me.Dirty code instead. I can't remember why I included the acSaveNo at first. I did it for some reason and then forgot. I suppose I could remove it, lol.

Please let me know if you have any other suggestions regarding the close part of the command. Thanks!

 

[MarkK]

There is a leak in your error handler. Add a MsgBox. There could be an error occurring that you don't know about . . .

errHandler:
If Err.Number = 2046 Then
  Resume Next
[COLOR="Blue"]ELSE
   MsgBox "previously un-trapped, and therefore silent error: " & err & " " err.description
[/COLOR]End If

 

[Cronk]

Rather than requery the combo box in the close event of the New facilitator form, put the requery code in the calling subform.

 

[JHB]

...
Please let me know if you have any other suggestions regarding the close part of the command. Thanks!

Your are missing one important code line in your procedure when you've an errorhandling, the "Exit Sub/Function" before the errorhandling part, else your code will run into it even when no error occur.

...
  DoCmd.Close acForm, "New Facilitator Form", acSaveNo
End If

[SIZE=4][B][COLOR=Red]Exit Sub[/COLOR][/B][/SIZE]
errHandler:
If Err.Number = 2046 Then
  Resume Next
End If

To find out if your code throws an unexpected error and in which codeline, I would comment out the error handling until it runs okay.

[SIZE=4][B][COLOR=Red]'[/COLOR][/B][/SIZE]On Error GoTo errHandler

 

[Reese]

Rather than requery the combo box in the close event of the New facilitator form, put the requery code in the calling subform.

I know this is kind of basic, but I don't know what you refer to as "call". What does that mean? I see a lot of instructions on how to do it--hell I may have done it myself without knowing the term--but I can't find a simple answer to "what does it mean when you call a code?".

Are you suggesting that I include the requery code in the subform itself in the click, enter or update events?

JHB & MarkK--

You were right about the error handle. The requery code at least was working correctly yesterday, but today, after taking your suggestions and commenting out the On Error line, I get the following error:

Run-time error '2450':

Microsoft Access cannot find the referenced form 'ZooMobile Education Update Form'.

I included the error code line back in and now neither the requery or the close code work, but no error code comes up. I hate inconsistent issues. Any suggestions?

 

[sneuberg]

, I get the following error:

Run-time error '2450':

Microsoft Access cannot find the referenced form 'ZooMobile Education Update Form'.

You will get that error if the ZooMobile Eduction Update Form is not open. Is it open? If not then there's no need to requery the combo boxes as they will be requeried when the form is opened.

 

[Reese]

You will get that error if the ZooMobile Eduction Update Form is not open. Is it open?

Yes it is. In fact the entire purpose of this code is to requery the combo box row source when the form is opened FROM the ZooMobile Education Update Form.

Yesterday that part of the code was working fine--the row source in a subform in the Update Form was being required, but the New Facilitator Form wasn't closing. Then today, when I started to play around with the error message codes based on others' suggestions, this issue came up and now the combo box row source isn't being requeried AND the New Facilitator Form isn't closing.

 

[sneuberg]

Please post the entire code of CloseCmd_Click as it is now.

 

[Reese]

Here is the current code:

Private Sub CloseCmd_Click()

On Error GoTo errHandler

Dim CmdCloseMsg As String
CmdCloseMsg = MsgBox("Save new facilitator and close?", vbOKCancel + vbDefaultButton1, "Close form?")
If (CmdCloseMsg = vbOK) Then
    DoCmd.RunCommand acCmdSaveRecord
    'If this form was opened from the ZooMobile Ed Update form, EdFormTxt control is set to "ZM"
    If Me.EdFormTxt = "ZM" Then
        DoCmd.Requery Forms![ZooMobile Eduction Update Form]!FacilitatorSubform.Form!DocentTxt
        DoCmd.Requery Forms![ZooMobile Eduction Update Form]!ShadowSubform.Form!ShadowTxt
    End If
    DoCmd.Close acForm, Me.Name
End If

Exit Sub

errHandler:
If Err.Number = 2046 Then
  Resume Next
End If

End Sub

 

[JHB]

Post a stripped down version of your database with some sample data in it.
I don't think you quiet understand my suggestion about comment out the errorhandling, the I see you still have it on.
When you not comment it out you're grappling in the dark trying to find what cause your problem.

 

[Reese]

I don't think you quiet understand my suggestion about comment out the errorhandling, the I see you still have it on.

I did actually comment it out and that's when the 2450 error code came up. I had simply put it back in as I continued experimenting afterwards. I just commented it back out as you can see below and got the 2450 error code again:

Private Sub CloseCmd_Click()

'On Error GoTo errHandler

Dim CmdCloseMsg As String
CmdCloseMsg = MsgBox("Save new facilitator and close?", vbOKCancel + vbDefaultButton1, "Close form?")
If (CmdCloseMsg = vbOK) Then
    DoCmd.RunCommand acCmdSaveRecord
    'If this form was opened from the ZooMobile Ed Update form, EdFormTxt control is set to "ZM"
    If Me.EdFormTxt = "ZM" Then
        DoCmd.Requery Forms![ZooMobile Eduction Update Form]!FacilitatorSubform!DocentTxt
        DoCmd.Requery Forms![ZooMobile Eduction Update Form]!ShadowSubform!ShadowTxt
    End If
    DoCmd.Close acForm, Me.Name
End If

Exit Sub

errHandler:
If Err.Number = 2046 Then
  Resume Next
End If

End Sub

I had also continued experimenting since my last comment and removed the ".Form" from the DoCmd reference line, something which I have sometimes found to work in other situations. Still it doesn't work.

Post a stripped down version of your database with some sample data in it.

I'll try to get to that, but I don't think I'll be able to. I'm not dedicated to IT and this is but a small part of my duties at work. Right now I'm actually multitasking with other duties.

This database has been in use for over 2 years; the front end is 30 MB and the back end is 3 MB. I'd have to merge the two, take time to delete all of the non-relevant tables, forms, reports and queries, then repopulate certain tables.

 

[sneuberg]

I don't think these requeries were ever working as you have them. If you check https://msdn.microsoft.com/en-us/library/office/ff195253.aspx you will see it says, "Requery action to update the data in a specified control on the active object" . The form ZooMobile Eduction Update Form is not the active object in this case. I suggest you try MarkK's suggested code again.

With Forms![ZooMobile Eduction Update Form]
   !FacilitatorSubform.Form!DocentTxt.Requery
   !ShadowSubform.Form!ShadowTxt.Requery
End With

if you don't like to use With then

Forms![ZooMobile Eduction Update Form]!FacilitatorSubform.Form!DocentTxt.Requery
Forms![ZooMobile Eduction Update Form]!ShadowSubform.Form!ShadowTxt.Requery

I suggest you leave the On Error GoTo errHandler commented out until don't get any errors.

 

[Reese]

I don't think these requeries were ever working as you have them.

I'll accept that perhaps the technique I use wouldn't work, except at one point it did. But if you don't think it should I'll take your advice. I only went back to my original technique because both it and With were working and I had no idea what I was doing regarding the With structure--I had never encountered it before.

I just tried the With technique again and it didn't work, same error code:

Private Sub CloseCmd_Click()

'On Error GoTo errHandler

Dim CmdCloseMsg As String
CmdCloseMsg = MsgBox("Save new facilitator and close?", vbOKCancel + vbDefaultButton1, "Close form?")
If (CmdCloseMsg = vbOK) Then
    DoCmd.RunCommand acCmdSaveRecord
    'If this form was opened from the ZooMobile Ed Update form, EdFormTxt control is set to "ZM"
    With Forms![ZooMobile Eduction Update Form]
        !FacilitatorSubform.Form!DocentTxt.Requery
        !ShadowSubform.Form!ShadowTxt.Requery
    End With
    DoCmd.Close acForm, Me.Name
End If

Exit Sub

errHandler:
If Err.Number = 2046 Then
  Resume Next
End If

End Sub

I also went back to your second suggested alternative and ended with the same result:

Private Sub CloseCmd_Click()

'On Error GoTo errHandler

Dim CmdCloseMsg As String
CmdCloseMsg = MsgBox("Save new facilitator and close?", vbOKCancel + vbDefaultButton1, "Close form?")
If (CmdCloseMsg = vbOK) Then
    DoCmd.RunCommand acCmdSaveRecord
    'If this form was opened from the ZooMobile Ed Update form, EdFormTxt control is set to "ZM"
    If Me.EdFormTxt = "ZM" Then
        Forms![ZooMobile Eduction Update Form]!FacilitatorSubform.Form!DocentTxt.Requery
        Forms![ZooMobile Eduction Update Form]!ShadowSubform.Form!ShadowTxt.Requery
    End If
    DoCmd.Close acForm, Me.Name
End If

Exit Sub

errHandler:
If Err.Number = 2046 Then
  Resume Next
End If

End Sub

 

[jdraw]

I would not comment out the error handling - the more info you can get the better.
I would suggest some breakpoints, some debug.print, local watches and/or step debugging to focus on the issue.

You need an Else in your errorhandler, as has been suggested by Markk in post 6, to trap any other error.
Good luck.

 

[Reese]

I agree that the more info I have the better, but aren't the others right? Wouldn't I get more info by commenting it out?

I would suggest some breakpoints, some debug.print, local watches and/or step debugging to focus on the issue.

I see the logic, but I have never used those and have no idea what I'd be doing. I'll try researching them later when I have time.

You need an Else in your errorhandler, as has been suggested by Markk in post 6, to trap any other error.

I just tried copy/pasting MarkK's suggested code and it comes up red. This is another situation where I have no idea what I'm doing. When compiling it gives me a syntax error.

 

[jdraw]

Change markks code as follows:

errHandler:
If Err.Number = 2046 Then
Resume Next
ELSE
MsgBox "previously un-trapped, and therefore silent error: " & Err.Number & " " & err.description
End If

Also, see the debugging tips in my signature (links to the Chip Pearson stuff)