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

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

  Old trick:

 Issue a me.repaint in the form's OnOpen event.

  This occurs before the load of the forms recordset and the .repaint forces
the instantiation of all controls before the code will continue.

  So you can add a control, default it, then have your SQL point to it with
out issue.

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Saturday, October 25, 2014 11:29 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Cannot refer to a textbox on a form in the
form'srecordsource 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.  

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com