loop and validate 2

[Duane8889]

Hello
I am using Access 2k and have a form with roughly 50 combo boxes. They are in 3 columns as each column represents a time slot and each row is an equipment set. The combos will pull down with a list of employee names. It's basically a rotation schedule for work.

Anyway I would like some validation to prevent an emps name from appearing more than once in a column. I thought a loop for each column would be a good start. For now I just want to make sure each cbo is filled in.
Using the control loop I have...

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
For i = 1 to 20
If IsNullMe.Controls("CboEmp" & i) Then
MsgBox "Fill in selections."
End If
Next i
End Sub

...but the msgbox never comes up whether the combos are filled or not.
Maybe beforeUpdate is the wrong method to put this under.

I also tried it within a save routine but I get the error message "Object required"
if there is an entry or not in the combobox.

Code:
Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click
For i = 1 To 20
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If IsNullMe.Controls("CboEmp" & i) Then
MsgBox "Fill in selections."
End If
Next i

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click

End Sub

Have a great safe weekend!

Hal

 

[PHV]

Have you tried to replace this:
If IsNullMe.Controls("CboEmp" & i) Then
By this ?
If IsNull(Me.Controls("CboEmp" & i)) Then
Or this ?
If Me.Controls("CboEmp" & i).ListIndex < 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Duane8889]

Thanks PHV
It didn't solve the problem but it got me to look at the code a different way.

It still doesn't bring up a msgbox is the combobox is empty.
Very Strange

Duane

 

[PHV]

And this ?
If Trim(Me.Controls("CboEmp" & i) & "") = "" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

Hal,

If the combos are not bound, the form's BeforeUpdate event won't fire if/when changes are made. Try putting your code in a different event, maybe the form's Unload event (Unload is good because the Cancel argument then is available).

Ken S.

 

[Duane8889]

Thanks PHV and Ken S.
I'm getting the error "Access can't find the field 'cboEmp' referred to your expression." I was getting it earlier I should have mentioned it.
It occurs on any of these If statements

Private Sub Form_Unload(Cancel As Integer)
    If Trim(Me.Controls("cboEmp" & i) & "") = "" Then ' here
    'If Me.Controls("cboEmp" & i).ListIndex < 0 Then
    'If IsNull(Me.Controls("cboEmp" & i)) Then
    'If Me.Controls("cboEmp" & i) = "" Then
    'If Me.Controls("CboEmp" & i).ListIndex < 0 Then
        MsgBox "Fill in selections."
    End If
End Sub

So there is another issue going on.
I don't think the combos are unbound cause the control source is set to a field name in the table. I believe if they are unbound it shows the word 'unbound' in the controls value in design view.

Validation has to be easier than this. I do appreciate the help!

Hal

 

[PHV]

What are the names of your 20 combos in this form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Duane8889]

The combo names are cboEmp1, cboEmp2, etc.
The loop isn't right so I added...

Private Sub Form_Unload(Cancel As Integer)
    For i = 1 To 20 ' added
    'If Trim(Me.Controls("cboEmp" & i) & "") = "" Then
    If Me.Controls("cboEmp" & i).ListIndex < 0 Then
    'If IsNull(Me.Controls("cboEmp" & i)) Then
    'If Me.Controls("cboEmp" & i) = "" Then
    'If Me.Controls("CboEmp" & i).ListIndex < 0 Then
        MsgBox "Fill in selections."
    End If
    Next i  ' added
End Sub

I get the msgbox now when a combo is empty but when I click ok it still pops out of the form. So a little tweaking and it should be good. I think I can do that.

Thanks for the patience!

Hal

 

[PHV]

For i = 1 To 20
If Me.Controls("cboEmp" & i).ListIndex < 0 Then
MsgBox "Fill in selections."
Me.Controls("cboEmp" & i).SetFocus
Cancel = True
End If
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Duane8889]

Yes, that works!

Me.Controls("cboEmp" & i).SetFocus
Cancel = True

these all worked too except...

If Trim(Me.Controls("cboEmp" & i) & "") = "" Then
If Me.Controls("cboEmp" & i).ListIndex < 0 Then
If IsNull(Me.Controls("cboEmp" & i)) Then
If Me.Controls("cboEmp" & i) = "" Then ' this did not work
If Me.Controls("CboEmp" & i).ListIndex < 0 Then

thanks again you saved me hours!

Hal

 

[TheAceMan1]

How are ya Duane8889 . . . . .

To prevent duplicates:
[ol][li]In the [blue]Tag[/blue] Property for all the comboxes in column1 enter [purple]1[/purple], column2 enter [purple]2[/purple],column3 enter [purple]3[/purple].
Note: you can group select each column and enter once in the Tag property.[/li]
[li]Add the following code to the BeforeUpdate event of the form:

[blue]   Dim Col As Integer, Src, Cmp, Idx As Integer, Flg As Boolean
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   For Col = 1 To 3
      For Each Src In Me.Controls
         If Src.Tag = Col Then
            If Trim(Src & "") <> "" Then
               Idx = Src.TabIndex
               
               For Each Cmp In Me.Controls
                  If Cmp.Tag = Col Then
                     If Cmp.TabIndex <> Idx Then
                        If Trim(Cmp & "") <> "" Then
                           If Src = Cmp Then
                              Msg = "Duplicate Data '" & Src.Column(1) & "' in Column" & Col & " !" & DL & _
                                    "Correct the duplication and try again . . ."
                              Style = vbInformation + vbOKOnly
                              Title = "Column" & Col & " Duplicates Data Detected!"
                              MsgBox Msg, Style, Title
                              
                              Src.SetFocus
                              Flg = True
                              Cancel = True
                              Exit For
                           End If
                        End If
                     End If
                  End If
               Next
            End If
         End If
         
         If Flg Then Exit For
      Next
      
      If Flg Then Exit For
   Next[/blue]

[/li][/ol]
[purple]Cheers! . . .[/purple]

See Ya! . . . . . .

 

[Duane8889]

thanks Ace
Excellent code, exactly what I needed!!

Hal