Access2003 Form/Query

[wquatan]

Hi,

I have a form, bound to a query.
None of the queryfields are on the form as the form is only there for navigation.

In an form.event (OnCurrent) I want to use (in VBA) the queryfields, but ..... how do I reference to the QueryFields ?

Thx

[Rabbit]

You reference a field by typing the field name.

[MMcCarthy]

Hi,

I have a form, bound to a query.
None of the queryfields are on the form as the form is only there for navigation.

In an form.event (OnCurrent) I want to use (in VBA) the queryfields, but ..... how do I reference to the QueryFields ?

Thx

Put the query fields on the form but set them to invisible if you don't want the user to see them.

[wquatan]

Put the query fields on the form but set them to invisible if you don't want the user to see them.

That's what I wanted to avoid.
I thought I would have been possible to use sort of Me!captionname.fieldname referencing instead of Me!fieldname

[MMcCarthy]

That's what I wanted to avoid.
I thought I would have been possible to use sort of Me!captionname.fieldname referencing instead of Me!fieldname

If you want to refer to values in fields in the query bound to the form then those fields have to be in controls on the form.

[Rabbit]

If you want to refer to values in fields in the query bound to the form then those fields have to be in controls on the form.

That can't be right, I know I've been able to reference fields that have no corresponding control on my form but are in my control source.

[wquatan]

That can't be right, I know I've been able to reference fields that have no corresponding control on my form but are in my control source.

That's exactly what I want to do.
Can you remember how-to ?
Would be great to have a syntax-sample :-)

Thx

[NeoPa]

That can't be right, I know I've been able to reference fields that have no corresponding control on my form but are in my control source.

That would certainly be interesting Rabbit. I too am of the opinion that you reference an item by Me.ControlName rather than Me.FieldName.
It wouldn't be the first time you've confounded us with some extra clever stuff, but I wait to be convinced on this one.

[missinglinq]

And the winner is......Br'er Rabbit! Yes, you certainly can reference a field in the underlying table/query that isn't bound to a control on the form! The syntax is simply

Me.FieldName!

Welcome to TheScripts, wquatan!

Linq ;0)>

[MMcCarthy]

And the winner is......Br'er Rabbit! Yes, you certainly can reference a field in the underlying table/query that isn't bound to a control on the form! The syntax is simply

Me.FieldName!

Welcome to TheScripts, wquatan!

Linq ;0)>

Nice one Rabbit and Linq.

I have honestly never tried to do that so the issue never came up. That's what fascinates me about Access. How much we don't know :D

[FishVal]

Its quite an interesting thing.

Seems that Item property of Form.Controls collection is quite intelligent to return recordset field when no control has the name passed as argument.

Example.
[SomeForm] has a RecordSource but no control.

? Forms!SomeForm.Controls.Count
0

? Forms!SomeForm.Controls!SomeField
<FieldValue>

? TypeName(Forms!SomeForm.Controls!SomeField)
AccessField
that is hidden class in Access library

And even more interesting.
Access creates property SomeField in Form_SomeForm class quite intelligently.
If [SomeForm] has a control bound to [SomeField], then the property is declared to return the control object. Otherwise it is declared to return AccessField object. Anyone can easily check this in Object Browser.

Summary.

• if a form bound to query/table doesn't have a control with the name same as of recordset field, then references like
  Me.SomeField
  and
  Me!SomeField
  will return AccessField object
• if a form bound to query/table has a control with the name same as of recordset field, then references like
  Me.SomeField
  and
  Me!SomeField
  will return the control object

[MMcCarthy]

It's interesting stuff. Typical of Access to do something really clever and forget to tell you about it. :)

[MMcCarthy]

It's interesting stuff. Typical of Access to do something really clever and forget to tell you about it. :)

By the way, if anyone would like to do an article on this that would be even better.

[NeoPa]

Sounds like I was simply wrong on this one then :(
Never mind. It's something I should have a play with and know for the future. Thanks for the responses people :)