Combo Box issue for Call Center Shift Bid

[vapid2323]

Hey guys,

I am trying to limit what my users can choose when filling out a shift bid. This is a VERY simple database and will be tossed when the bid is over.

I have two tables "Shiftbid" and "tblOptions"

In my form I have a list of combo boxes that have their datasource set to "tblOptions" the user has a numerical choice from 1 to 14.

As the user is filling out the options I want to limit the choices available in the other combo boxes.

For instance a user would not be able to go down the list and place a "1" in each combo box, if they did, they would receive an error.

I tried to search for an answer but most deal with cascading combo boxes, where a user wants to filter table values based on another table.

I have attached an image if it helps.

Thank you!

 

[pbaldy]

Well, I probably wouldn't go about it that way, because users will invariably move around playing with the order, and at any given point might have 2 of the same value. I'd test at the end after the user is done making their selections. They also would likely not fill out the form from top to bottom, making managing the selections trickier.

I can't think of a really elegant solution off the top of my head. The best I can think of offhand is a loop of those controls within a "For x = 1 to 14" loop. Basically you'd be testing to make sure one of the controls has each of the numbers.

 

[vapid2323]

Well, I probably wouldn't go about it that way, because users will invariably move around playing with the order, and at any given point might have 2 of the same value. I'd test at the end after the user is done making their selections. They also would likely not fill out the form from top to bottom, making managing the selections trickier.

I can't think of a really elegant solution off the top of my head. The best I can think of offhand is a loop of those controls within a "For x = 1 to 14" loop. Basically you'd be testing to make sure one of the controls has each of the numbers.

Good point, I understand what you are asking me to do but I don’t quite know how to implement it within VB. I will continue to research this but any more info will be appreciated.
P.S. I am a Noob with VB but an expert with google

 

[pbaldy]

This is quick and dirty, but has the basics (it uses the tag property of the desired controls):

  Dim ctl                     As Control
  Dim x                       As Integer
  
  For x = 1 To 14
    For Each ctl In Me.Controls
      If ctl.Tag = "whatever" Then
        If ctl.Value = x Then
          Exit For
        Else
          MsgBox "Hey there's no " & x
        End If
      End If
    Next ctl
  Next x

 

[vapid2323]

This is quick and dirty, but has the basics (it uses the tag property of the desired controls):

  Dim ctl                     As Control
  Dim x                       As Integer
 
  For x = 1 To 14
    For Each ctl In Me.Controls
      If ctl.Tag = "whatever" Then
        If ctl.Value = x Then
          Exit For
        Else
          MsgBox "Hey there's no " & x
        End If
      End If
    Next ctl
  Next x

Thank you for puting this together for me I will give it a shot and let you know if it works!

 

[pbaldy]

The problem with quick and dirty is that sometimes it's too dirty. I noticed that had logical flaw. This is still quick but maybe not as dirty.

  Dim ctl                     As Control
  Dim x                       As Integer
  Dim booTest                 As Boolean

  For x = 1 To 14
    For Each ctl In Me.Controls
      If ctl.Tag = "whatever" Then
        If ctl.Value = x Then
          booTest = True
          Exit For
        Else
          booTest = False
        End If
      End If
    Next ctl
    If booTest = False Then
      MsgBox "Hey there ain't no " & x
      Exit Sub
    End If
  Next x
  MsgBox "It's all good"

 

[vapid2323]

Thank you for helping me, unfortunately I was not able to get this to work.

Not because of your code, but because I hit my deadline for the project

I will try a second time when my next shift bid comes around.

Thank you for all your help!

 

[pbaldy]

No problemo; post back when you get back into it. I'm curious to see if it works.