Re-ordering Combo Box Values

[Guest]

I currently have a form with combo-boxes on it. I intend users to run an SQL
driven query based on values chosen for these combo-boxes. The values chosen
are 1st,2nd,3rd....8th or 'n/a' . These are to correspond to which and where
fields will be returned in a query and hence will define a SELECT clause.
'n/a' refers to fields NOT to be included with '1st', '2nd' etc referring to
a chosen field's place in the SELECT statement. Obviously the first combo box
value will not always be 1st and so-on , so I do need to re-order these
accordingly before constructing the SQL string. Please help - my brain aches
!!! -(I'm using Access 2003 (VB version 6.3) - i still tend to use DAO and
not ADO.)

 

[SteveS]

I currently have a form with combo-boxes on it. I intend users to run an SQL
driven query based on values chosen for these combo-boxes. The values chosen
are 1st,2nd,3rd....8th or 'n/a' . These are to correspond to which and where
fields will be returned in a query and hence will define a SELECT clause.
'n/a' refers to fields NOT to be included with '1st', '2nd' etc referring to
a chosen field's place in the SELECT statement. Obviously the first combo box
value will not always be 1st and so-on , so I do need to re-order these
accordingly before constructing the SQL string. Please help - my brain aches
!!! -(I'm using Access 2003 (VB version 6.3) - i still tend to use DAO and
not ADO.)

How many combo boxes are there? It sounds like 8. Or are there 8 pairs of combo
boxes - one for order and the other (in the pair) for the field name?

Out of curiosity, why does it matter which field is first? Maybe if you use a
datasheet to display results, but ....?

Do you have any code that you have tried? Something to get a better idea of
what you are trying to do.

 

[Guest]

...there are 8 combos - one for each possible field in query. Order is
important as I intend to SUM a Units field at the end of the grouped fields
and that will be varied in value depending on which and in what order the
preceding fields are. So if e.g. Combo 2 = 4th / Combo 3 = 2nd / Combo 5 =
1st and Combo 8 = 3rd, the code will need to create a SELECT statement as
follows : SELECT (combo5 ref) , (combo3 ref) , (combo8 ref), (combo 2ref),Sum
of UNITS - so it needs to first ignore other combo values where 'n/a' was
selected, then IMPORTANTLY re-order references to combos with required values
e.g 1st,2nd,3rd etc - then build a SELECT statement from them

 

[Guest]

Try this:

Create 8 combo boxes named cbo1 - cbo8
---
Select the 8 combo boxes, then open properties

If you select all 8 combo boxes, you can set the properties for all 8 at
the same time... less typing
---
Click on the "Data Tab". Set these properties:
Row Source Type = Value List
Row Source = 0;;1;1;2;2;3;3;4;4;5;5;6;6;7;7;8;8
Limit To List = Yes
Default Value = 0 (zero)
---
Click on the "Format Tab"

Column Count = 2
Column Widths = 0";0.2"
---
Select cbo1

Click on the "Other tab"

For each of the combo boxes put the associated field name in the TAG property

Tag = NameOf Field

The code uses the TAG property to get the field names
---
Create a button to clear the combo boxes. Name it btnClear
---
Create a button to make the SQL string. Name it "btnDoIt"
---

Open the code window

Paste in the following code:
'******************************************
Option Compare Database
Option Explicit
Option Base 1

' button code
Private Sub btnDoIt_Click()
Dim strSQL As String

'to store the field names
Dim x(8) As String
'counters
Dim i As Integer, j As Integer

strSQL = ""

'clear array
For i = 1 To 8
x(i) = ""
Next i

'put the field names in the array
For i = 1 To 8
For j = 1 To 8
If Me("cbo" & j) = i Then
x(i) = Me("cbo" & j).Tag
End If
Next j
Next i

' build the field list
For i = 1 To 8
If Len(x(i)) > 0 Then
strSQL = strSQL & ", " & x(i)
End If
Next i

'create the SQL string
If Len(strSQL) > 0 Then
' change MyTable to the name of your table
strSQL = "Select " & Mid(strSQL, 3) & " From MyTable"
End If

' do something
MsgBox strSQL

End Sub

' resets the combo boxes
Private Sub btnClear_Click()
Me.cbo1 = 0
Me.cbo1.BackColor = vbWhite
Me.cbo2 = 0
Me.cbo2.BackColor = vbWhite
Me.cbo3 = 0
Me.cbo3.BackColor = vbWhite
Me.cbo4 = 0
Me.cbo4.BackColor = vbWhite
Me.cbo5 = 0
Me.cbo5.BackColor = vbWhite
Me.cbo6 = 0
Me.cbo6.BackColor = vbWhite
Me.cbo7 = 0
Me.cbo7.BackColor = vbWhite
Me.cbo8 = 0
Me.cbo8.BackColor = vbWhite
Me.Label33.Caption = "."
End Sub


Private Sub cbo1_AfterUpdate()
ChkDup
End Sub

Private Sub cbo2_AfterUpdate()
ChkDup
End Sub

Private Sub cbo3_AfterUpdate()
ChkDup
End Sub

Private Sub cbo4_AfterUpdate()
ChkDup
End Sub

Private Sub cbo5_AfterUpdate()
ChkDup
End Sub

Private Sub cbo6_AfterUpdate()
ChkDup
End Sub

Private Sub cbo7_AfterUpdate()
ChkDup
End Sub

Private Sub cbo8_AfterUpdate()
ChkDup
End Sub

' checks for duplicate selections
' ie cbo1 = 1 and cbo7 = 1
Private Sub ChkDup()
Dim same As Boolean

same = False
If Me.cbo1 = 0 Then
Me.cbo1.BackColor = vbWhite
Else
same = (Me.cbo1 = Me.cbo2) Or (Me.cbo1 = Me.cbo3) _
Or (Me.cbo1 = Me.cbo4) Or (Me.cbo1 = Me.cbo5) _
Or (Me.cbo1 = Me.cbo6) Or (Me.cbo1 = Me.cbo7) _
Or (Me.cbo1 = Me.cbo8)
If same Then
Me.cbo1.BackColor = vbYellow
Else
Me.cbo1.BackColor = vbWhite
End If
End If

same = False
If Me.cbo2 = 0 Then
Me.cbo2.BackColor = vbWhite
Else
same = (Me.cbo2 = Me.cbo1) Or (Me.cbo2 = Me.cbo3) _
Or (Me.cbo2 = Me.cbo4) Or (Me.cbo2 = Me.cbo5) _
Or (Me.cbo2 = Me.cbo6) Or (Me.cbo2 = Me.cbo7) _
Or (Me.cbo2 = Me.cbo8)
If same Then
Me.cbo2.BackColor = vbYellow
Else
Me.cbo2.BackColor = vbWhite
End If
End If

same = False
If Me.cbo3 = 0 Then
Me.cbo3.BackColor = vbWhite
Else
same = (Me.cbo3 = Me.cbo1) Or (Me.cbo3 = Me.cbo2) _
Or (Me.cbo3 = Me.cbo4) Or (Me.cbo3 = Me.cbo5) _
Or (Me.cbo3 = Me.cbo6) Or (Me.cbo3 = Me.cbo7) _
Or (Me.cbo3 = Me.cbo8)
If same Then
Me.cbo3.BackColor = vbYellow
Else
Me.cbo3.BackColor = vbWhite
End If
End If

same = False
If Me.cbo4 = 0 Then
Me.cbo4.BackColor = vbWhite
Else
same = (Me.cbo4 = Me.cbo1) Or (Me.cbo4 = Me.cbo2) _
Or (Me.cbo4 = Me.cbo3) Or (Me.cbo4 = Me.cbo5) _
Or (Me.cbo4 = Me.cbo6) Or (Me.cbo4 = Me.cbo7) _
Or (Me.cbo4 = Me.cbo8)
If same Then
Me.cbo4.BackColor = vbYellow
Else
Me.cbo4.BackColor = vbWhite
End If
End If

same = False
If Me.cbo5 = 0 Then
Me.cbo5.BackColor = vbWhite
Else
same = (Me.cbo5 = Me.cbo1) Or (Me.cbo5 = Me.cbo2) _
Or (Me.cbo5 = Me.cbo3) Or (Me.cbo5 = Me.cbo4) _
Or (Me.cbo5 = Me.cbo6) Or (Me.cbo5 = Me.cbo7) _
Or (Me.cbo5 = Me.cbo8)
If same Then
Me.cbo5.BackColor = vbYellow
Else
Me.cbo5.BackColor = vbWhite
End If
End If

same = False
If Me.cbo6 = 0 Then
Me.cbo6.BackColor = vbWhite
Else
same = (Me.cbo6 = Me.cbo1) Or (Me.cbo6 = Me.cbo2) _
Or (Me.cbo6 = Me.cbo3) Or (Me.cbo6 = Me.cbo4) _
Or (Me.cbo6 = Me.cbo5) Or (Me.cbo6 = Me.cbo7) _
Or (Me.cbo6 = Me.cbo8)
If same Then
Me.cbo6.BackColor = vbYellow
Else
Me.cbo6.BackColor = vbWhite
End If
End If

same = False
If Me.cbo7 = 0 Then
Me.cbo7.BackColor = vbWhite
Else
same = (Me.cbo7 = Me.cbo1) Or (Me.cbo7 = Me.cbo2) _
Or (Me.cbo7 = Me.cbo3) Or (Me.cbo7 = Me.cbo4) _
Or (Me.cbo7 = Me.cbo5) Or (Me.cbo7 = Me.cbo6) _
Or (Me.cbo7 = Me.cbo8)
If same Then
Me.cbo7.BackColor = vbYellow
Else
Me.cbo7.BackColor = vbWhite
End If
End If

same = False
If Me.cbo8 = 0 Then
Me.cbo8.BackColor = vbWhite
Else
same = (Me.cbo8 = Me.cbo1) Or (Me.cbo8 = Me.cbo2) _
Or (Me.cbo8 = Me.cbo3) Or (Me.cbo8 = Me.cbo4) _
Or (Me.cbo8 = Me.cbo5) Or (Me.cbo8 = Me.cbo6) _
Or (Me.cbo8 = Me.cbo7)
If same Then
Me.cbo8.BackColor = vbYellow
Else
Me.cbo8.BackColor = vbWhite
End If
End If
End Sub
'******************************************
Now hook up the events:

Click on the EVENTS tab for each if the buttons and create an "OnClick"
event procedure.

For each of the combo boxes, create an "AfterUpdate" event procedure.
These call the SUB ChkDup(). If there are two or more combo boxes selected
with the same order, it turns the back color yellow to indicate an error.

HTH

 

[Guest]

Thank you ! - this looks very promising - i will give it a go soon and let
you know if it worked !