Prev: Storig data from website to database
Next: Checkbox that pulls related info from form to report
From: Ron on 10 Feb 2010 23:13
If I have a form "myform" with recordsource "mytable" and the table contains
a field called "id," I can reference the current value of that field in
expressions used for controls in the form by myform.id. This is true even
when there is no control bound to mytable.ID on the form. I can "print" the
value of myform.id in the debug immediate window.

If I try the same thing for a form whose record source is a query, ?
myform.id produces an error.

Why the difference?

And a corollary question: what's the most efficient way to test the values
of expressions, for example the condition clause (the third clause) of
DLookup, after constructing it in the expression builder?

Thanks for any help, Ron

From: Dirk Goldgar on 11 Feb 2010 00:31
"Ron" <Harvested(a)comcast.net> wrote in message
news:hl005r$t6$1(a)news.eternal-september.org...
> If I have a form "myform" with recordsource "mytable" and the table
> contains a field called "id," I can reference the current value of that
> field in expressions used for controls in the form by myform.id. This is
> true even when there is no control bound to mytable.ID on the form. I can
> "print" the value of myform.id in the debug immediate window.
>
> If I try the same thing for a form whose record source is a query, ?
> myform.id produces an error.
>
> Why the difference?

You are mistaken. So long as the form's recordsource query includes the
field in the list of fields that it selects, the field is available as a
property of the form even if there is no control bound to that form. If the
field is present in the form's recordset (obtained by querying the
recordsource), the field can be accessed via the form. If the field is not
present in the form's recordset, it can't be access via the form.

So if your form has recordsource "SELECT ID, OtherField FROM MyTable", ID
can be retrieved via Forms!MyForm.ID . But if your form has recordsource
"SELECT OtherField FROM MyTable", then the reference will fail.

> And a corollary question: what's the most efficient way to test the values
> of expressions, for example the condition clause (the third clause) of
> DLookup, after constructing it in the expression builder?

Sorry, I don't understand the question you're asking well enough to answer
it.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: John Spencer on 11 Feb 2010 08:19
Thanks Dirk. I just learned something new (and I tested it). I always
thought you could only reference controls on a form in a query.

Something new to add to my knowledge.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
From: John Spencer on 11 Feb 2010 08:24
Do you want to test if the syntax of the condition clause is correct, if the
logic is correct, or if Dlookup returns a value?

The only way that I know to test the syntax is to try it - that may generate
an error.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ron wrote:
> If I have a form "myform" with recordsource "mytable" and the table
> contains a field called "id," I can reference the current value of that
> field in expressions used for controls in the form by myform.id. This
> is true even when there is no control bound to mytable.ID on the form.
> I can "print" the value of myform.id in the debug immediate window.
>
> If I try the same thing for a form whose record source is a query, ?
> myform.id produces an error.
>
> Why the difference?
>
> And a corollary question: what's the most efficient way to test the
> values of expressions, for example the condition clause (the third
> clause) of DLookup, after constructing it in the expression builder?
>
> Thanks for any help, Ron
From: Ron on 11 Feb 2010 09:53

> Do you want to test if the syntax of the condition clause is correct, if
> the
> logic is correct, or if Dlookup returns a value?
>
> The only way that I know to test the syntax is to try it - that may
> generate an error.
>

Thanks for the response.

Actually Access does give hints about syntax errors, at least after I've
used the expression builder to build a control source property. If there's
a syntax error (because I did some typing in there), I get a message when I
click away from that property. No, I meant "test" in the sense of
"evaluating the expression." I can copy and paste the expression into the
debug immediate window, but when there's an error, I'm never quite sure if
I've done something wrong or if I'm simply out of scope.

Thanks also to Dirk! I will test again to see why I made my observation,
correct syntax, etc., and repost if the problem to which my question
pertains persists. (I am trying to build a DLookup criterion parameter.
The DLookup would serve as the control source for a textbox on a main form.
The criterion parameter must reference a query which itself is the
recordsource for a subform. I was having trouble referencing the
query.field, so I tried the subform.field and (thought I) noticed the
disparity I posted.)

-Ron

 | 
Pages: 1
Prev: Storig data from website to database
Next: Checkbox that pulls related info from form to report