Best Practice - Bound Forms

[BiigJiim]

Hi,

I often have bound forms or subforms where I need in vba to update the value of one of the fields in the form recordset, although that field does not need to be displayed on the form.

I have always just added a bound control to the form, set it invisible, and then updated the value of the control in the usual way eg. me.ctlFieldX=123. However I have always thought it seems a bit inefficient having an invisible bound control on a form just to do this. Is there a way to update a field in a form recordset without having a bound control? And what is considered best practice?

Thanks!
Jim

 

[CJ_London]

Assuming the field is brought through in the form recordset then just

me.fieldname=123

 

[BiigJiim]

Thanks CJ. Feel a bit silly now. I know this didn't work using the bang! operator, but for some reason thought it didn't using .dot either. Must be the heat....!

 

[CJ_London]

it should have worked with a bang operator. My understanding is that in reference to access forms there is no real difference except if you have a typo

me!fieldnamespeltwrong

will give you a runtime error

me.fieldnamespeltwrong

will fail on compile

and you actually don't need the me predicate

fieldname=123

will work just as well, providing you don't have a control with the same name as the field (in which case it is the control that get updated, not the field, which is not a problem if the controlsource is the field in question)

 

[MarkK]

My understanding is that the bang operator can only ever reference a named member of a collection, so given that the default property of a form is the controls collection, therefore the code...

me!controlname

...references a control, where the full reference would have been...

me.controls!controlname

In the following code, the bang references a named member of the recordset's fields collection....

   with currentdb.openrecordset(sql)
      do while not .eof
         debug.print !fieldname  [COLOR="Green"]'a named member of Recordset.Fields[/COLOR]
         .movenext
      loop
      .close
   end with

...where, again in this case, Fields is the default property of the Recordset.
Another common case where we reference a collection using the bang is the public Forms collection...

dim frm as form
set frm = Forms!SomeFormName [COLOR="Green"]'named member of the Forms collection[/COLOR]

 

[CJ_London]

dot v bang does have a huge debate.

I tend to use this link to clarify my understanding when I'm getting confused

http://bytecomb.com/the-bang-exclamation-operator-in-vba/

 

[Galaxiom]

and you actually don't need the me predicate

fieldname=123

Yes that does work but it is a bad practice to omit it.

On encountering an unqualified name, it is checked for among the variables, then in the controls collection then in the recordset fields collection.

BTW If you have a control by the same name as a field then you can differentiate with:

Me.Controls!controlname
Me.Recordset!fieldname

Me.somename will default to a control.