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

Yes and No.
Yes, changing txtMinimumAge in form_load (or any of the opening sequence
events) is needed.
Yes - A requery is *crucial* (as Gustav and I pointed out).

Gustav, my recordsource is set fine at design time; it needs to be that way
or I won't see qualifying records at form load.

            ?screen.ActiveForm.RecordSource
             SELECT * FROM MyTable WHERE MyAge >=
NZ([forms]![Form1]!txtMinimumAge,0);

Thanks Gentlemen


'My form's code module
Option Compare Database
Option Explicit

Private Sub Form_Load()
     txtMinimumAge = 18
     Me.Requery 
End Sub

Private Sub txtMinimumAge_AfterUpdate()
     Me.Requery
End Sub

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Saturday, October 25, 2014 12:18 PM
To: Access Developers discussion and problem solving
Subject: Re: [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