The crucial piece of the Bang verses Dot argument is that you must use a
Bang (!) when dealing with fields of a recordset. This works:
rs!City = "New York"
This does not:
rs.City = "New York" '<-Error!!!
When referring to controls on a form, the Bang and Dot both work, so take
your pick. My preference is to use the dot since:
- Access autocompletes the name for you, making it faster and more accurate
to type.
- If you still do make a mistake and you've used the dot, the compiler
catches it. If you used the bang, you are not notified about the problem
until runtime, so the error can lie dormant and bite you later.
Anything that helps you catch errors at design time is gold.
If you are interested in a technical description of the difference between
bang and dot, Andy Baron has an article here:
http://doc.advisor.com/doc/05352
There are 2 other cases worth commenting on:
a) If you have a field that is in the Recordset of a *form*, but there is no
control by that name on the form, it will work with the bang, and it only
sometimes works with the dot. This data type (known as an AccessField) is
handled inconsistently. If you programmatically reassign the form's
RecordSource after opening the form so there is now an extra field, you can
only refer to this field with the bang. If the field was present at design
time, it usually works either way but can fail if you use the dot (e.g. when
converting to another version of Access, the code may not compile.) My
personal preference is to work around this issue by adding a hidden control
to avoid the AccessField problems.
b) In a *report*, even if a field is in the RecordSource, Access may not
fetch it. Refering to the field in any context may result in an error - not
just in VBA, but even in the Control Source of a text box. AFAICT, this is
caused by the way Access optimizes the report's RecordSource, for the
sorting and grouping. Again, the workaround is to add a hidden text box for
the field. Access then realizes it must fetch this field, so you avoid the
error.
HTH.
