VBA: How can I get the type of the control programatically?

[WTC AS3 NCO]

Reference to Access 2007
I have a Dialog Form that users will use to select two Table names from two
combo boxes. How can I clear the values when the form loads to force the user
to select an item from the list?

In code I'm iterating through the Form's Controls Collection to set the
value but I can't seem to find how to narrow my collection to only text entry
types. With VB this was fairly simple, but VBA is not any help.

I could reference the controls directly but I have other forms with more
text type controls and I'd rather iterate with For Each.

Thanks!

 

[Stuart McCall]

Reference to Access 2007
I have a Dialog Form that users will use to select two Table names from
two
combo boxes. How can I clear the values when the form loads to force the
user
to select an item from the list?

In code I'm iterating through the Form's Controls Collection to set the
value but I can't seem to find how to narrow my collection to only text
entry
types. With VB this was fairly simple, but VBA is not any help.

I could reference the controls directly but I have other forms with more
text type controls and I'd rather iterate with For Each.

Thanks!

Dim itm As Variant

For Each itm In Forms!MyForm.Controls
Select Case itm.ControlType
Case acTextbox, acComboBox, acLabel
'do whatever you need to do
Case Else
End Select
Next

 

[Douglas J. Steele]

Dim itm As Variant

For Each itm In Forms!MyForm.Controls
Select Case itm.ControlType
Case acTextbox, acComboBox, acLabel
'do whatever you need to do
Case Else
End Select
Next

Not that it's any better, but an alternative is

Dim ctl As Control

For Each ctl In Forms!MyForm.Controls
If TypeOf ctl Is Textbox Or TypeOf ctl Is acComboBox Then
'do whatever you need to do
End If
Next


(Stuart: was there a reason why you declared itm As Variant instead of As
Control?)

 

[Stuart McCall]

Not that it's any better, but an alternative is

Dim ctl As Control

For Each ctl In Forms!MyForm.Controls
If TypeOf ctl Is Textbox Or TypeOf ctl Is acComboBox Then
'do whatever you need to do
End If
Next

Except that acCombobox is a number. Should be the object title (my
terminology) :

Or TypeOf ctl Is ComboBox Then

(Stuart: was there a reason why you declared itm As Variant instead of As
Control?)

Pure laziness. I tend to develop code using variants so I don't get
distracted re data types. Then when I know I'm keeping the code, I do a
'second pass' and type everything correctly (along with other tweaks to
clear out rubbish code, commented out stuff etc.). The exception to that
rule is when coding with the windows api, obviously.

 

[Douglas J. Steele]

Except that acCombobox is a number. Should be the object title (my
terminology) :

Or TypeOf ctl Is ComboBox Then

Oops. Right you are.

Pure laziness. I tend to develop code using variants so I don't get
distracted re data types. Then when I know I'm keeping the code, I do a
'second pass' and type everything correctly (along with other tweaks to
clear out rubbish code, commented out stuff etc.). The exception to that
rule is when coding with the windows api, obviously.

Thanks. I was just curious. Can't say I've ever done it that way (largely
because I use a strict naming convention, and it would be a PITA to have to
not only change the data type but the variable name too!)

 

[Stuart McCall]

Thanks. I was just curious. Can't say I've ever done it that way (largely
because I use a strict naming convention, and it would be a PITA to have
to not only change the data type but the variable name too!)

Oh that's nothing <g>. I also sometimes code without Option Explicit <gasp!>
and use type declaration characters. But published code is written neatly
and properly.

I find that doing the second pass is v useful for spotting badguys because
it makes me go through it carefully, instead of thinking 'ok that works' and
moving on.

I've been doing things similarly since way before vb/vba, and as Edgar
Dijkstra said: "It is practically impossible to teach good programming to
students that have had a prior exposure to BASIC: as potential programmers
they are mentally
mutilated beyond hope of regeneration." ;-)

 

[David W. Fenton]

message news:[email protected]...

Pure laziness. I tend to develop code using variants so I don't
get distracted re data types. Then when I know I'm keeping the
code, I do a 'second pass' and type everything correctly (along
with other tweaks to clear out rubbish code, commented out stuff
etc.). The exception to that rule is when coding with the windows
api, obviously.

In this case I neglected to do the 2nd pass. <hangs head in shame>

I would say getting the data types right on the first pass would be
what would count as lazy -- I'm way too lazy to go back and fix it.

In any event, what's complicated about figuring out the data type
for the item in a collection called "Controls"? Seems pretty
obvious!

 

[Stuart McCall]

I would say getting the data types right on the first pass would be
what would count as lazy -- I'm way too lazy to go back and fix it.

Whatever floats your boat. I'm self-taught and picked up 'bad' habits along
the way.

In any event, what's complicated about figuring out the data type
for the item in a collection called "Controls"? Seems pretty
obvious!

Who mentioned complication? I said distraction. I also tend to use
single-char variable names for the same reason. These are then renamed
unless the procedure is a small one where what's happening is obvious.

What can I say? I'm a male of the species and unable to multitask. When I'm
thinking about code, that's what I want to do, not go off at a tangent
thinking up a suitable variable name along the way. I prefer to do that once
the code is up and running.

 

[David W. Fenton]

When I'm
thinking about code, that's what I want to do, not go off at a
tangent thinking up a suitable variable name along the way. I
prefer to do that once the code is up and running.

I write For/Each loops on a daily basis. There's no pondering
necessary. When I'm looping controls:

Dim ctl As Control

For Each ctl In Me.Controls
Next ctl
Set ctl = Nothing

For looping tables:

Dim tdf As TableDef

For Each tdf In CurrentDB.TableDefs
Next tdf
Set tdf = Nothing

For looping queries:

Dim qdf As QueryDef

For Each qdf In CurrentDB.QueryDefs
Next qdf
Set qdf = Nothing

For looping open forms:

Dim i As Integer

For i = 0 To Forms.Count -1
Next i

(I wouldn't use a Form variable because I usually want to control
the order, e.g., For i = Forms.Count-1 To 0 Step -1)

These are standard loops and it shouldn't require any thought at all
to choose variable names or data types for those variables. You
should use the same variable names each time you write the same kind
of code.

I'd think that any regular Access coder would already have a set of
standard variable names that they use. Here are some of mine:

ctl
tdf
qdf
frm
rpt
varItem
i (integer counter)
l (long counter)
rs
db
strSQL
strMsg
strTemp

I would only use nongeneric names for these when I needed more than
one within a subroutine (e.g., if I'm opening two recordsets, I'd
give each a meaningful name; if I'm opening only one, I'd use the
generic rs).

I would expect anyone who codes regularly to have a set of practices
like this. Pondering variable names is not something that takes me
any time at all -- either one of my generic variable names is the
obvious choice, or if not, then the specific name is clear to me
from context.

 

[Stuart McCall]

I write For/Each loops on a daily basis. There's no pondering
necessary. When I'm looping controls:

Dim ctl As Control

For Each ctl In Me.Controls
Next ctl
Set ctl = Nothing

For looping tables:

Dim tdf As TableDef

For Each tdf In CurrentDB.TableDefs
Next tdf
Set tdf = Nothing

For looping queries:

Dim qdf As QueryDef

For Each qdf In CurrentDB.QueryDefs
Next qdf
Set qdf = Nothing

For looping open forms:

Dim i As Integer

For i = 0 To Forms.Count -1
Next i

(I wouldn't use a Form variable because I usually want to control
the order, e.g., For i = Forms.Count-1 To 0 Step -1)

These are standard loops and it shouldn't require any thought at all
to choose variable names or data types for those variables. You
should use the same variable names each time you write the same kind
of code.

I'd think that any regular Access coder would already have a set of
standard variable names that they use. Here are some of mine:

ctl
tdf
qdf
frm
rpt
varItem
i (integer counter)
l (long counter)
rs
db
strSQL
strMsg
strTemp

I would only use nongeneric names for these when I needed more than
one within a subroutine (e.g., if I'm opening two recordsets, I'd
give each a meaningful name; if I'm opening only one, I'd use the
generic rs).

I would expect anyone who codes regularly to have a set of practices
like this. Pondering variable names is not something that takes me
any time at all -- either one of my generic variable names is the
obvious choice, or if not, then the specific name is clear to me
from context.

Wow. Have I touched a nerve? What works for me clearly isn't going to work
for you. So what? I'm not advocating my methods, in fact please note that I
never preach these things on here. The only reason it was mentioned at all
was because Doug was curious.

FWIW I agree with all you say and I can work that way when it suits me.
Witness my posts on here and on my site.