Visual Basic&database, Reduce amount of code

Paul Whit
#1 / 3

Reduce amount of code

I'm sure there is a way to drastically reduce the maount of code to perform
the below.
Any suggestions ?

'**********************************************************************
sSQL = "SELECT * FROM STRU"
adoRecordset.Open (sSQL), adoConnection
MSFlexGrid1.Row = 0
nColCount = 0
Do While Not adoRecordset.EOF
nColCount = nColCount + 1
MSFlexGrid1.Col = nColCount
MSFlexGrid1.Text = adoRecordset!Name

If adoRecordset!Type = "C" Then

If nColCount = 1 Then
If ob1 Then
sSQLOrder = " ORDER BY " & adoRecordset!Name
If obGroupYes Then
sSQLSelect = adoRecordset!Name
sSQLGroup = " GROUP BY " & adoRecordset!Name
Else
sSQLSelect = adoRecordset!Name
End If
Else
If Not obGroupYes Then
sSQLSelect = adoRecordset!Name
End If
End If

ElseIf nColCount = 2 Then
If ob2 Then
sSQLOrder = " ORDER BY " & adoRecordset!Name
If obGroupYes Then
sSQLSelect = adoRecordset!Name
sSQLGroup = " GROUP BY " & adoRecordset!Name
Else
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
Else
If Not obGroupYes Then
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
End If

ElseIf nColCount = 3 Then
If ob3 Then
sSQLOrder = " ORDER BY " & adoRecordset!Name
If obGroupYes Then
sSQLSelect = adoRecordset!Name
sSQLGroup = " GROUP BY " & adoRecordset!Name
Else
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
Else
If Not obGroupYes Then
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
End If

ElseIf nColCount = 4 Then
If ob4 Then
sSQLOrder = " ORDER BY " & adoRecordset!Name
If obGroupYes Then
sSQLSelect = adoRecordset!Name
sSQLGroup = " GROUP BY " & adoRecordset!Name
Else
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
Else
If Not obGroupYes Then
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
End If

ElseIf nColCount = 5 Then
If ob5 Then
sSQLOrder = " ORDER BY " & adoRecordset!Name
If obGroupYes Then
sSQLSelect = adoRecordset!Name
sSQLGroup = " GROUP BY " & adoRecordset!Name
Else
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
Else
If Not obGroupYes Then
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
End If

ElseIf nColCount = 6 Then
If ob6 Then
sSQLOrder = " ORDER BY " & adoRecordset!Name
If obGroupYes Then
sSQLSelect = adoRecordset!Name
sSQLGroup = " GROUP BY " & adoRecordset!Name
Else
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
Else
If Not obGroupYes Then
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
End If

ElseIf nColCount = 7 Then
If ob7 Then
sSQLOrder = " ORDER BY " & adoRecordset!Name
If obGroupYes Then
sSQLSelect = adoRecordset!Name
sSQLGroup = " GROUP BY " & adoRecordset!Name
Else
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
Else
If Not obGroupYes Then
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
End If

ElseIf nColCount = 8 Then
If ob8 Then
sSQLOrder = " ORDER BY " & adoRecordset!Name
If obGroupYes Then
sSQLSelect = adoRecordset!Name
sSQLGroup = " GROUP BY " & adoRecordset!Name
Else
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
Else
If Not obGroupYes Then
sSQLSelect = sSQLSelect & "," & adoRecordset!Name
End If
End If
End If

Else
If obGroupYes Then
sSQLNumbers = sSQLNumbers & ",sum(" & adoRecordset!Name &
")"
Else
sSQLNumbers = sSQLNumbers & "," & adoRecordset!Name
End If
End If

adoRecordset.MoveNext
Loop
adoRecordset.Close
'**********************************************************************

Wed, 25 Jun 2003 23:51:03 GMT

HR
#2 / 3

Reduce amount of code

Here's one way. Assuming all that code for ColCount 1 to 8 is identical
(which I was too lazy to examine in painstaking detail), then if ob1 thru
ob8 were addressable as obarray(1 to 8), you could simply code

If nColCount > 0 and nColCount < 9 then
if obarray(nColCount) then
the rest of the "Type = C" code, one time only

If you can't change the ob1 thru ob8 variables into an array, create a new
array and initialize the 8 elements before the If statement.

Quote:

> I'm sure there is a way to drastically reduce the maount of code to
perform
> the below.
> Any suggestions ?

'**********************************************************************

Quote:

> sSQL = "SELECT * FROM STRU"
> adoRecordset.Open (sSQL), adoConnection
> MSFlexGrid1.Row = 0
> nColCount = 0
> Do While Not adoRecordset.EOF
> nColCount = nColCount + 1
> MSFlexGrid1.Col = nColCount
> MSFlexGrid1.Text = adoRecordset!Name

> If adoRecordset!Type = "C" Then

> If nColCount = 1 Then
> If ob1 Then
> sSQLOrder = " ORDER BY " & adoRecordset!Name
> If obGroupYes Then
> sSQLSelect = adoRecordset!Name
> sSQLGroup = " GROUP BY " & adoRecordset!Name
> Else
> sSQLSelect = adoRecordset!Name
> End If
> Else
> If Not obGroupYes Then
> sSQLSelect = adoRecordset!Name
> End If
> End If

> ElseIf nColCount = 2 Then
> If ob2 Then
> sSQLOrder = " ORDER BY " & adoRecordset!Name
> If obGroupYes Then
> sSQLSelect = adoRecordset!Name
> sSQLGroup = " GROUP BY " & adoRecordset!Name
> Else
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> Else
> If Not obGroupYes Then
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> End If

> ElseIf nColCount = 3 Then
> If ob3 Then
> sSQLOrder = " ORDER BY " & adoRecordset!Name
> If obGroupYes Then
> sSQLSelect = adoRecordset!Name
> sSQLGroup = " GROUP BY " & adoRecordset!Name
> Else
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> Else
> If Not obGroupYes Then
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> End If

> ElseIf nColCount = 4 Then
> If ob4 Then
> sSQLOrder = " ORDER BY " & adoRecordset!Name
> If obGroupYes Then
> sSQLSelect = adoRecordset!Name
> sSQLGroup = " GROUP BY " & adoRecordset!Name
> Else
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> Else
> If Not obGroupYes Then
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> End If

> ElseIf nColCount = 5 Then
> If ob5 Then
> sSQLOrder = " ORDER BY " & adoRecordset!Name
> If obGroupYes Then
> sSQLSelect = adoRecordset!Name
> sSQLGroup = " GROUP BY " & adoRecordset!Name
> Else
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> Else
> If Not obGroupYes Then
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> End If

> ElseIf nColCount = 6 Then
> If ob6 Then
> sSQLOrder = " ORDER BY " & adoRecordset!Name
> If obGroupYes Then
> sSQLSelect = adoRecordset!Name
> sSQLGroup = " GROUP BY " & adoRecordset!Name
> Else
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> Else
> If Not obGroupYes Then
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> End If

> ElseIf nColCount = 7 Then
> If ob7 Then
> sSQLOrder = " ORDER BY " & adoRecordset!Name
> If obGroupYes Then
> sSQLSelect = adoRecordset!Name
> sSQLGroup = " GROUP BY " & adoRecordset!Name
> Else
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> Else
> If Not obGroupYes Then
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> End If

> ElseIf nColCount = 8 Then
> If ob8 Then
> sSQLOrder = " ORDER BY " & adoRecordset!Name
> If obGroupYes Then
> sSQLSelect = adoRecordset!Name
> sSQLGroup = " GROUP BY " & adoRecordset!Name
> Else
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> Else
> If Not obGroupYes Then
> sSQLSelect = sSQLSelect & "," & adoRecordset!Name
> End If
> End If
> End If

> Else
> If obGroupYes Then
> sSQLNumbers = sSQLNumbers & ",sum(" & adoRecordset!Name &
> ")"
> Else
> sSQLNumbers = sSQLNumbers & "," & adoRecordset!Name
> End If
> End If

> adoRecordset.MoveNext
> Loop
> adoRecordset.Close

'**********************************************************************

- Show quoted text -

Thu, 26 Jun 2003 01:00:37 GMT

Paul Whit
#3 / 3

Reduce amount of code

I tried this method, which seems to work fine. Thought I would post it for
any interested parties..........

Dim nParam As Integer
Dim myOption As OptionButton

If adoRecordset!Type = "C" Then
nParam = 1
For nParam = 1 To 8
Set myOption = Me.Controls("ob" & nParam)
If nColCount = nParam Then
If myOption Then
sSQLOrder = " ORDER BY " & adoRecordset!Name
If obGroupYes Then
sSQLSelect = adoRecordset!Name
sSQLGroup = " GROUP BY " & adoRecordset!Name
Else
If nParam = 1 Then
sSQLSelect = adoRecordset!Name
Else
sSQLSelect = sSQLSelect & "," &
adoRecordset!Name
End If
End If
Else
If Not obGroupYes Then
If nParam = 1 Then
sSQLSelect = adoRecordset!Name
Else
sSQLSelect = sSQLSelect & "," &
adoRecordset!Name
End If
End If
End If
End If
Next nParam
Else
If obGroupYes Then
sSQLNumbers = sSQLNumbers & ",sum(" & adoRecordset!Name &
")"
Else
sSQLNumbers = sSQLNumbers & "," & adoRecordset!Name
End If
End If