VB record set not refreshing with new id focus.

[Dom Torrez]

Hello,
I have a three forms 1 (main), 2 (sub), 3(subsub). My third level sub form
gets its data from a query which gets an id passed in by the 2nd level sub
form. I'm having problems with getting this query to refresh or reload when
the id has changed. My query for the 3rd level subform seems to run only
once with id = 123, but when I click any of the navigator buttons to switch
between records and the id changes, id = 456, my query keeps the record set
from the original id = 123. Is there a way for me to rerun my query every
time the id changes? I have my VB code including the query for the third
level sub form within the 'On-Open' procedure. An example is I open my
main form (1) with id 123, forms 2 and 3 are loaded as well with id = 123.
All data shows up fine in all forms. But when I switch forms 1 and 2 to now
be focused on id=456 , my 3rd subsub form doesn't re-run the VB and query
with this new id=456, it remains locked to id=123. Does anyone know how I
can get my VB in the 3rd form to re-run every time the id focus changes?
Thanks for the help,
Dom Torrez

 

[Joey Archer]

Have you tried doing a requery on the 3rd sub? In your VB coding on the "on
current" event for the main form and sub form try something like this....

me.subformname.form.secondsubformname.requery

It may just need to requery the query that the second subform uses as it's
recordset.

Joey Archer
MVP wantabee )

 

[Dom Torrez]

Hi Joey,
Thanks for the reply...
So I gave this a shot and the debugger doesn't like the code.
here's what i'm using:

***********************************************
Private Sub Form_Current()
Me.LocalesDataEntry.Form.ReconnaissanceDataEntrySF.Requery
End Sub
***********************************************
I get the following VB Alert:
'Compile Error: Method or Datamember not found'

The 'LocalesDataEntry' section of the code is highlighted as well.
Now I triple checked my form names and they are correct. Am I possibly
using incorrect syntax for that line of code?

Thanks,

 

[Marshall Barton]

If that line of code is in the main form's Current event,
then it should be:

Me.LocalesDataEntry.Form.ReconnaissanceDataEntrySF.FORM.Requery

where LocalesDataEntry and ReconnaissanceDataEntrySF are the
names of the subform ***controls***, which may be different
from the name of the form object the controls are
displaying.

All this confusion can be avoided by using the subform
control's Link Master/Child Fields properties so Access
takes care of synchronizing the data automatically.

 

[Dom Torrez]

Thanks for the suggestions.
LocalesDataEntry and ReconnaissanceDataEntrySF are the names of my FORMS.
LocalesDateEntry is my master form and ReconnaissanceDataEntrySF is my
subform, and theres actually one more level to go down. ReconImagesSSF is my
subsubform.
Here's the tree:
master: LocalesDateEntry
child: ReconnaissanceDataEntrySF --[contains (idrecon)]
grandchild: ReconImagesSSF

Now my subsubform or the 'grandchild' form is completely controlled by VB.
all the records and fields provided in this form are gotten from my VB code
on this form.
In the 'onopen()' event of the grandchild formI run a query passing in the
parameter (idrecon) from the child form then save the results to a record set
and take this query result recordset and assign it to the form as
such...(me.recordset = queryresultRS).
Because of my code being in the 'on open' event of the grandchild form, and
because this code is using a passed in parameter (idrecon) from the child
form (one level up), if the idrecon ever changes my grandchild form never
recognizes this.
Because this grandchild forms control course is VB, I have no way to use the
Link Master/Child Fields properties. I get the message stating that I cannot
set these properties with an unbound form.
Is there any way to do this same thing in VB?

What I need to do is have my application realize that the idrecon has
changed in my child form, then take this "new" idrecon and refeed/rerun the
VB code query on my grandchild form.
Sorry for being so long winded, I'm getting desperate to find a solution.

Thanks for all the help.
Dom Torrez

If that line of code is in the main form's Current event,
then it should be:

Me.LocalesDataEntry.Form.ReconnaissanceDataEntrySF.FORM.Requery

where LocalesDataEntry and ReconnaissanceDataEntrySF are the
names of the subform ***controls***, which may be different
from the name of the form object the controls are
displaying.

All this confusion can be avoided by using the subform
control's Link Master/Child Fields properties so Access
takes care of synchronizing the data automatically.
--
Marsh
MVP [MS Access]

here's what i'm using:

***********************************************
Private Sub Form_Current()
Me.LocalesDataEntry.Form.ReconnaissanceDataEntrySF.Requery
End Sub
***********************************************
I get the following VB Alert:
'Compile Error: Method or Datamember not found'

The 'LocalesDataEntry' section of the code is highlighted as well.
Now I triple checked my form names and they are correct. Am I possibly
using incorrect syntax for that line of code?

 

[Marshall Barton]

Well, this is more complicated than your original question,
but let's see how far we can get.

The place that can change the idrecon value in the subform
is:
a) When the subform changes its current record, probably by
user navigation in either the subform or the main form. In
this case the code to resync the subsubform should be in the
subform's Current event.

b) When a user edits the value of a control bound to the
idrecon field. Use the control's AfterUpdate event.

c) When some other code modifies the value of a control
bound to the idrecon field. Use the code that modified the
value.

d) ? ? ?

In any/all of those cases, you need to call the subsubform's
procedure that you used to create the subsubform's
recordset. Move that code out of the Open event and place
it in its own public procedure in the subsubform's module.
Making this new procedure Public makes it a method of the
subsubform's class module so you can call it by using the
syntax:

From anywhere:
Forms!LocalesDateEntry.ReconnaissanceDataEntrySF.Form.ReconImagesSSF.Form.nameofprocedure

From the mainform:
Me.ReconnaissanceDataEntrySF.Form.ReconImagesSSF.Form.nameofprocedure

From the subform:
Me.ReconImagesSSF.Form.nameofprocedure

From the subsubform:
nameofprocedure

I don't see a reason to call this procedure from the
subsubform's Open event. The subform's Current event will
call the procedure when it starts up (maybe more than once?)
--
Marsh
MVP [MS Access]

LocalesDataEntry and ReconnaissanceDataEntrySF are the names of my FORMS.
LocalesDateEntry is my master form and ReconnaissanceDataEntrySF is my
subform, and theres actually one more level to go down. ReconImagesSSF is my
subsubform.
Here's the tree:
master: LocalesDateEntry
child: ReconnaissanceDataEntrySF --[contains (idrecon)]
grandchild: ReconImagesSSF

Now my subsubform or the 'grandchild' form is completely controlled by VB.
all the records and fields provided in this form are gotten from my VB code
on this form.
In the 'onopen()' event of the grandchild formI run a query passing in the
parameter (idrecon) from the child form then save the results to a record set
and take this query result recordset and assign it to the form as
such...(me.recordset = queryresultRS).
Because of my code being in the 'on open' event of the grandchild form, and
because this code is using a passed in parameter (idrecon) from the child
form (one level up), if the idrecon ever changes my grandchild form never
recognizes this.
Because this grandchild forms control course is VB, I have no way to use the
Link Master/Child Fields properties. I get the message stating that I cannot
set these properties with an unbound form.
Is there any way to do this same thing in VB?

What I need to do is have my application realize that the idrecon has
changed in my child form, then take this "new" idrecon and refeed/rerun the
VB code query on my grandchild form.
Sorry for being so long winded, I'm getting desperate to find a solution.

If that line of code is in the main form's Current event,
then it should be:

Me.LocalesDataEntry.Form.ReconnaissanceDataEntrySF.FORM.Requery

where LocalesDataEntry and ReconnaissanceDataEntrySF are the
names of the subform ***controls***, which may be different
from the name of the form object the controls are
displaying.

All this confusion can be avoided by using the subform
control's Link Master/Child Fields properties so Access
takes care of synchronizing the data automatically.

 

[Dom Torrez]

This solution worked perfectly. I made a public sub.........
and in my child form using the 'On Current()' event I call my public
function.
Everything is now refreshing as expected.
Thanks so much for all the help.
--Dom

Well, this is more complicated than your original question,
but let's see how far we can get.

The place that can change the idrecon value in the subform
is:
a) When the subform changes its current record, probably by
user navigation in either the subform or the main form. In
this case the code to resync the subsubform should be in the
subform's Current event.

b) When a user edits the value of a control bound to the
idrecon field. Use the control's AfterUpdate event.

c) When some other code modifies the value of a control
bound to the idrecon field. Use the code that modified the
value.

d) ? ? ?

In any/all of those cases, you need to call the subsubform's
procedure that you used to create the subsubform's
recordset. Move that code out of the Open event and place
it in its own public procedure in the subsubform's module.
Making this new procedure Public makes it a method of the
subsubform's class module so you can call it by using the
syntax:

From anywhere:
Forms!LocalesDateEntry.ReconnaissanceDataEntrySF.Form.ReconImagesSSF.Form.nameofprocedure

From the mainform:
Me.ReconnaissanceDataEntrySF.Form.ReconImagesSSF.Form.nameofprocedure

From the subform:
Me.ReconImagesSSF.Form.nameofprocedure

From the subsubform:
nameofprocedure

I don't see a reason to call this procedure from the
subsubform's Open event. The subform's Current event will
call the procedure when it starts up (maybe more than once?)
--
Marsh
MVP [MS Access]

LocalesDataEntry and ReconnaissanceDataEntrySF are the names of my FORMS.
LocalesDateEntry is my master form and ReconnaissanceDataEntrySF is my
subform, and theres actually one more level to go down. ReconImagesSSF is my
subsubform.
Here's the tree:
master: LocalesDateEntry
child: ReconnaissanceDataEntrySF --[contains (idrecon)]
grandchild: ReconImagesSSF

Now my subsubform or the 'grandchild' form is completely controlled by VB.
all the records and fields provided in this form are gotten from my VB code
on this form.
In the 'onopen()' event of the grandchild formI run a query passing in the
parameter (idrecon) from the child form then save the results to a record set
and take this query result recordset and assign it to the form as
such...(me.recordset = queryresultRS).
Because of my code being in the 'on open' event of the grandchild form, and
because this code is using a passed in parameter (idrecon) from the child
form (one level up), if the idrecon ever changes my grandchild form never
recognizes this.
Because this grandchild forms control course is VB, I have no way to use the
Link Master/Child Fields properties. I get the message stating that I cannot
set these properties with an unbound form.
Is there any way to do this same thing in VB?

What I need to do is have my application realize that the idrecon has
changed in my child form, then take this "new" idrecon and refeed/rerun the
VB code query on my grandchild form.
Sorry for being so long winded, I'm getting desperate to find a solution.

If that line of code is in the main form's Current event,
then it should be:

Me.LocalesDataEntry.Form.ReconnaissanceDataEntrySF.FORM.Requery

where LocalesDataEntry and ReconnaissanceDataEntrySF are the
names of the subform ***controls***, which may be different
from the name of the form object the controls are
displaying.

All this confusion can be avoided by using the subform
control's Link Master/Child Fields properties so Access
takes care of synchronizing the data automatically.


Dom Torrez wrote:
here's what i'm using:

***********************************************
Private Sub Form_Current()
Me.LocalesDataEntry.Form.ReconnaissanceDataEntrySF.Requery
End Sub
***********************************************
I get the following VB Alert:
'Compile Error: Method or Datamember not found'

The 'LocalesDataEntry' section of the code is highlighted as well.
Now I triple checked my form names and they are correct. Am I possibly
using incorrect syntax for that line of code?


:
Have you tried doing a requery on the 3rd sub? In your VB coding on the "on
current" event for the main form and sub form try something like this....

me.subformname.form.secondsubformname.requery

It may just need to requery the query that the second subform uses as it's
recordset.

:
I have a three forms 1 (main), 2 (sub), 3(subsub). My third level sub form
gets its data from a query which gets an id passed in by the 2nd level sub
form. I'm having problems with getting this query to refresh or reload when
the id has changed. My query for the 3rd level subform seems to run only
once with id = 123, but when I click any of the navigator buttons to switch
between records and the id changes, id = 456, my query keeps the record set
from the original id = 123. Is there a way for me to rerun my query every
time the id changes? I have my VB code including the query for the third
level sub form within the 'On-Open' procedure. An example is I open my
main form (1) with id 123, forms 2 and 3 are loaded as well with id = 123.
All data shows up fine in all forms. But when I switch forms 1 and 2 to now
be focused on id=456 , my 3rd subsub form doesn't re-run the VB and query
with this new id=456, it remains locked to id=123. Does anyone know how I
can get my VB in the 3rd form to re-run every time the id focus changes?
Thanks for the help,
Dom Torrez