! Using WITH !

[Wembly]

Hi,

I am having a difficulty with the following code:
....
Dim currentForm as Form
Set currentForm = Screen.ActiveForm

With currentForm

.varName = False
!ctrlName.Enabled = True

End With
....

The line with the bang does not run. The error msg is
something along the lines that the control cannot be found.

Why is it that it works with the dot and not the bang?

Thanks for your help.

 

[Paul Overway]

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com

 

[Paul Overway]

bang is not used with With...see help and use dot.

 

[Dirk Goldgar]

bang is not used with With...see help and use dot.

I have to disagree. You can use bang with With.

 

[Paul Overway]

If you do...it defeats the entire purpose of the construct. Pretty dumb
really.

 

[Tim Ferguson]

With currentForm

.varName = False
!ctrlName.Enabled = True

End With
...

The line with the bang does not run. The error msg is
something along the lines that the control cannot be found.

The bang operator refers to a member of the default collection, and in the
case of a Form object this is the Controls collection, so the code you have
offered should be right. There are some possible confounders, however.

The first thing to check is that ctrlName actually exists on the form --
and this should be a control actually called "ctrlName". If ctrlName is a
string containing the name ("txtMyTextBox") then you need the fuller syntax

With frmCurrentForm
.Controls(strCtrlName).Enabled = True

End With

The next is when there is a collision between the properties of a form, the
fields in the recordset the form is based on, and the controls. Access
exposes all the fields in the recordset as form properties, so that

frmCurrentForm.AnnualCost

returns the value from the database. Contrast this with

frmCurrentForm!AnnualCost

which reads the value in the control. Although these are the same
practically all the time, there are situations where you need to be able to
refer definitely to one or the other. Finally, if you have a field called
Caption, then the following can cause great confusion:

frmCurrentForm.Caption

All this is a good reason for using some form of data-type labelling of
your variables. It does force you continually to bear in mind whether you
are referring to an object, or a string containing a name, or a property or
a handle or whatever. The prefix method I used here is common, but you can
use something else as long as it makes sense to you and your successors.

Hope that helps

Tim F

 

[Dirk Goldgar]

If you do...it defeats the entire purpose of the construct. Pretty
dumb really.

Could you explain to me how this is so? I must be missing something.
I've written a fair amount of code like this, for example:

Dim rs As Recordset
Dim varX As Variant

Set rs = CurrentDb.OpenRecordset( ... )
With rs
If .RecordCount <> 0 Then
varX = !SomeField
End If
.Close
End With
Set rs = Nothing

If there's something there that defeats the whole purpose of the
construct, I'd like to know it.

 

[Paul Overway]

I'd have to do some more research on this to be sure, but by using bang, it
seems to me that you only referencing the property or method
indirectly...you are adding another level of interpretation. I suspect this
is slower. Plus you lose intellisense. I rarely use bang for
anything....but I came from VB.

 

[Dirk Goldgar]

I'd have to do some more research on this to be sure, but by using
bang, it seems to me that you only referencing the property or method
indirectly...you are adding another level of interpretation. I
suspect this is slower. Plus you lose intellisense. I rarely use
bang for anything....but I came from VB.

The bang isn't a reference to a property or method, it's a reference to
a member of the object's default collection; in this case, the form
object's Controls collection. I'm not convinced that it's adding any
extra level of interpretation in the case of form objects, though I
suppose its possible. Even so, the dot notation only works with
controls on Access forms and reports because Access goes out of its way
to make the controls available as properties of the object. You can't,
for example, use the dot notation for the fields of a recordset object.
That doesn't mean referring to those fields via the bang notation inside
a With construct is a waste of time, or counterproductive.

My personal practice is to use the dot notation wherever it's available
and not misleading, so as to get the intellisense. However, I don't
think using the bang gives a performance hit, and there are many objects
that don't support using the dot notation for members of their default
collections. At any rate, the perennial "bang vs. dot" question seems
to me to be a completely separate issue from the proper use of the With
construct.

 

[Paul Overway]

You CAN use dot to reference fields in a recordset

With rst
.Edit
.fields("whatever") = x
.fields("whatever2") = y
.Update .
' etc.
End With

A little more typing...more explicit...but no bang....plus intellisense.
)

My biggest issue with using bang....the compiler is ignorant as to whether
the thing you referenced actually exists whereas if you use dot, you'll get
a compile error if it doesn't.

 

[Dirk Goldgar]

You CAN use dot to reference fields in a recordset

With rst
.Edit
.fields("whatever") = x
.fields("whatever2") = y
.Update .
' etc.
End With

A little more typing...more explicit...but no bang....plus
intellisense.
)

My biggest issue with using bang....the compiler is ignorant as to
whether the thing you referenced actually exists whereas if you use
dot, you'll get a compile error if it doesn't.

Okay, now we're getting into some serious quibbling. I'd say that
you *aren't* using the dot to reference the fields themselves, but
rather to reference the Fields *property* of the recordset, which
returns a reference to the Fields collection, which you are then
indexing with the field name to get at the field itself. But you don't
get any more intellisense about field "whatever" itself, not even
whether it exists or not, than you get by just using the bang. A
reference of the form

rst.Fields("whatever")

gives you no more information about whether field "whatever" exists than
the shorthand

rst!whatever

does -- the compiler is equally ignorant.

FWIW, it's my understanding that the bang notation

Object!Foo

is internally translated into

Object.{default collection}("Foo")

so feel free to argue that you're just making an implicit reference
explicit, which many see as a good thing. Personally, I see no
particular benefit to it in this case, but to each his own.

 

[Paul Overway]

Fields is probably a bad example in this case, controls on a form or report
would highlight the problem better. Guess I'm anal . )