Refresh data on form tab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I have a form with 5 tabs. The first tab is editable, and the other 4 are
just reports relating to the data on the first. When I change the data on
the first form, this is not reflected in the other tabs. I've read, and
read, and read posts on here without success. The nearest I can see is
Me.Requery - but Access comes back with 'Macro not found'. Please point me
in the right direction! I always coped with Access (2000) but I must be
losing my touch with this one!
Thanks.
 
Andy said:
Hi
I have a form with 5 tabs. The first tab is editable, and the other 4
are just reports relating to the data on the first. When I change the
data on the first form, this is not reflected in the other tabs. I've
read, and read, and read posts on here without success. The nearest I
can see is Me.Requery - but Access comes back with 'Macro not found'.
Please point me in the right direction! I always coped with Access
(2000) but I must be losing my touch with this one!
Thanks.
Click to expand...

Do the other tabs have subforms on them? If so you need to issue a Requery on
the subforms. If they just have calculated controls then Requery, Refresh, or
Recalc might all do what you want.

Sounds like you typed "Me.Requery" directly into the event property box. That's
not how code events work. In the event property box you type [Event Procedure]
and then press the build button [...] to the right. That will take you to the
VBA code editor window and THAT is where Me.Requery goes. In between the Sub
and End Sub lines that will be auto-created for you.
 
Hi Rick - and thanks for the reply!!

I tried the Me.Requery in the OnClick of the tab. That doesn't work (as you
said it wouldn't). The only options on the subform are OnEnter and OnExit.
I'm not doing either of these (purely viewing) so where do I put the
Me.Requery? I don't want to requery the first tab, either, as I want it to
stay displaying the record that is already on it - not jump to the first
record.
Thanks.

Rick Brandt said:
Andy said:
Hi
I have a form with 5 tabs. The first tab is editable, and the other 4
are just reports relating to the data on the first. When I change the
data on the first form, this is not reflected in the other tabs. I've
read, and read, and read posts on here without success. The nearest I
can see is Me.Requery - but Access comes back with 'Macro not found'.
Please point me in the right direction! I always coped with Access
(2000) but I must be losing my touch with this one!
Thanks.
Click to expand...

Do the other tabs have subforms on them? If so you need to issue a
Requery on the subforms. If they just have calculated controls then
Requery, Refresh, or Recalc might all do what you want.

Sounds like you typed "Me.Requery" directly into the event property box.
That's not how code events work. In the event property box you type
[Event Procedure] and then press the build button [...] to the right.
That will take you to the VBA code editor window and THAT is where
Me.Requery goes. In between the Sub and End Sub lines that will be
auto-created for you.
Click to expand...
 
Andy said:
Hi Rick - and thanks for the reply!!

I tried the Me.Requery in the OnClick of the tab. That doesn't work
(as you said it wouldn't). The only options on the subform are
OnEnter and OnExit. I'm not doing either of these (purely viewing) so
where do I put the Me.Requery? I don't want to requery the first tab,
either, as I want it to stay displaying the record that is already on
it - not jump to the first record.
Thanks.
Click to expand...

The OnClick of a TabPage is not what you think it is. Use the Change event of
the entire TabControl. You can use its Value property to determine which page
was just switched to.
 
Hi Rick

I've got:

Private Sub TabCtl85_Change()
Select Case Me!TabCtl85.Value
Case 1
Forms!Form1!TabCtl85 = 1
Me.Requery
'do something else
End Select
End Sub

When I change a field on page 0 (the first page) and click on page 1, the
query/report does not show the changes I've just made. If I press F9 or
shift-F9 it does. When I go back to page 0, it has returned to the first
record (which I don't want) whether I've pressed F9 or not. That is the
reason that I tried the 'Forms!Form1!TabCtl85 = 1' bit in the above.
Thanks again!
 
Andy said:
Hi Rick

I've got:

Private Sub TabCtl85_Change()
Select Case Me!TabCtl85.Value
Case 1
Forms!Form1!TabCtl85 = 1
Me.Requery
'do something else
End Select
End Sub
Click to expand...

Your Case 1 code is going to run when the user selects the tab page with an
index value of 1. There is no reason for your code to then set the TabControl's
value to 1 (it already IS 1).
When I change a field on page 0 (the first page) and click on page 1,
the query/report does not show the changes I've just made. If I press
F9 or shift-F9 it does. When I go back to page 0, it has returned to
the first record (which I don't want) whether I've pressed F9 or not.
That is the reason that I tried the 'Forms!Form1!TabCtl85 = 1' bit in
the above. Thanks again!
Click to expand...

You have to save the change made on Page 0 and then Requery the subform on Page
1...

Private Sub TabCtl85_Change()
Select Case Me!TabCtl85.Value
Case 1
Me.Dirty = False
Me.SubformControlName.Requery
End Select
End Sub
 
Hi Rick - and thanks again!
We're getting there. I've used your code and extended it for the other tabs.
It now looks like this:

Private Sub TabCtl85_Change()
Select Case Me!TabCtl85.Value
Case 1
Me.Dirty = False
Me.frmNonCP.Requery
Case 2
Me.Dirty = False
Me.frmThisWeek.Requery
Case 3
Me.Dirty = False
'Me.frmNextWeek.Requery
Case 4
Me.Dirty = False
Me.frmWeekAfter.Requery
Case 5
Me.Dirty = False
Me.frmLastWeek.Requery
Case 6
Me.Dirty = False
Me.frmLastMonth.Requery
Case 7
Me.Dirty = False
Me.frmThisMonth.Requery
End Select
End Sub

When I hit a tab (2nd one, for example) I get an error that the 'Method or
data member not found' and 'Me.frmNextWeek.Requery is highlighted (which is
why it's rem'd). When I rem it, the code errors on the next section on the
Me.frmWeekAfter.Requery line. All of the forms are within the database, and
work fine when they're not included in the script.

Cheers.
 
Andy said:
Hi Rick - and thanks again!
We're getting there. I've used your code and extended it for the
other tabs. It now looks like this:

Private Sub TabCtl85_Change()
Select Case Me!TabCtl85.Value
Case 1
Me.Dirty = False
Me.frmNonCP.Requery
Case 2
Me.Dirty = False
Me.frmThisWeek.Requery
Case 3
Me.Dirty = False
'Me.frmNextWeek.Requery
Case 4
Me.Dirty = False
Me.frmWeekAfter.Requery
Case 5
Me.Dirty = False
Me.frmLastWeek.Requery
Case 6
Me.Dirty = False
Me.frmLastMonth.Requery
Case 7
Me.Dirty = False
Me.frmThisMonth.Requery
End Select
End Sub

When I hit a tab (2nd one, for example) I get an error that the
'Method or data member not found' and 'Me.frmNextWeek.Requery is
highlighted (which is why it's rem'd). When I rem it, the code errors
on the next section on the Me.frmWeekAfter.Requery line. All of the
forms are within the database, and work fine when they're not
included in the script.
Click to expand...

In that code the identifier following Me. needs to be the name of the subform
*control* which might not be the same as the name of the form it references.
 
Rick Brandt said:
In that code the identifier following Me. needs to be the name of the
subform *control* which might not be the same as the name of the form it
references.
Click to expand...


I believe it should be Me.NameOfSubformControl.Form.Requery
 
Douglas said:
I believe it should be Me.NameOfSubformControl.Form.Requery
Click to expand...

That will also work, but I have never had a problem just issuing the Requery on
the control.
 
Thank you, thank you, thank you!!!

I often post replies in the Excel forums and I know what it's like to solve
something for someone. Now I know it's just as nice to be the 'someone' who
has something solved for them!

Cheers.
 
Back
Top