Subform linked to Main form via user login

[DontKnow]

Hi Guys,

I have a form that has the userID login displayed via a textbox on the main
form. On that form I have a subform that is bound via a query that uses the
login id via the textbox on the main form to filter all the relavent userID
records. The filter is in the form of "[Forms]![Main Risk Register
Form]![Text7]".

Unfortunately for me the form when opened does not show any records. Yet
when I open the query with the main form open the query shows all the
appropriate records. If I open the subform with the main form already open I
also receive the relavent records.

Why is it when I open the form that contains the subform can't I get the
appropriate records displayed??

Please help me, it is quite frustrating!!

Cheers,

 

[Marshall Barton]

I have a form that has the userID login displayed via a textbox on the main
form. On that form I have a subform that is bound via a query that uses the
login id via the textbox on the main form to filter all the relavent userID
records. The filter is in the form of "[Forms]![Main Risk Register
Form]![Text7]".

Unfortunately for me the form when opened does not show any records. Yet
when I open the query with the main form open the query shows all the
appropriate records. If I open the subform with the main form already open I
also receive the relavent records.

Why is it when I open the form that contains the subform can't I get the
appropriate records displayed??

If you are using that kind of criteria in the subform's
record source query, then you need to requery the subform
after setting the text box to the userid:
Me.subformcontrol.Form.Requery

That is not the "best" way to do this kind of thing. It is
"better" to use the subform control's Link Master/Child
properties to sync the subform and main form and remove the
criteria from the query. This will automatically take care
of filtering the subform records so you can also remove the
Requery needed by your query criteria approach.

The Link Master Fields property would be set to Text7 (poor
choice of a name) and the Link Child Fields property would
be set to the userid field. No criteria or code needed.

 

[DontKnow]

Thats excellent Marshall!!

I shall incorporate your suggestion straight away!!

I must admit that I did not know about the "requery" requirement ...

cheers,

I have a form that has the userID login displayed via a textbox on the main
form. On that form I have a subform that is bound via a query that uses the
login id via the textbox on the main form to filter all the relavent userID
records. The filter is in the form of "[Forms]![Main Risk Register
Form]![Text7]".

Unfortunately for me the form when opened does not show any records. Yet
when I open the query with the main form open the query shows all the
appropriate records. If I open the subform with the main form already open I
also receive the relavent records.

Why is it when I open the form that contains the subform can't I get the
appropriate records displayed??

If you are using that kind of criteria in the subform's
record source query, then you need to requery the subform
after setting the text box to the userid:
Me.subformcontrol.Form.Requery

That is not the "best" way to do this kind of thing. It is
"better" to use the subform control's Link Master/Child
properties to sync the subform and main form and remove the
criteria from the query. This will automatically take care
of filtering the subform records so you can also remove the
Requery needed by your query criteria approach.

The Link Master Fields property would be set to Text7 (poor
choice of a name) and the Link Child Fields property would
be set to the userid field. No criteria or code needed.