field references in forms

[Ron]

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

 

[Dirk Goldgar]

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.

 

[John Spencer]

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

 

[John Spencer]

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]

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

 

[Ron]

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.

Thank you! It was a subform (in a main form) from which I was testing, and
I had the syntax wrong. (Aggravated by fact that brackets suggested by the
expression builder cause an error in the debug immediate window.)

What I really want is to use a field in a stored query (not any form's
record source) for an expression in the criteria clause of DLookup. The
syntaxes: myquery.myfield or [myquery.myfield] give errors in the immediate
window. I have stored queries in the navigation pane, yet the object
browser doesn't show the queries. There's obviously some fundamental stuff
I've not absorbed, but at moment, would appreciate advice (a link?) on how
to express a query field.

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

I was referring to evaluation of expressions at the form design stage,
particularly ones used in control sources. At the moment, I copy/paste to
the debug immediate window. If an expression evaluates, then I know the
syntax is correct (and occasionally get an insight into my logic ) . If
there's an error, I'm not sure whether it's a syntax or context issue. Was
just wondering if there's a faster way to do this sort of thing. I think
there's a debug feature where expression values pop up when the cursor is
placed on the expression. But that's for executing VBA code that's been
halted, no? I was hoping there was something similar for design windows.

I appreciate any help. -Ron