settin up a control Array

[awrude]

I have a form with many 'like' controls. I have named them cboLocation1, cboLocation2...etc. No rather than set up each individual control (i have 23) i am trying to do it in VBA. I have created a control array to attempt to do this. I tried me.cbLocation(i).ControlSource = "location" & e
in the first for next but it told me it couldnt find it or something like that. My data fields are location0, location1, etc...

This is giving me an error in the second for next loop. says it cant find the method or data field. any suggestions would be great.


Dim cbLocation(1 To 4) As Control
Dim i As Integer
Dim e As Integer

e = 0
For i = 1 To 4
Set cbLocation(i) = Controls("cbolocation" & i)
'txtSerial1.Value = "Test" & i
Next i

For i = 1 To 4
me.cbLocation(i).ControlSource = "location" & e

e = e + 1
Next i

 

[jdraw]

What are you trying to do in plain English?

Your post seems to be more about HOW you are using combo boxes and you're having vba syntax type issues.

 

[awrude]

I am wondering what i am able to do with the control after i set it. after i do this: Set cbLocation(i) = Controls("cbolocation" & i), can i access that control now using cbLocation? Can I change anything with that control using cbLocation?

 

[mdlueck]

Is this a bound form, and you wish to verify that Control1 is still bound to Column1, and so on?

 

[awrude]

originally i was going to do a mass recordsource rather than change each one manually. I did just end up entering them in manually.

Now I am trying to set up a textbox check, to make sure that no blanks were left in the form. I have set this up in the BeforeUpdate of the form. Can i use the control array somehow to do a mass check in a for next loop? The following works fine but rather than enter this in for each textbox i am checking, i thought a control array may be a lot neater and cleaner.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.txtSerial1 & vbNullString) = 0 Then
MsgBox "You must enter a serial number"
Me.txtSerial1.SetFocus
Cancel = True
Exit Sub
End If
End Sub

 

[mdlueck]

Now I am trying to set up a textbox check, to make sure that no blanks were left in the form.

I do this sort of thing in what I call my Validation classes. These are classes that the AddRecord / EditRecord forms make an instance of, make call to the class passing the call a pointer back to itself, and the class then manipulates the form... reading controls, populating controls, etc...

So on this point, my shared code to safely read a field control no matter what:

Safely read form field text controls
http://www.access-programmers.co.uk/forums/showthread.php?p=1131039#post1131115

So I read all fields into class attributes. Then I validate the attributes. Required fields which have an unacceptable value I set to Red background, and do not touch the DB.

If Validation passes, then I go ahead and transfer the attribute values to the DB class attributes, then call the appropriate INSERT / UPDATE method of that class.

 

[missinglinq]

You don't need a Control array, you just need to loop through the Control Collection. This code will loop through all Textboxes and Comboboxes on a Form and check that they're populated. Each time one is found to be empty, a message will pop up, specific to that particular Control, and Focus will be returned to that Control, allowing the user to correct the data omission for the Control. After data is entered in this Control, and the user attempts to leave the Record or close the Form, the Controls will be checked again, and the process repeated until all Controls are populated.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

Dim CName As String


For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox
            If Nz(ctl, "") = "" Then
              CName = ctl.Controls(0).Caption
              MsgBox "Following field is required: " & vbCrLf & vbCrLf & CName
              Cancel = True
              ctl.SetFocus
              Exit Sub
            End If
    End Select
Next ctl

End Sub

This example checks Textboxes and Comboboxes, but other types of Controls can easily be added.

Linq ;0)>

 

[awrude]

i tried the code you sent.

it comes back with a 'expression refers to an object that is closed or doesnt exist' and highlights "CName = ctl.Controls(0).Caption"

any suggestions? I am not familiar enough with the controls functions to play around with it

 

[missinglinq]

What version of Access are you using? Code works without any problems in versions 2003 and 2007.

At any rate, replace the line

CName = ctl.Controls(0).Caption

with

CName = ctl.Name

Linq ;0)>