p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Loop through combo boxes (http://p2p.wrox.com/showthread.php?t=38876)

Scootterp February 22nd, 2006 01:32 PM

Loop through combo boxes
I have not used VBA for about a year so I am a little rusty. I have a form with 25 combo boxes (not my design) and I need to check each combo box to see if it is populated. Is there a way to iterate through the combo boxes, say in a loop? The problem I am having is I do not know how to change the combo box name (cmb1) to the next combo box (cmb2) for the next loop.


kindler February 22nd, 2006 04:19 PM

Loop through the controls on the form

for i = 0 to form.controls.count - 1
if form.controls(i).type = acComboBox
 'do stuff here
end if

Scootterp February 22nd, 2006 07:53 PM

Thanks Kindler, I will see if I can get it to work.


Scootterp February 23rd, 2006 05:11 PM

Kindler, I finally got it to work. I kept getting errors when using the "if form.controls(i).type = acComboBox then" statement. But knowing that it is the "controls" allowed me to figure it out.

The 25 combo boxes go from cmbCF1 to cmbCF25.

For i = 1 To 25
    If IsNull(Me.Controls("cmbCF" & i)) = False Then
        Ptotals = Ptotals + CFPoints
    End If
Next i

Thanks again.


SerranoG February 24th, 2006 09:50 AM

Say, I have found that to be a problem in the past as well. Access XP (2002) doesn't want to recognize the .Type method, that is, a statement like this always fails:

if form.controls(i).type = acComboBox

I get around it by placing a unique string in my controls of interest .Tag property and do use a loop, but I use the .Tag property instead of the .Type property to pinpoint my controls.

Why does Access XP not accept the .Type method?

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

kindler February 24th, 2006 12:06 PM

You've got me Serrano, I'm using Access 2k3 but on databases saved in the 2000/02 format. I had to loop through and check for acCheckBox types and it worked (if I remember right). I ended up scrapping the type check though (so maybe it didn't work? It was a few months ago I wrote the code for that function) and just did a name check against the value in a field.

Glad you were able to get it working Scooter.

All times are GMT -4. The time now is 01:15 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.