Requerying SubForm

  • Thread starter Thread starter Simon Harris
  • Start date Start date
S

Simon Harris

Hi All,

I have two subforms on a form, the source object for each is two seperate
forms.

The forms display data in datasheet view.

The data displayed in subform B is related to data in subform A (For each
record in A there are potentially many records in B)

Data in form B is driven from a query, which as the following criteria under
the relevent column:
[Forms]![frm_add_amend_order]![SUBFRM_Supplier_Orders]![ID]
I know this is getting a value, because I get a value back when I MsgBox
this (Using the same onclick event)

I want my user to be able to select a record in subform A by clicking on the
record selector on the left, which will then display all related records in
subform B.

I have set an onclick event on the form of subform A, with the following
code:
Me.Parent![SUBFRM_Parts_Orders_Items].Requery

This doesn't seem to do much.

Anyone got any suggestions?

Many thanks for reading!

Simon.


--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
 
Ok - Think I 'solved' this one myself, or discovered a bug???

If the column storing the ID of the linked record in subform B is hidden,
the requery doesnt happen. If it is shown, the requery happens AOK.

Odd!?? Can anyone shed any further light on this?

Cheers!

Simon.
 
Requery is not dependent upon the columns in the combo box.

What I believe you're discovering here is that the bound column is the value
of the combo box; you can use other columns in the combo box's row source as
well. The bound column does not have to be visible in the dropdown list.

I suggest that you use the AfterUpdate event of the combo box for a requery.

Also, what is the source object's recordsource in the second subform? Is
this second subform linked via Master and Child fields, or is it using a
query that is reading the value from the combo box? You are not providing
enough info in your psot.

If this isn't successful, post the row source of the combo box, and the
linking fields between the subform etc.
--
Ken Snell
<MS ACCESS MVP>

Simon Harris said:
Ok - Think I 'solved' this one myself, or discovered a bug???

If the column storing the ID of the linked record in subform B is hidden,
the requery doesnt happen. If it is shown, the requery happens AOK.

Odd!?? Can anyone shed any further light on this?

Cheers!

Simon.


Simon Harris said:
Hi All,

I have two subforms on a form, the source object for each is two seperate
forms.

The forms display data in datasheet view.

The data displayed in subform B is related to data in subform A (For each
record in A there are potentially many records in B)

Data in form B is driven from a query, which as the following criteria under
the relevent column:
[Forms]![frm_add_amend_order]![SUBFRM_Supplier_Orders]![ID]
I know this is getting a value, because I get a value back when I MsgBox
this (Using the same onclick event)

I want my user to be able to select a record in subform A by clicking on the
record selector on the left, which will then display all related records in
subform B.

I have set an onclick event on the form of subform A, with the following
code:
Me.Parent![SUBFRM_Parts_Orders_Items].Requery

This doesn't seem to do much.

Anyone got any suggestions?

Many thanks for reading!

Simon.


--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
 
Hi Ken,

Many thanks for your reply.

I am using two subforms which are in datasheet view. There are no
parent/child links - Instead I have used criteria in the queries that drive
the forms within the subforms.

As mentioned, if the ID column (Which is the bound field) has to be present
(e.g Not hidden) for the requery event to fire. If I hide the ID column
(Right click on the header of the first column in the datasheet > hide
columns) then the requery even simply does not work.

I am happy to leave the ID column visible, but I'm still believe this must
be some sort of bug...unless I'm missing something???

Thanks again,

Simon.

Ken Snell said:
Requery is not dependent upon the columns in the combo box.

What I believe you're discovering here is that the bound column is the value
of the combo box; you can use other columns in the combo box's row source as
well. The bound column does not have to be visible in the dropdown list.

I suggest that you use the AfterUpdate event of the combo box for a requery.

Also, what is the source object's recordsource in the second subform? Is
this second subform linked via Master and Child fields, or is it using a
query that is reading the value from the combo box? You are not providing
enough info in your psot.

If this isn't successful, post the row source of the combo box, and the
linking fields between the subform etc.
--
Ken Snell
<MS ACCESS MVP>

Simon Harris said:
Ok - Think I 'solved' this one myself, or discovered a bug???

If the column storing the ID of the linked record in subform B is hidden,
the requery doesnt happen. If it is shown, the requery happens AOK.

Odd!?? Can anyone shed any further light on this?

Cheers!

Simon.


Simon Harris said:
Hi All,

I have two subforms on a form, the source object for each is two seperate
forms.

The forms display data in datasheet view.

The data displayed in subform B is related to data in subform A (For each
record in A there are potentially many records in B)

Data in form B is driven from a query, which as the following criteria under
the relevent column:
[Forms]![frm_add_amend_order]![SUBFRM_Supplier_Orders]![ID]
I know this is getting a value, because I get a value back when I MsgBox
this (Using the same onclick event)

I want my user to be able to select a record in subform A by clicking
Click to expand...
on
the
record selector on the left, which will then display all related
Click to expand...
records
in
subform B.

I have set an onclick event on the form of subform A, with the following
code:
Me.Parent![SUBFRM_Parts_Orders_Items].Requery

This doesn't seem to do much.

Anyone got any suggestions?

Many thanks for reading!

Simon.


--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
Click to expand...
 
You're saying that the ID field must be visible in the datasheet view of the
subform for the subform to be requeried? I don't see this behavior when I
test it using ACCESS 2002 database.

I think there is more going on here than what I am understanding. Can you
provide more details about the recordsource query (SQL statement) for the
subform? The fields that are on the subform? How do you call the requery
action? And anything more about your setup that you can provide.
--
Ken Snell
<MS ACCESS MVP>



Simon Harris said:
Hi Ken,

Many thanks for your reply.

I am using two subforms which are in datasheet view. There are no
parent/child links - Instead I have used criteria in the queries that drive
the forms within the subforms.

As mentioned, if the ID column (Which is the bound field) has to be present
(e.g Not hidden) for the requery event to fire. If I hide the ID column
(Right click on the header of the first column in the datasheet > hide
columns) then the requery even simply does not work.

I am happy to leave the ID column visible, but I'm still believe this must
be some sort of bug...unless I'm missing something???

Thanks again,

Simon.

Ken Snell said:
Requery is not dependent upon the columns in the combo box.

What I believe you're discovering here is that the bound column is the value
of the combo box; you can use other columns in the combo box's row
Click to expand...
source
as
well. The bound column does not have to be visible in the dropdown list.

I suggest that you use the AfterUpdate event of the combo box for a requery.

Also, what is the source object's recordsource in the second subform? Is
this second subform linked via Master and Child fields, or is it using a
query that is reading the value from the combo box? You are not providing
enough info in your psot.

If this isn't successful, post the row source of the combo box, and the
linking fields between the subform etc.
--
Ken Snell
<MS ACCESS MVP>

Simon Harris said:
Ok - Think I 'solved' this one myself, or discovered a bug???

If the column storing the ID of the linked record in subform B is hidden,
the requery doesnt happen. If it is shown, the requery happens AOK.

Odd!?? Can anyone shed any further light on this?

Cheers!

Simon.


Hi All,

I have two subforms on a form, the source object for each is two seperate
forms.

The forms display data in datasheet view.

The data displayed in subform B is related to data in subform A (For each
record in A there are potentially many records in B)

Data in form B is driven from a query, which as the following criteria
under
the relevent column:
[Forms]![frm_add_amend_order]![SUBFRM_Supplier_Orders]![ID]
I know this is getting a value, because I get a value back when I MsgBox
this (Using the same onclick event)

I want my user to be able to select a record in subform A by
Click to expand...
Click to expand...
clicking
on
the
record selector on the left, which will then display all related records
in
subform B.

I have set an onclick event on the form of subform A, with the following
code:
Me.Parent![SUBFRM_Parts_Orders_Items].Requery

This doesn't seem to do much.

Anyone got any suggestions?

Many thanks for reading!

Simon.


--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to
Click to expand...
another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
Click to expand...
 
Hi Again Ken,

Just to confirm...

___________________________
|Subform A, displayed as datasheet|
|__________________________|

___________________________
|Subform B, displayed as datasheet|
|__________________________|

OnClick event of subform A requeries Subform B (The onclick event of the
actual form itself)

Subform B is driven from a query, the SQL of which is:

SELECT QRY_view_order_items.id, QRY_view_order_items.Detail,
QRY_view_order_items.[Qty Ord'], QRY_view_order_items.[Unit Cost],
QRY_view_order_items.sell_price, QRY_view_order_items.[Advice Note],
QRY_view_order_items.supplier_order_internal_ref
FROM QRY_view_order_items
WHERE
(((QRY_view_order_items.supplier_order_internal_ref)=[Forms]![frm_add_amend_
order]![SUBFRM_Supplier_Orders]![ID]));

Fields on subform B are:

- ID
- Detail
- Qty Ord'
- Unit Cost
- Advice Note
- supplier_order_internal_ref

The requery event works only when:

- The ID field of subform B is visible in the datasheet
and
- The ID field is the first column in the datasheet

Dont worry too much, because as I say, I'm happy to leave te ID field where
it is - It works like this, after all...But, I am intrigued to find out
whats going on here (As I suspect you are too!)

Thanks again Ken,

Simon.

Ken Snell said:
You're saying that the ID field must be visible in the datasheet view of the
subform for the subform to be requeried? I don't see this behavior when I
test it using ACCESS 2002 database.

I think there is more going on here than what I am understanding. Can you
provide more details about the recordsource query (SQL statement) for the
subform? The fields that are on the subform? How do you call the requery
action? And anything more about your setup that you can provide.
--
Ken Snell
<MS ACCESS MVP>



Simon Harris said:
Hi Ken,

Many thanks for your reply.

I am using two subforms which are in datasheet view. There are no
parent/child links - Instead I have used criteria in the queries that drive
the forms within the subforms.

As mentioned, if the ID column (Which is the bound field) has to be present
(e.g Not hidden) for the requery event to fire. If I hide the ID column
(Right click on the header of the first column in the datasheet > hide
columns) then the requery even simply does not work.

I am happy to leave the ID column visible, but I'm still believe this must
be some sort of bug...unless I'm missing something???

Thanks again,

Simon.

Ken Snell said:
Requery is not dependent upon the columns in the combo box.

What I believe you're discovering here is that the bound column is the value
of the combo box; you can use other columns in the combo box's row
Click to expand...
source
as
well. The bound column does not have to be visible in the dropdown list.

I suggest that you use the AfterUpdate event of the combo box for a requery.

Also, what is the source object's recordsource in the second subform? Is
this second subform linked via Master and Child fields, or is it using a
query that is reading the value from the combo box? You are not providing
enough info in your psot.

If this isn't successful, post the row source of the combo box, and the
linking fields between the subform etc.
--
Ken Snell
<MS ACCESS MVP>

Ok - Think I 'solved' this one myself, or discovered a bug???

If the column storing the ID of the linked record in subform B is hidden,
the requery doesnt happen. If it is shown, the requery happens AOK.

Odd!?? Can anyone shed any further light on this?

Cheers!

Simon.


Hi All,

I have two subforms on a form, the source object for each is two
seperate
forms.

The forms display data in datasheet view.

The data displayed in subform B is related to data in subform A (For
each
record in A there are potentially many records in B)

Data in form B is driven from a query, which as the following criteria
under
the relevent column:
[Forms]![frm_add_amend_order]![SUBFRM_Supplier_Orders]![ID]
I know this is getting a value, because I get a value back when I MsgBox
this (Using the same onclick event)

I want my user to be able to select a record in subform A by
Click to expand...
clicking
on
the
record selector on the left, which will then display all related records
in
subform B.

I have set an onclick event on the form of subform A, with the following
code:
Me.Parent![SUBFRM_Parts_Orders_Items].Requery

This doesn't seem to do much.

Anyone got any suggestions?

Many thanks for reading!

Simon.


--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
Click to expand...
 
I am at a loss to try to explain what you're seeing here. If you'd like, zip
up a small example of the database and email it (zipped file) to me at my
email address (remove this is not real from the email address). I'll take a
look and see what's happening. I am intrigued!


--
Ken Snell
<MS ACCESS MVP>

Simon Harris said:
Hi Again Ken,

Just to confirm...

___________________________
|Subform A, displayed as datasheet|
|__________________________|

___________________________
|Subform B, displayed as datasheet|
|__________________________|

OnClick event of subform A requeries Subform B (The onclick event of the
actual form itself)

Subform B is driven from a query, the SQL of which is:

SELECT QRY_view_order_items.id, QRY_view_order_items.Detail,
QRY_view_order_items.[Qty Ord'], QRY_view_order_items.[Unit Cost],
QRY_view_order_items.sell_price, QRY_view_order_items.[Advice Note],
QRY_view_order_items.supplier_order_internal_ref
FROM QRY_view_order_items
WHERE
(((QRY_view_order_items.supplier_order_internal_ref)=[Forms]![frm_add_amend_
order]![SUBFRM_Supplier_Orders]![ID]));

Fields on subform B are:

- ID
- Detail
- Qty Ord'
- Unit Cost
- Advice Note
- supplier_order_internal_ref

The requery event works only when:

- The ID field of subform B is visible in the datasheet
and
- The ID field is the first column in the datasheet

Dont worry too much, because as I say, I'm happy to leave te ID field where
it is - It works like this, after all...But, I am intrigued to find out
whats going on here (As I suspect you are too!)

Thanks again Ken,

Simon.

Ken Snell said:
You're saying that the ID field must be visible in the datasheet view of the
subform for the subform to be requeried? I don't see this behavior when I
test it using ACCESS 2002 database.

I think there is more going on here than what I am understanding. Can you
provide more details about the recordsource query (SQL statement) for the
subform? The fields that are on the subform? How do you call the requery
action? And anything more about your setup that you can provide.
Click to expand...
subform?
Is
Click to expand...
using
a
query that is reading the value from the combo box? You are not providing
enough info in your psot.

If this isn't successful, post the row source of the combo box, and the
linking fields between the subform etc.
--
Ken Snell
<MS ACCESS MVP>

Ok - Think I 'solved' this one myself, or discovered a bug???

If the column storing the ID of the linked record in subform B is
hidden,
the requery doesnt happen. If it is shown, the requery happens AOK.

Odd!?? Can anyone shed any further light on this?

Cheers!

Simon.


Hi All,

I have two subforms on a form, the source object for each is two
seperate
forms.

The forms display data in datasheet view.

The data displayed in subform B is related to data in subform A (For
each
record in A there are potentially many records in B)

Data in form B is driven from a query, which as the following criteria
under
the relevent column:
[Forms]![frm_add_amend_order]![SUBFRM_Supplier_Orders]![ID]
I know this is getting a value, because I get a value back when I
MsgBox
this (Using the same onclick event)

I want my user to be able to select a record in subform A by clicking
on
the
record selector on the left, which will then display all related
records
in
subform B.

I have set an onclick event on the form of subform A, with the
following
code:
Me.Parent![SUBFRM_Parts_Orders_Items].Requery

This doesn't seem to do much.

Anyone got any suggestions?

Many thanks for reading!

Simon.


--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top