Second click on Tab Control page/tab re-runs OnChange event - Prev

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

Hello

I have a tab control on a form that displays records selected from a drop
down.

The On Change for the Tab Control is

Private Sub TabCtl0_Change()
DoCmd.Requery
End Sub

Which works fine.

However, if a user clicks one of the tab/page and then clicks the SAME
tab/page (ie the one that is already selected) again, the currently selected
record is lost and the form defaults back to the first record in the
underlying table when they click over to another tab/page. It seems to be
running the OnChange event again even though the page is not changing.

How do I prevent this from happening?

Someone on the forum suggested I create a

"module-level variable in your form's module that keeps track of the current
tab - when the form is open set it to the tab number that is displayed when
the form is open. Then, in the On CHange event, check to see if the new tab
number value is the same as the previous one. If so, this means you're still
on the same tab, so do nothing, otherwise reset the variable to the new tab
number that was selected and do your requery.

This sounds like a good idea but I am not sure how to go about it.

Do I create the module in the same code window that is used for the main
form, or a separate module in the modules objects?

Here is an idea of what I think I need to do:

``````````````````
dim PageNum As Integer

pageNum = TabCtl0.Pages.Item(#PageNum#)

not sure how to get the #PageNum# into the PageNum variable?

``````````````````

Then for the OnChange Event on the TabControl:


``````````````````
Private Sub TabCtl0_Change()

dim PageNum As Integer

if PageNum <> TabCtl0.Pages.Item(PageNum) then

DoCmd.Requery

' else do nothing? does this need to be coded in???

end if

End Sub


``````````````````

Sorry for being such a nube :-)

thanks!
s
 
It seems to be running the OnChange event again even though the
page is not changing.
Click to expand...

That's odd. I was not able to duplicate that behavior, but you might try
the following.

Declare your PageNum variable in the declarations section of your form
module (at the top underneath Option Compare and Option Explicit).

Then in the Change event of the tab control put;

Private Sub TabCtl0_Change()

If PageNum <> Me!TabCtl0.Value Then
DoCmd.Requery
PageNum = Me!TabCtl0.Value
End If

End Sub
 
Hi Beetle -

I think we are getting somewhere but I am getting a "statement invalid
outside Type block" error on the OnChange event

Here is my code - I think I have declared the variables incorrectly...

-----------------------
Option Compare Database
Option Explicit

pagenum As Integer

----------------------
Private Sub TabCtl0_Change()

Dim pagenum As integer

If pagenum <> Me!TabCtl0.Value Then
DoCmd.Requery

pagenum = Me!TabCtl0.Value

End If

End Sub

==============

Also, shouldn't

pagenum = Me!TabCtl0.Value

come before the IF statement?


Thanks again for your help!
sandra
 
The error is because you didn't dimension (Dim) the variable in the
declarations section.

You only need to declare your variable once in the declarations section.
You don't need to declare it again in your procedure. So the code would
look like;

Option Compare Database
Option Explicit

Dim pagenum As Integer

----------------------
Private Sub TabCtl0_Change()

If pagenum <> Me!TabCtl0.Value Then
DoCmd.Requery

pagenum = Me!TabCtl0.Value

End If

End Sub
Also, shouldn't

pagenum = Me!TabCtl0.Value

come before the IF statement?
Click to expand...

No. Let's suppose you're on the second tab (which would have a value
of 1 since it is a zero based index). As soon as you click on the third
tab (which has a value of 2) the procedure will check the value of
the pagenum variable (which will be 1 since you were just on the second
tab). Since it is not equal to the current tab value it will run the requery
and then set the pagenum variable to 2. If you click the third tab again,
the pagenum variable will be equal to the tab value, so nothing will happen.
 
Hi again Sean -

I now do not get an error but the code does not solve the problem. I am
stumped!

I was able to replicate the issue on a new database. It has one table with 3
records, each of which has one ID field and 2 text fields.

First I created a main form using the table as the data source
then I added a drop down using the wizard (find a record based on the value
I select ....) based on the first ID field
I then created two sub-forms, one with the ID and field 2 on it and one with
the ID and field 3 on it.
I then added a tab control to the main form and added the 2 sub-forms to
each of the two tabs in a tab control and linked the records to the main form
via on the ID field
I then set the OnChange Event for the Tab control to DoCmd.Requery

try this to test:

Select the third record from the drop down on the main form
click on the second tab - you will see record 3, field 3
click on the second tab AGAIN - no change in data but the button gets a
slight dashed line around it
click on the first tab - you will see record 1 field 2 - we should be seeing
record 3 field 1

man this is frustrating!

thanks again,
sandra
 
OK, I see what's happening.

Let's say you select record three from the combo box in your main form.
Both of the subforms will subsequently move to the matching record ID.
Now, the first time you click a new tab the focus doesn't actually go to
the tab, it goes to the subform contained in that tab. So when the Change
event fires, the Requery acts upon the SUBFORM because it has the
focus. Since nothing has changed in the main form, the subform returns
to the same record after the Requery.

However, if you click that same tab a *second* time, focus now leaves
the subform and goes to the tab control itself. Since the tab control is part
of the main form, the focus is now effectively on the main form. This time,
when you change tabs, the Requery acts upon the MAIN FORM since it has
the focus. The main form automatically returns to the first record after the
Requery (which is normal) and each subform subsequently moves back to
the first record as well.

So, what you're seeing is actually normal behavior based on your code.

The question then becomes, what do you *want* to happen?
 
You are genius!

What I need to achieve is this: When a user enters a value in a field on one
sub form, eg the quantity of an item - the calculations on the other sub
forms - for that same record that use the quantity in the formula - need to
be updated.

The tab control and its sub forms are actually based mostly on data for one
record in one table - it is just that there are so many fields in the record,
it was better to organize the data in separate forms.

Ok, so what I need is to either
a) disable the second click on the active tab
b) move the DoCmd.Requery to an event on each tab

I tried various approaches for b) but did not have luck as the data in the
calculated fields of the subsequent forms did not updateérecalculate.

The event should requery the data in the underlying record and update the
calculations using the data on the current subform, and then move the focus
to the new page.

Thanks again for all your help - i really appreciate this!

sandra
 
Requery your subforms explicitly instead of just doing a generic requery.
You can do this with the method;

Me![SubFormControl].Requery

Keep in mind that you need to refer to the name of the *Subform Control*
(the "window" that displays the subform). Depending on how you added
the subforms, the subform control may, or may not, have the same name
as the subform itself.

There are a couple of ways you could approach it. You could requery
every subform each time you change tabs like this;

Private Sub TabCtl0_Change()

Me![TheFirstSubformControl].Requery
Me![TheSecondSubformControl].Requery
Me![TheThirdSubformControl].Requery
'etc.

End Sub

but that may not be necessary and could slow down your form. Another
option would be to just requery the subform that is on the tab that was
selected by looking at the tab control value (remeber it is a zero based
index);

Private Sub TabCtl0_Change()

Select Case Me!TabCtl0
Case 0
Me![TheFirstSubformControl].Requery
Case 1
Me![TheSecondSubformControl].Requery
Case 2
Me![TheThirdSubformControl].Requery
'etc.
End Select

End Sub


On a side note, if your table has so many fields that you have to split
them up amongst multiple forms to have a useable interface, then there
is a very good chance you have a faulty table design. In most cases, any
table with more than, say, 20 or 30 fields is not properly normalized.
 
Hi Sean -

Thanks again for all your help.

Long story short, after playing with this I got this to work. Seems that the
Requery on one particular form messed everything up but once I removed that
form from the sub all worked fine.

I have just one question now. On the form (Form X) that I had to remove, I
still need to be able to save it if someone makes a change.

If someone is in Form X, changes a field in one record and hits enter or
moves to another field, then all is well. If, however, they update the field
and and click on a new Tab before exiting the updated record, the refresh
does not work first time. It does work if you go to a second tab and then
come back.

So I need to save the currently active record on Form X before the form
loses its focus.

I have tried on lost focus, on change wiht no luck. The Save needs to run
before the OnChange sub for the TabCtrl

I also tried DoCmd.Save as the first line of the OnChange for the TabCtrl
but based on the Beep test sequence, it seems to change to the new tab before
doing the save.

Again, your help greatly appreciated
Many thanks,
sandra









Beetle said:
Requery your subforms explicitly instead of just doing a generic requery.
You can do this with the method;

Me![SubFormControl].Requery

Keep in mind that you need to refer to the name of the *Subform Control*
(the "window" that displays the subform). Depending on how you added
the subforms, the subform control may, or may not, have the same name
as the subform itself.

There are a couple of ways you could approach it. You could requery
every subform each time you change tabs like this;

Private Sub TabCtl0_Change()

Me![TheFirstSubformControl].Requery
Me![TheSecondSubformControl].Requery
Me![TheThirdSubformControl].Requery
'etc.

End Sub

but that may not be necessary and could slow down your form. Another
option would be to just requery the subform that is on the tab that was
selected by looking at the tab control value (remeber it is a zero based
index);

Private Sub TabCtl0_Change()

Select Case Me!TabCtl0
Case 0
Me![TheFirstSubformControl].Requery
Case 1
Me![TheSecondSubformControl].Requery
Case 2
Me![TheThirdSubformControl].Requery
'etc.
End Select

End Sub


On a side note, if your table has so many fields that you have to split
them up amongst multiple forms to have a useable interface, then there
is a very good chance you have a faulty table design. In most cases, any
table with more than, say, 20 or 30 fields is not properly normalized.

--
_________

Sean Bailey


Sandy said:
You are genius!

What I need to achieve is this: When a user enters a value in a field on one
sub form, eg the quantity of an item - the calculations on the other sub
forms - for that same record that use the quantity in the formula - need to
be updated.

The tab control and its sub forms are actually based mostly on data for one
record in one table - it is just that there are so many fields in the record,
it was better to organize the data in separate forms.

Ok, so what I need is to either
a) disable the second click on the active tab
b) move the DoCmd.Requery to an event on each tab

I tried various approaches for b) but did not have luck as the data in the
calculated fields of the subsequent forms did not updateérecalculate.

The event should requery the data in the underlying record and update the
calculations using the data on the current subform, and then move the focus
to the new page.

Thanks again for all your help - i really appreciate this!

sandra
Click to expand...
Click to expand...
 
Well, this is another case where I could not duplicate the problem you
describe, but that might not be possible without knowing what the
original problem was with Form X, etc.

At any rate, you might try this as the first line in your tab control
Change event;

If Me![FormXSubformControl].Form.Dirty Then
Me![FormXSubformControl].Form.Dirty = False

(the above should be all on one line in your code)

Again, remember that you need to refer to the Subform Control, plus,
in this case you need to add the .Form reference.

Just as a point of information, generic methods like DoCmd.Save and
DoCmd.Requery are not always what they they might seem. For example,
DoCmd.Save does *not* save the current record, it saves the current
*object* (like a form, report, etc.). DoCmd.Requery is much slower
than doing a direct requery of an object like;

Me.Requery

-or-

Me![SubformControl].Requery


DoCmd.Requery is only provided for backwards compatibility
with Access 95.

--
_________

Sean Bailey


Sandy said:
Hi Sean -

Thanks again for all your help.

Long story short, after playing with this I got this to work. Seems that the
Requery on one particular form messed everything up but once I removed that
form from the sub all worked fine.

I have just one question now. On the form (Form X) that I had to remove, I
still need to be able to save it if someone makes a change.

If someone is in Form X, changes a field in one record and hits enter or
moves to another field, then all is well. If, however, they update the field
and and click on a new Tab before exiting the updated record, the refresh
does not work first time. It does work if you go to a second tab and then
come back.

So I need to save the currently active record on Form X before the form
loses its focus.

I have tried on lost focus, on change wiht no luck. The Save needs to run
before the OnChange sub for the TabCtrl

I also tried DoCmd.Save as the first line of the OnChange for the TabCtrl
but based on the Beep test sequence, it seems to change to the new tab before
doing the save.

Again, your help greatly appreciated
Many thanks,
sandra









Beetle said:
Requery your subforms explicitly instead of just doing a generic requery.
You can do this with the method;

Me![SubFormControl].Requery

Keep in mind that you need to refer to the name of the *Subform Control*
(the "window" that displays the subform). Depending on how you added
the subforms, the subform control may, or may not, have the same name
as the subform itself.

There are a couple of ways you could approach it. You could requery
every subform each time you change tabs like this;

Private Sub TabCtl0_Change()

Me![TheFirstSubformControl].Requery
Me![TheSecondSubformControl].Requery
Me![TheThirdSubformControl].Requery
'etc.

End Sub

but that may not be necessary and could slow down your form. Another
option would be to just requery the subform that is on the tab that was
selected by looking at the tab control value (remeber it is a zero based
index);

Private Sub TabCtl0_Change()

Select Case Me!TabCtl0
Case 0
Me![TheFirstSubformControl].Requery
Case 1
Me![TheSecondSubformControl].Requery
Case 2
Me![TheThirdSubformControl].Requery
'etc.
End Select

End Sub


On a side note, if your table has so many fields that you have to split
them up amongst multiple forms to have a useable interface, then there
is a very good chance you have a faulty table design. In most cases, any
table with more than, say, 20 or 30 fields is not properly normalized.

--
_________

Sean Bailey


Sandy said:
You are genius!

What I need to achieve is this: When a user enters a value in a field on one
sub form, eg the quantity of an item - the calculations on the other sub
forms - for that same record that use the quantity in the formula - need to
be updated.

The tab control and its sub forms are actually based mostly on data for one
record in one table - it is just that there are so many fields in the record,
it was better to organize the data in separate forms.

Ok, so what I need is to either
a) disable the second click on the active tab
b) move the DoCmd.Requery to an event on each tab

I tried various approaches for b) but did not have luck as the data in the
calculated fields of the subsequent forms did not updateérecalculate.

The event should requery the data in the underlying record and update the
calculations using the data on the current subform, and then move the focus
to the new page.

Thanks again for all your help - i really appreciate this!

sandra


:

OK, I see what's happening.

Let's say you select record three from the combo box in your main form.
Both of the subforms will subsequently move to the matching record ID.
Now, the first time you click a new tab the focus doesn't actually go to
the tab, it goes to the subform contained in that tab. So when the Change
event fires, the Requery acts upon the SUBFORM because it has the
focus. Since nothing has changed in the main form, the subform returns
to the same record after the Requery.

However, if you click that same tab a *second* time, focus now leaves
the subform and goes to the tab control itself. Since the tab control is part
of the main form, the focus is now effectively on the main form. This time,
when you change tabs, the Requery acts upon the MAIN FORM since it has
the focus. The main form automatically returns to the first record after the
Requery (which is normal) and each subform subsequently moves back to
the first record as well.

So, what you're seeing is actually normal behavior based on your code.

The question then becomes, what do you *want* to happen?


--
_________

Sean Bailey


:

Hi again Sean -

I now do not get an error but the code does not solve the problem. I am
stumped!

I was able to replicate the issue on a new database. It has one table with 3
records, each of which has one ID field and 2 text fields.

First I created a main form using the table as the data source
then I added a drop down using the wizard (find a record based on the value
I select ....) based on the first ID field
I then created two sub-forms, one with the ID and field 2 on it and one with
the ID and field 3 on it.
I then added a tab control to the main form and added the 2 sub-forms to
each of the two tabs in a tab control and linked the records to the main form
via on the ID field
I then set the OnChange Event for the Tab control to DoCmd.Requery

try this to test:

Select the third record from the drop down on the main form
click on the second tab - you will see record 3, field 3
click on the second tab AGAIN - no change in data but the button gets a
slight dashed line around it
click on the first tab - you will see record 1 field 2 - we should be seeing
record 3 field 1

man this is frustrating!

thanks again,
sandra



:

The error is because you didn't dimension (Dim) the variable in the
declarations section.

You only need to declare your variable once in the declarations section.
You don't need to declare it again in your procedure. So the code would
look like;

Option Compare Database
Option Explicit

Dim pagenum As Integer

----------------------
Private Sub TabCtl0_Change()

If pagenum <> Me!TabCtl0.Value Then
DoCmd.Requery

pagenum = Me!TabCtl0.Value

End If

End Sub

Also, shouldn't

pagenum = Me!TabCtl0.Value

come before the IF statement?

No. Let's suppose you're on the second tab (which would have a value
of 1 since it is a zero based index). As soon as you click on the third
tab (which has a value of 2) the procedure will check the value of
the pagenum variable (which will be 1 since you were just on the second
tab). Since it is not equal to the current tab value it will run the requery
and then set the pagenum variable to 2. If you click the third tab again,
the pagenum variable will be equal to the tab value, so nothing will happen.

--
_________

Sean Bailey


:

Hi Beetle -

I think we are getting somewhere but I am getting a "statement invalid
outside Type block" error on the OnChange event

Here is my code - I think I have declared the variables incorrectly...

-----------------------
Option Compare Database
Option Explicit

pagenum As Integer

----------------------
Private Sub TabCtl0_Change()

Dim pagenum As integer

If pagenum <> Me!TabCtl0.Value Then
DoCmd.Requery

pagenum = Me!TabCtl0.Value

End If

End Sub

==============

Also, shouldn't

pagenum = Me!TabCtl0.Value

come before the IF statement?


Thanks again for your help!
sandra


:

It seems to be running the OnChange event again even though the
page is not changing.

That's odd. I was not able to duplicate that behavior, but you might try
the following.

Declare your PageNum variable in the declarations section of your form
module (at the top underneath Option Compare and Option Explicit).

Then in the Change event of the tab control put;

Private Sub TabCtl0_Change()

If PageNum <> Me!TabCtl0.Value Then
DoCmd.Requery
PageNum = Me!TabCtl0.Value
End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Sean -

Thank you for all your help on this - I really learned a lot and very much
appreciate the time you took to help me solve this issue.

All the best,
sandra


Beetle said:
Well, this is another case where I could not duplicate the problem you
describe, but that might not be possible without knowing what the
original problem was with Form X, etc.

At any rate, you might try this as the first line in your tab control
Change event;

If Me![FormXSubformControl].Form.Dirty Then
Me![FormXSubformControl].Form.Dirty = False

(the above should be all on one line in your code)

Again, remember that you need to refer to the Subform Control, plus,
in this case you need to add the .Form reference.

Just as a point of information, generic methods like DoCmd.Save and
DoCmd.Requery are not always what they they might seem. For example,
DoCmd.Save does *not* save the current record, it saves the current
*object* (like a form, report, etc.). DoCmd.Requery is much slower
than doing a direct requery of an object like;

Me.Requery

-or-

Me![SubformControl].Requery


DoCmd.Requery is only provided for backwards compatibility
with Access 95.

--
_________

Sean Bailey


Sandy said:
Hi Sean -

Thanks again for all your help.

Long story short, after playing with this I got this to work. Seems that the
Requery on one particular form messed everything up but once I removed that
form from the sub all worked fine.

I have just one question now. On the form (Form X) that I had to remove, I
still need to be able to save it if someone makes a change.

If someone is in Form X, changes a field in one record and hits enter or
moves to another field, then all is well. If, however, they update the field
and and click on a new Tab before exiting the updated record, the refresh
does not work first time. It does work if you go to a second tab and then
come back.

So I need to save the currently active record on Form X before the form
loses its focus.

I have tried on lost focus, on change wiht no luck. The Save needs to run
before the OnChange sub for the TabCtrl

I also tried DoCmd.Save as the first line of the OnChange for the TabCtrl
but based on the Beep test sequence, it seems to change to the new tab before
doing the save.

Again, your help greatly appreciated
Many thanks,
sandra









Beetle said:
Requery your subforms explicitly instead of just doing a generic requery.
You can do this with the method;

Me![SubFormControl].Requery

Keep in mind that you need to refer to the name of the *Subform Control*
(the "window" that displays the subform). Depending on how you added
the subforms, the subform control may, or may not, have the same name
as the subform itself.

There are a couple of ways you could approach it. You could requery
every subform each time you change tabs like this;

Private Sub TabCtl0_Change()

Me![TheFirstSubformControl].Requery
Me![TheSecondSubformControl].Requery
Me![TheThirdSubformControl].Requery
'etc.

End Sub

but that may not be necessary and could slow down your form. Another
option would be to just requery the subform that is on the tab that was
selected by looking at the tab control value (remeber it is a zero based
index);

Private Sub TabCtl0_Change()

Select Case Me!TabCtl0
Case 0
Me![TheFirstSubformControl].Requery
Case 1
Me![TheSecondSubformControl].Requery
Case 2
Me![TheThirdSubformControl].Requery
'etc.
End Select

End Sub


On a side note, if your table has so many fields that you have to split
them up amongst multiple forms to have a useable interface, then there
is a very good chance you have a faulty table design. In most cases, any
table with more than, say, 20 or 30 fields is not properly normalized.

--
_________

Sean Bailey


:

You are genius!

What I need to achieve is this: When a user enters a value in a field on one
sub form, eg the quantity of an item - the calculations on the other sub
forms - for that same record that use the quantity in the formula - need to
be updated.

The tab control and its sub forms are actually based mostly on data for one
record in one table - it is just that there are so many fields in the record,
it was better to organize the data in separate forms.

Ok, so what I need is to either
a) disable the second click on the active tab
b) move the DoCmd.Requery to an event on each tab

I tried various approaches for b) but did not have luck as the data in the
calculated fields of the subsequent forms did not updateérecalculate.

The event should requery the data in the underlying record and update the
calculations using the data on the current subform, and then move the focus
to the new page.

Thanks again for all your help - i really appreciate this!

sandra


:

OK, I see what's happening.

Let's say you select record three from the combo box in your main form.
Both of the subforms will subsequently move to the matching record ID.
Now, the first time you click a new tab the focus doesn't actually go to
the tab, it goes to the subform contained in that tab. So when the Change
event fires, the Requery acts upon the SUBFORM because it has the
focus. Since nothing has changed in the main form, the subform returns
to the same record after the Requery.

However, if you click that same tab a *second* time, focus now leaves
the subform and goes to the tab control itself. Since the tab control is part
of the main form, the focus is now effectively on the main form. This time,
when you change tabs, the Requery acts upon the MAIN FORM since it has
the focus. The main form automatically returns to the first record after the
Requery (which is normal) and each subform subsequently moves back to
the first record as well.

So, what you're seeing is actually normal behavior based on your code.

The question then becomes, what do you *want* to happen?


--
_________

Sean Bailey


:

Hi again Sean -
Click to expand...
I now do not get an error but the code does not solve the problem. I am
stumped!

I was able to replicate the issue on a new database. It has one table with 3
records, each of which has one ID field and 2 text fields.

First I created a main form using the table as the data source
then I added a drop down using the wizard (find a record based on the value
I select ....) based on the first ID field
I then created two sub-forms, one with the ID and field 2 on it and one with
the ID and field 3 on it.
I then added a tab control to the main form and added the 2 sub-forms to
each of the two tabs in a tab control and linked the records to the main form
via on the ID field
I then set the OnChange Event for the Tab control to DoCmd.Requery

try this to test:

Select the third record from the drop down on the main form
click on the second tab - you will see record 3, field 3
click on the second tab AGAIN - no change in data but the button gets a
slight dashed line around it
click on the first tab - you will see record 1 field 2 - we should be seeing
record 3 field 1

man this is frustrating!

thanks again,
sandra



:

The error is because you didn't dimension (Dim) the variable in the
declarations section.

You only need to declare your variable once in the declarations section.
You don't need to declare it again in your procedure. So the code would
look like;

Option Compare Database
Option Explicit

Dim pagenum As Integer

----------------------
Private Sub TabCtl0_Change()

If pagenum <> Me!TabCtl0.Value Then
DoCmd.Requery

pagenum = Me!TabCtl0.Value

End If

End Sub

Also, shouldn't

pagenum = Me!TabCtl0.Value

come before the IF statement?

No. Let's suppose you're on the second tab (which would have a value
of 1 since it is a zero based index). As soon as you click on the third
tab (which has a value of 2) the procedure will check the value of
the pagenum variable (which will be 1 since you were just on the second
tab). Since it is not equal to the current tab value it will run the requery
and then set the pagenum variable to 2. If you click the third tab again,
the pagenum variable will be equal to the tab value, so nothing will happen.

--
_________

Sean Bailey


:

Hi Beetle -

I think we are getting somewhere but I am getting a "statement invalid
outside Type block" error on the OnChange event

Here is my code - I think I have declared the variables incorrectly...

-----------------------
Option Compare Database
Option Explicit

pagenum As Integer

----------------------
Private Sub TabCtl0_Change()

Dim pagenum As integer

If pagenum <> Me!TabCtl0.Value Then
DoCmd.Requery

pagenum = Me!TabCtl0.Value
Click to expand...
Click to expand...
Click to expand...
 
Glad I could help. :-)
--
_________

Sean Bailey


Sandy said:
Hi Sean -

Thank you for all your help on this - I really learned a lot and very much
appreciate the time you took to help me solve this issue.

All the best,
sandra


Beetle said:
Well, this is another case where I could not duplicate the problem you
describe, but that might not be possible without knowing what the
original problem was with Form X, etc.

At any rate, you might try this as the first line in your tab control
Change event;

If Me![FormXSubformControl].Form.Dirty Then
Me![FormXSubformControl].Form.Dirty = False

(the above should be all on one line in your code)

Again, remember that you need to refer to the Subform Control, plus,
in this case you need to add the .Form reference.

Just as a point of information, generic methods like DoCmd.Save and
DoCmd.Requery are not always what they they might seem. For example,
DoCmd.Save does *not* save the current record, it saves the current
*object* (like a form, report, etc.). DoCmd.Requery is much slower
than doing a direct requery of an object like;

Me.Requery

-or-

Me![SubformControl].Requery


DoCmd.Requery is only provided for backwards compatibility
with Access 95.

--
_________

Sean Bailey


Sandy said:
Hi Sean -

Thanks again for all your help.

Long story short, after playing with this I got this to work. Seems that the
Requery on one particular form messed everything up but once I removed that
form from the sub all worked fine.

I have just one question now. On the form (Form X) that I had to remove, I
still need to be able to save it if someone makes a change.

If someone is in Form X, changes a field in one record and hits enter or
moves to another field, then all is well. If, however, they update the field
and and click on a new Tab before exiting the updated record, the refresh
does not work first time. It does work if you go to a second tab and then
come back.

So I need to save the currently active record on Form X before the form
loses its focus.

I have tried on lost focus, on change wiht no luck. The Save needs to run
before the OnChange sub for the TabCtrl

I also tried DoCmd.Save as the first line of the OnChange for the TabCtrl
but based on the Beep test sequence, it seems to change to the new tab before
doing the save.

Again, your help greatly appreciated
Many thanks,
sandra









:

Requery your subforms explicitly instead of just doing a generic requery.
You can do this with the method;

Me![SubFormControl].Requery

Keep in mind that you need to refer to the name of the *Subform Control*
(the "window" that displays the subform). Depending on how you added
the subforms, the subform control may, or may not, have the same name
as the subform itself.

There are a couple of ways you could approach it. You could requery
every subform each time you change tabs like this;

Private Sub TabCtl0_Change()

Me![TheFirstSubformControl].Requery
Me![TheSecondSubformControl].Requery
Me![TheThirdSubformControl].Requery
'etc.

End Sub

but that may not be necessary and could slow down your form. Another
option would be to just requery the subform that is on the tab that was
selected by looking at the tab control value (remeber it is a zero based
index);

Private Sub TabCtl0_Change()

Select Case Me!TabCtl0
Case 0
Me![TheFirstSubformControl].Requery
Case 1
Me![TheSecondSubformControl].Requery
Case 2
Me![TheThirdSubformControl].Requery
'etc.
End Select

End Sub


On a side note, if your table has so many fields that you have to split
them up amongst multiple forms to have a useable interface, then there
is a very good chance you have a faulty table design. In most cases, any
table with more than, say, 20 or 30 fields is not properly normalized.

--
_________

Sean Bailey


:

You are genius!

What I need to achieve is this: When a user enters a value in a field on one
sub form, eg the quantity of an item - the calculations on the other sub
forms - for that same record that use the quantity in the formula - need to
be updated.

The tab control and its sub forms are actually based mostly on data for one
record in one table - it is just that there are so many fields in the record,
it was better to organize the data in separate forms.

Ok, so what I need is to either
a) disable the second click on the active tab
b) move the DoCmd.Requery to an event on each tab

I tried various approaches for b) but did not have luck as the data in the
calculated fields of the subsequent forms did not updateérecalculate.

The event should requery the data in the underlying record and update the
calculations using the data on the current subform, and then move the focus
to the new page.

Thanks again for all your help - i really appreciate this!

sandra


:

OK, I see what's happening.

Let's say you select record three from the combo box in your main form.
Both of the subforms will subsequently move to the matching record ID.
Now, the first time you click a new tab the focus doesn't actually go to
the tab, it goes to the subform contained in that tab. So when the Change
event fires, the Requery acts upon the SUBFORM because it has the
focus. Since nothing has changed in the main form, the subform returns
to the same record after the Requery.

However, if you click that same tab a *second* time, focus now leaves
the subform and goes to the tab control itself. Since the tab control is part
of the main form, the focus is now effectively on the main form. This time,
when you change tabs, the Requery acts upon the MAIN FORM since it has
the focus. The main form automatically returns to the first record after the
Requery (which is normal) and each subform subsequently moves back to
the first record as well.

So, what you're seeing is actually normal behavior based on your code.

The question then becomes, what do you *want* to happen?


--
_________

Sean Bailey


:

Hi again Sean -


I now do not get an error but the code does not solve the problem. I am
stumped!

I was able to replicate the issue on a new database. It has one table with 3
records, each of which has one ID field and 2 text fields.

First I created a main form using the table as the data source
then I added a drop down using the wizard (find a record based on the value
I select ....) based on the first ID field
I then created two sub-forms, one with the ID and field 2 on it and one with
the ID and field 3 on it.
I then added a tab control to the main form and added the 2 sub-forms to
each of the two tabs in a tab control and linked the records to the main form
via on the ID field
I then set the OnChange Event for the Tab control to DoCmd.Requery

try this to test:

Select the third record from the drop down on the main form
click on the second tab - you will see record 3, field 3
click on the second tab AGAIN - no change in data but the button gets a
slight dashed line around it
click on the first tab - you will see record 1 field 2 - we should be seeing
record 3 field 1

man this is frustrating!

thanks again,
sandra



:

The error is because you didn't dimension (Dim) the variable in the
declarations section.

You only need to declare your variable once in the declarations section.
You don't need to declare it again in your procedure. So the code would
look like;

Option Compare Database
Option Explicit

Dim pagenum As Integer

----------------------
Private Sub TabCtl0_Change()

If pagenum <> Me!TabCtl0.Value Then
DoCmd.Requery

pagenum = Me!TabCtl0.Value

End If

End Sub

Also, shouldn't

pagenum = Me!TabCtl0.Value

come before the IF statement?

No. Let's suppose you're on the second tab (which would have a value
of 1 since it is a zero based index). As soon as you click on the third
tab (which has a value of 2) the procedure will check the value of
the pagenum variable (which will be 1 since you were just on the second
tab). Since it is not equal to the current tab value it will run the requery
and then set the pagenum variable to 2. If you click the third tab again,
the pagenum variable will be equal to the tab value, so nothing will happen.

--
_________

Sean Bailey


:

Hi Beetle -

I think we are getting somewhere but I am getting a "statement invalid
outside Type block" error on the OnChange event

Here is my code - I think I have declared the variables incorrectly...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top