Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 15th, 2007, 12:32 PM
Authorized User
Join Date: Jan 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default Loop through form looking for Combo Control

The aim is to ensure that an application form has all the necessary fields filled in. So what I am attempting is to create a basic loop through the form controls where they find a Combo Box, which is then interogated. If no value is found the field is highlighted and the Sub is exited.

So what I have is

For i = 0 To Form.Controls.Count - 1

If Form.Controls(i).type=acComboBox Then
  If IsNull(Form.Controls(i)) Then
  MsgBox "Required Field", vbCritical, "Required"
  Form.Controls(i).BackColor = 8454143
  Exit Sub
  End If
End If

It doesn't like the .type property any other suggestions.

Old November 16th, 2007, 09:17 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

This worked for me when enumerating listboxes.

Dim ctl As Control

For Each ctl In Me.Controls
    With ctl
        If .ControlType = acListBox Then
            MsgBox "Worked"
        End If
    End With
Next ctl

Can you use this?


Look it up at: http://wrox.books24x7.com
Old November 16th, 2007, 04:34 PM
Authorized User
Join Date: Jan 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks I didn't actually use your advice with this question. But you have back to me with good advice on other topics, so thanks again.

Below is the code i used

quote:Private Sub Command1_Click()
On Error GoTo Err_Command1_Click

Dim ctrl As Control
Dim CboBox As ComboBox

For Each ctrl In Me.Controls

If (TypeOf ctrl Is ComboBox) Then

    If IsNull(ctrl) Then

    Set CboBox = ctrl

    With CboBox
    .BackColor = vbYellow
    End With

    MsgBox "Field Required", vbCritical, "Required"


    End If

End If

Old November 20th, 2007, 09:17 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

You originally used TYPE whereas MMcDonal used CONTROLTYPE. I also have never been able to get that to work so I have had to use tags and compare the TAG properties. So both your solutions will be something I'll try in the future, i.e. ControlType and TypeOf.

Remember to write code to change the backcolor BACK to white when the data is actually entered!

Note, if you use conditional formatting to make any field with the focus change backcolor, there would be no need to actually assign the backcolor at all. It'll just automatically change to yellow and back to white when a field gets/loses focus.

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop through records on Continuous Form paulcbyrum Access 1 May 13th, 2008 06:28 AM
Mouse wheel control with combo box Coby Excel VBA 0 January 30th, 2008 06:36 PM
Loop through combo boxes Scootterp Access VBA 5 February 24th, 2006 12:06 PM
Unique Control IDs inside a loop using a varible Joshua Sebastian Classic ASP Basics 2 February 3rd, 2005 10:44 AM
Creating custom Combo box control nazran VB How-To 2 January 21st, 2004 02:30 PM

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