using Parent or Me

[Guest]

I have a subform (frmAddress) that is used by two main forms (frmRider and
frmFilteredRider). In the record source, I have a WHERE condition that looks
at the ID field in the main form. Is there a way to write the WHERE
condition so it doesn't care which main form is open?

I tried "WHERE ClientID = Parent!ClientID", but all it does is bring back a
blank page. If I try "Me!ClientID", it pops up asking for "Me!ClientID".

I have the same problem if I want to use the where condition in a subquery.

 

[Guest]

Are you using the master and child lnk fields in the subform?, if so, you can
get rid of the where clause in your query, the parent-child link fields will
take care of it,

Find these properties in the properties window of the subform inside each
main forms

 

[Guest]

I have a XP mdb that has link tables to SQL 2k tables.

Two questions:

1. If I don't put in a WHERE condition, the program will grab all the data
for the subform and then do a filter based on the master/child link, right?
The subtables has over 200,000 records, then I'm asking Access/SQL to pull
all 200k records and then do a filter. If I put a WHERE condition, I'm just
asked them to pull the records for that client id. Less burden on the
resources.

2. What if one subform has the recordsource based on a subquery? If I
don't put a criteria on the subquery to just look up the client id and
instead pull everything, it takes a few more seconds to open the form because
of the reason in #1. Therefore, in the subquery, I have to put a criteria
where clientID equals to the clientid in the main form.

Thanks.
Ngan

 

[Guest]

You can assign the recorsource to the sub form from your form

me.[subformname].[Form].[RecordSource]="Select * From Table Where ClientID =
" & Parent!ClientID

Now I don't knew ow your form work.
If you open once without moving between records, then put it on the on load
property.
If you move between records the put it on the on current.
If you change the Client Id with a combo, then on After event of that combo.

I hope it make sense.

 

[Guest]

Actually, I did think of that option. So on the the main form OnOpen, I set
the recordsource for the subforms, like you suggested. I just didnt know if
there was another way to do it without having to code it on the onopen. I
would have to do the same for the other main form then.

Thanks.

You can assign the recorsource to the sub form from your form

me.[subformname].[Form].[RecordSource]="Select * From Table Where ClientID =
" & Parent!ClientID

Now I don't knew ow your form work.
If you open once without moving between records, then put it on the on load
property.
If you move between records the put it on the on current.
If you change the Client Id with a combo, then on After event of that combo.

I hope it make sense.

I have a XP mdb that has link tables to SQL 2k tables.

Two questions:

1. If I don't put in a WHERE condition, the program will grab all the data
for the subform and then do a filter based on the master/child link, right?
The subtables has over 200,000 records, then I'm asking Access/SQL to pull
all 200k records and then do a filter. If I put a WHERE condition, I'm just
asked them to pull the records for that client id. Less burden on the
resources.

2. What if one subform has the recordsource based on a subquery? If I
don't put a criteria on the subquery to just look up the client id and
instead pull everything, it takes a few more seconds to open the form because
of the reason in #1. Therefore, in the subquery, I have to put a criteria
where clientID equals to the clientid in the main form.

Thanks.
Ngan

 

[John Vinson]

Actually, I did think of that option. So on the the main form OnOpen, I set
the recordsource for the subforms, like you suggested. I just didnt know if
there was another way to do it without having to code it on the onopen. I
would have to do the same for the other main form then.

If you use the main form's Current event (instead of Open) the subform
will repopulate with each ClientID as you move between records on the
mainform.

John W. Vinson[MVP]

 

[david epsom dot com dot au]

If you have a subform,the subform opens first, then
requeries after the main form is opened (and the master
field is set)

The solution is to not to put a form into a subform
control until after the form has opened. ie in the
Load event of the main form, put:

me.sbfcontrol.sourceobject= "frm_mysbf"

(david)

 

[Guest]

What about setting the recordsource (where clientid = parent!clientID) in the
OnOpen event of the subforms (similar to what the other poster suggested) ?
That means the recordsource is set only once.

 

[John Vinson]

What about setting the recordsource (where clientid = parent!clientID) in the
OnOpen event of the subforms (similar to what the other poster suggested) ?
That means the recordsource is set only once.

That's why I suggested the Current event instead! It *DOES* set the
recordsource only once; the subform's Open event fires before the
mainform's Open event. You would see the first records' client
information on the subform regardless of what the mainform contains.

John W. Vinson[MVP]