using Parent or Me

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ngan - 05 May 2005 17:14 GMT

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.

Reply to this Message

jl5000 - 05 May 2005 18:30 GMT

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

Signature

jl5000
<a href="http://joshdev.com"></a>

> 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
[quoted text clipped - 5 lines]
>
> I have the same problem if I want to use the where condition in a subquery.

Reply to this Message

ngan - 05 May 2005 18:51 GMT

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

> 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
[quoted text clipped - 12 lines]
> >
> > I have the same problem if I want to use the where condition in a subquery.

Reply to this Message

Ofer - 05 May 2005 19:38 GMT

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.
>
[quoted text clipped - 32 lines]
> > >
> > > I have the same problem if I want to use the where condition in a subquery.

Reply to this Message

ngan - 05 May 2005 19:58 GMT

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
>
[quoted text clipped - 45 lines]
> > > >
> > > > I have the same problem if I want to use the where condition in a subquery.

Reply to this Message

John Vinson - 06 May 2005 06:20 GMT

>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]

Reply to this Message

ngan - 06 May 2005 16:17 GMT

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.

> >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
[quoted text clipped - 6 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 07 May 2005 00:37 GMT

>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]

Reply to this Message

david epsom dot com dot au - 06 May 2005 07:09 GMT

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)

>I have a XP mdb that has link tables to SQL 2k tables.
>
[quoted text clipped - 43 lines]
>> > I have the same problem if I want to use the where condition in a
>> > subquery.

Reply to this Message