UnDo macro command works on one page, but not the other 3

[ColdDay]

I have a form in AC2010 with 4 tabbed pages: Add, Edit, Reports and Tables. On the Add page I have a button and macro for clearing the data in the text boxes before saving the info. The macro name is mcr_ClearAddForm. It uses the Undo Record command. It works as it should no matter how many text boxes have info in them.

On the Reports page, I have a macro with the name mcr_ClearReportsForm. The only differences b/w the two macros are: they are for two different pages, they have different names and the GoToControl (after the boxes are cleared) is different. They both use the UnDo Record command.

As I mentioned above, the macro on the Add page works perfectly, but the macro on the Reports page does not clear the text boxes, but it does go to the desired GoToControl.

Is there some way that my Reports page is “locked” after I enter info into a box that prevents my macro from working.

Crl+Z works for the Add page but does not work for the Reports page. These are the only two pages where I need the ability to clear the boxes if I do not want to keep or use the info I have entered.

Thanks.

 

[lameid]

Undorecord command... Not familiar with it but my hunch would be that the form that works is bound and hence there is a record to be undone whereas the other form is unbound, probably just controls to filter your report and therefore cannot be undone.

 

[ColdDay]

Right you are. All the boxes on the Add page are bound and all of the boxes on the Reports page are unbound.

Will I need VBA to clear the boxes? I tried the following with no success:

Private Sub btn_ClearFieldsReportsCharts_Click()
'On Error GoTo mcr_ClearViewSelectedRecordsForm_Err

    On Error Resume Next
    DoCmd.RunCommand acCmdUndo
    DoCmd.GoToControl "txt_BeginDate"


mcr_ClearViewSelectedRecordsForm_Exit:
    Exit Sub

mcr_ClearViewSelectedRecordsForm_Err:
    MsgBox Error$
    Resume mcr_ClearViewSelectedRecordsForm_Exit
End Sub

Thoughts/suggestions?

Thanks.

 

[TheAceMan1]

How are ya ColdDay . . .

You've already proven you can't use [blue]DoCmd.RunCommand acCmdUndo[/blue] on unbound controls. The following example will clear all textboxes on the active [blue]tab page[/blue]:

[blue]   Dim TC As TabControl, ctl As Control
   
   Set TC = Me.[purple][B][I]YourTabControlName[/I][/B][/purple]
   
   For Each ctl In TC.Pages(TC).Controls
      If ctl.ControlType = acTextBox Then ctl = Null
   Next
   
   Set TC = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ColdDay]

AceMan,

As will become painfully obvious, I do not know what name to use for the tabbed page. It is page index 2. It's name to the user is Reports/Charts (not just Reports as I stated earlier).

I have tried all of the following with a variety of error messages occurring.

Private Sub btn_ClearFieldsReportsCharts_Click()
Dim TC As TabControl
Dim ctl As Control
   
   'Set TC = Me.YourTabControlName
    
   'Set TC = Me.Form("frm_aaa_TabbedPagesPractice").Controls("tab_Pages") = 2
   'Set TC = Me.Form("frm_aaa_TabbedPagesPractice").PageIndex = 2
   'Set TC = ("2")
   'Set TC = "2"
   'Set TC = Me.Controls("tab_Pages") = 2
   'Set TC = Controls("tab_pages") = 2
   'Set TC = Controls(2)
   'Set TC = Me.Pages(2)
   'Set TC = Me.PageIndex(2)
   'Set TC = PageIndex(2)
   'Set TC = Me.TabControl("Reports/Charts")
   'Set TC = Me.Pages("Reports/Charts")
   
   For Each ctl In TC.Pages(TC).Controls
      If ctl.ControlType = acTextBox Then ctl = Null
   Next
   Set TC = Nothing
   
 
End Sub

Thanks in advance.

 

[PHV]

What about this ?

Private Sub btn_ClearFieldsReportsCharts_Click()
Dim TC As TabControl
Dim ctl As Control
   For Each ctl In TC.Pages(TC).Controls
      If ctl.ControlType = acTextBox Then ctl = Null
   Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry:

Private Sub btn_ClearFieldsReportsCharts_Click()
Dim ctl As Control
   For Each ctl In TC.Pages(TC).Controls
      If ctl.ControlType = acTextBox Then ctl = Null
   Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ColdDay]

PHV,

I believe your first one is correct b/c it declares the variable TC as a TabControl. It's getting the value Set for TC that I think is my issue.

With the TC declared, I then try to run it and get the error message: "Object variable or With block variable not set."

CD.

 

[PHV]

Did you try the second one ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ColdDay]

Yes. Receive error "Variable not defined".

For Each ctl In TC.Pages(TC).Controls

The TC in parenthesis is highlighted on error.

 

[PHV]

OOps, sorry:

Private Sub btn_ClearFieldsReportsCharts_Click()
Dim ctl As Control
   For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Then ctl = Null
   Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ColdDay]

PHV, still no luck. When using your latest code, I get an error message saying that "You must enter a value in the tbl_DisruptionsDetails.ReportDate field." and ctl = Null is highlighted in yellow.

But I'm not trying to clear any fields on the table, I'm trying clear the fields on a tabbed page. Plus, there is a ReportDate value in every record of the table.

When I place the cursor over ctl, the date 4/4/2011 is shown as the value in the variable. This is the date of the very first record in the table. On the tabbed page, I have entered the date 3/07/2012 in the field on the tabbed page.

I tried the following code but got the error message "The setting you entered isn't valid for this property." When I place the cursor over ctl, the value in the variable is 2, which is the page index number. ctl = Null is highlighted in yellow.

Private Sub btn_ClearFieldsReportsCharts_Click()
Dim ctl As Control
   For Each ctl In Me.Controls
      If Not IsNull(ctl) Then ctl = Null
   Next
End Sub

Thanks.

 

[lameid]

Going back to the OP and Aceman's code.... It looks like you want to clear all the controls on the ADD page...

  Dim ctls as Controls, ctl As Control
   
   Set ctls = Me.YourTabControlName.Pages("Add").Controls
   
   For Each ctl In ctls
      If ctl.ControlType = acTextBox Then ctl = Null
   Next
   
   Set ctls = Nothing

 

[ColdDay]

Lameid, still no luck.

The form name is frm_aaa_TabbedPagesPractice.
The Tab Control name is tab_pages.
The page with the controls to clear is Reports/Charts.

Private Sub btn_ClearFieldsReportsCharts_Click()
Dim ctls As Controls, ctl As Control
    
'either below result in type mismatch
   Set ctls = Me.Controls("tab_Pages").Pages("Reports/Charts").Controls

'    Set ctls = Me.tab_Pages.Pages("Reports/Charts").Controls
    
   For Each ctl In ctls
      If ctl.ControlType = acTextBox Then ctl = Null
   Next
   
   Set ctls = Nothing
End Sub

 

[PHV]

The page with the controls to clear is Reports/Charts
No subform in this page ?
Where is located btn_ClearFieldsReportsCharts ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ColdDay]

The form name is “aaa_frm_TabbedPagesPractice”
The form has a tab control named “tab_pages”.
The pages on the tab control are named: “Add/Main”, “Edit/Delete”, “Reports/Charts” and “Tables”.

There is a “btn_ClearAllControls” button on the Add/Main page. It works fine. It is for if someone begins to enter data then decides not to. These fields are bound. This button runs a macro with Undo Record.

The “btn_ClearFieldsReportsCharts” button is on the Reports/Charts page. It does not work. It is for if someone begins to query data for a report, but then decides not to. Some of these fields may have info for the lookup (date ranges, staff member name, location and a few other items), but other fields may be left blank to query all info for that field(s). The vba to filter the data for reports on the Reports/Charts page works as desired.

The fields on Reports/Charts are unbound. There are two text boxes for dates and five combo boxes for other information to filter. There are currently three buttons on the page for filtering and/or printing information.

The “btn_ClearFieldsReportsCharts” button has vba code because I could not get a macro with Undo Record to work with it.

There is no subform on the Reports/Charts page.

 

[TheAceMan1]

[blue]As will become painfully obvious, I do not know what name to use for the tabbed page.[/blue]

This is where you left my code and started receiving variations there of. The code as presented will clear all textboxes of the currently active page ([purple]selected page[/purple]) of the tab control. To be specific with tab page [blue]Reports/Charts[/blue] ... try the following ... note: where just now getting the names of things:

[blue]   Dim TC As TabControl, ctl As Control
   
   Set TC = Me.[purple][b]tab_pages[/b][/purple]
   
   For Each ctl In TC.Pages("[purple][b]Reports / Charts[/b][/purple]").Controls
      If ctl.ControlType = acTextBox Then ctl = Null
   Next
   
   Set TC = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lameid]

The pages on the tab control are named: "Add/Main", "Edit/Delete", "Reports/Charts" and "Tables".

Another thought...

Are you sure you are not confusing the caption property for the page name?

 

[ColdDay]

PHV, You got it! I failed to mention that I also had a few combo boxes, so I added the proper code and it works! Here is what I'm using.

Private Sub btn_ClearFieldsReportsCharts_Click()
Dim TC As TabControl, ctl As Control
   
   Set TC = Me.tab_Pages
   
   For Each ctl In TC.Pages("Reports/Charts").Controls
      If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox _
      Then ctl = Null
   Next
   
   Set TC = Nothing

End Sub

I will make notes in my code for future reference.

Lameid, the names and captions for the boxes are the same. i.e. "Reports/Charts". Bad idea?

Thanks both of you for your help and time in this issue. I noticed a few other bugs last night and will create a different post for them.

THANKS!!!!!!

 

[ColdDay]

In above message, gave final credit to PHV, but should have given final credit to AceMan.

But giving credit to all that helped!

Thanks.