[AccessD] Cannot refer to a textbox on a form in the form's recordsource on open

Hi Bill

I would follow Dan's idea and hardcode it:

    SELECT * FROM MyTable WHERE MyAge >= 18;

Then, after update of the textbox, set the recordsource to:

    "SELECT * FROM MyTable WHERE MyAge >= " & Nz(Me!txtMinimumAge, 0) & ";"

That will requery the form.

/gustav

________________________________________
Fra: accessd-bounces at databaseadvisors.com <accessd-bounces at databaseadvisors.com> på vegne af Bill Benson <bensonforums at gmail.com>
Sendt: 25. oktober 2014 18:03
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Cannot refer to a textbox on a form in the form's recordsource on open

Hey Dan, thanks for jumping in. So here's the thing, that's was what I
tried 1st.

Nothing value I give that control, in any event - including form open -
causes the control to NOT be NULL at such time as the record source is
determining how many records to show (ie, opening my form).

It's as if the recordsource is being evaluated before form_open, which is
contrary to what i thought i understood about forms. Seems I remember using
form_open to test the recordsource before committing to bound field, way
back when.

Perhaps the key is Requery?

I am stubbornly resisting making use of a requery command until I am
absolutely convinced there is no way to get the record source to take note
of changes in that controls value made at startup.
On Oct 25, 2014 11:43 AM, "Dan Waters" <df.waters at outlook.com> wrote:

> Hi Bill,
>
> Would you be able to hardcode the minimum age?
>
> On form load:
>         txtAgeMinimum = 18
>
> Dan
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
> Sent: Saturday, October 25, 2014 10:29 AM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Cannot refer to a textbox on a form in the form's
> recordsource on open
>
> Apparently when a recordsource refers to a text box on the form, and the
> form is opening, there is no opportunity to set that control's value in
> preparation for the first time the record source's query is run. If one sets
> a NZ( .,.) alternative value, then you are stuck with that no matter when
> the control is null, not just on form open.
>
>
>
> Suppose I have recordsourec that depends on records where age is greater
> than a minimum, which is supplied by the user. Normally, If the user enter's
> nothing, then I would want all records, so I would not mind altering my
> recordsource to
>
>
>
> From:
>
> RecordSource:
>
> SELECT * FROM MyTable WHERE MyAge >=Forms![Form1]![txtMinimumAge];
>
>
>
> To:
>
> RecordSource:
>
> SELECT * FROM MyTable WHERE MyAge >=NZ(Forms![Form1]![txtMinimumAge],0);
>
>
>
> However, if I want the assumed minimum age to be 18, there is no way I can
> set this value when the form is opening. It does me no good to have the
> value in the control's default, the control is still NULL at the time that
> the recordsource is evaluated the first time.