Loop not working

[cb9002]

Hi

I am a beginner to VBA! I am trying to write a module that checks the content of six unbound text boxes, defined in an array. If the boxes do not contain any content, I wish to prompt the user to enter content. My code is:

Private Sub Submit_Click()
Dim DefectType(0 To 5) As String
DefectType(0) = "SurfaceBox"
DefectType(1) = "MarkingsBox"
DefectType(2) = "LightingBox"
DefectType(3) = "FenceBox"
DefectType(4) = "GatesBox"
DefectType(5) = "OtherBox"

Dim x As Integer

For MyCounter = 0 To 5
If DefectType(MyCounter).Value = "" Then
x = MsgBox("Please indicate whether " & DefectType(MyCounter) & " is satisfactory or not and describe any defect", vbOKOnly)
End If

Next
End Sub

I've tried loads of variations on the theme but keep getting an error: "Invalid qualifier".

Thanks for any help

 

[Remou]

If the code is running on the form where the textboxes are, instead of:

If DefectType(MyCounter).Value = "" Then

Try:

[tt]If Me(DefectType(MyCounter))= "" Then
[/tt]

 

[SkipVought]

Hi,

DefectType is not an object. Hence, loose the .Value...

If DefectType(MyCounter)[s].Value[/s] = "" Then

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cb9002]

Thanks for that Remou, it got rid of the error but didn't work! The MsgBox didn't come up if the text box was empty. I managed to sort that as you'll see below though.

Here's revised code:

Option Compare Database

Dim DefectType(0 To 5) As String
DefectType(0) = "Surface"
DefectType(1) = "Markings"
DefectType(2) = "Lighting"
DefectType(3) = "Fence"
DefectType(4) = "Gates"
DefectType(5) = "Other"

Private Sub Submit_Click()

Dim x As Integer

For MyCounter = 0 To 5
If Me((DefectType(MyCounter)) & "Box") <> "" Then
Else: x = MsgBox("Please indicate whether the " & DefectType(MyCounter) & " is satisfactory or not and describe any defect", vbOKOnly)
End If
Next

x = MsgBox("Report submitted, thank you", vbOKOnly)
DoCmd.Close , , acSaveYes
End Sub

The boxes I'm trying to check are called SurfaceBox, MarkingsBox etc.

However, I'm now getting an "Invalid outside procdure" error, I presume I've made a mistake in the If expression again?

 

[Remou]

Like so:

Private Sub Submit_Click()
Dim DefectType(0 To 5) As String
DefectType(0) = "Surface"
DefectType(1) = "Markings"
DefectType(2) = "Lighting"
DefectType(3) = "Fence"
DefectType(4) = "Gates"
DefectType(5) = "Other"

'Dim x As Integer

    For MyCounter = 0 To 5
        'Better to pick up empty strings and nulls
        If Trim(Me((DefectType(MyCounter)) & "Box") & "")="" Then
            MsgBox "Please indicate whether the " _
              & DefectType(MyCounter) _
              & " is satisfactory or not and describe any defect", vbOKOnly
        End If
    Next
    
    MsgBox "Report submitted, thank you", vbOKOnly
    DoCmd.Close , , acSaveYes
End Sub

 

[TheAceMan1]

How are ya cb9002 . . .

To relieve yourself of the array and hard coding textbox names, try the following:
[ol][li]In the [blue]Tag[/blue] property of your six unbound textboxes, enter a question mark [blue]?[/blue] [red](no quotations please!)[/red].[/li]
[li]In the forms [blue]Before Update[/blue] event, copy/paste the following:

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim ctl As Control
   
   For Each ctl In Me.Controls
      If ctl.tag = "[b]?[/b]" Then
         If Trim(ctl & "") = "" Then
            Msg = "Please indicate whether " & ctl.Name & " " & _
                  "is satisfactory or not and describe any defect."
            Style = vbInformation + vbOKOnly
            Title = "Data Input Required! . . ."
            MsgBox Msg, Style, Title
            ctl.SetFocus
            Cancel = True
            Exit For
         End If
      End If
   For Next[/blue]

[/li]
[li]Save and perform your testing.[/li][/ol]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cb9002]

That's great, thank you. This is my first foray into programming since learning C at uni about 15 years ago!

I understand now I don't need the x=MsgBox as long as I remove the brackets.

Could you explain why you've concatenated the null string at the end of the variable and then used Trim?

Also, on the first error I made, I understand that DefectType(MyCounter) is a variable and not an object, but since it refers to an object why don't I need .value - for example could I use

If Me((DefectType(MyCounter).Enabled)=True then....

Thanks again - I'm trying to deepen my understanding so I don't have to ask so many questions!

 

[cb9002]

My last message was directed at Remou.

Thanks Aceman, I'll try that solution later. I can see how that makes better sense and avoids the array.

 

[Remou]

If you use that format (trim etc), you will trap space-filled strings, zero-length strings and nulls, whereas the format you used would miss both nulls and space-filled strings.

Value is somewhat optional in VBA. Feel free to use it if it suits your style.

 

[JBinQLD]

Especially if you want added value.

He he he. I crack me up. Oh dear, it's def bed time....

JB

 

[TheAceMan1]

cb9002 . . .

There's a flaw in th code I provided. The ending [blue]For Next[/blue] should be [blue]Next[/blue].
Sorry about the mess . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]