Report Generator

[Intrepid_Yellow]

Hi,

I have created a basic report genertor, the user can choose a table
(from
cboTable), then fields from that table (from lstTableFields) and the
select a
specific value from one of those fields (select field from
cboFieldName1 then
a value from lstFieldValues1).

Everything works well. So now the issue... I have added a
cboFieldName2, 3 &
4 and a lstFieldValues2, 3 & 4. What is the best way to rework the code
below
so that the report reflects all conditions the user selects (anywhere
from
0-4)???

>>>>>>>

' creates the -WhereIN- string by looping thru the listbox
For i = 0 To lstFieldValues1.ListCount - 1
If lstFieldValues1.Selected(i) Then
If lstFieldValues1.Column(0, i) = "All" Then
flgAll = True
End If

' checks data type of field for delimiting
If cboFieldName1.Column(1) >= 1 And cboFieldName1.Column(1)
<= 7 Then
' if the field holds numeric data
strWhereIN = strWhereIN & " " &
lstFieldValues1.Column(0, i) & ","
ElseIf cboFieldName1.Column(1) = 8 Then
' if the field is a date
strWhereIN = strWhereIN & "#" &
lstFieldValues1.Column(0, i) & "#,"
ElseIf cboFieldName1.Column(1) = 10 Then
'if the field is text
strWhereIN = strWhereIN & "'" &
lstFieldValues1.Column(0, i) & "',"
End If
End If
Next i
' creates the -WHERE- string, stripping off the last comma of the
-IN- string
strWhere = " WHERE " & Me!cboFieldName1 & " in (" &
Left(strWhereIN, Len(strWhereIN) - 1) & ")"
'MsgBox strWhere
' if "All" was selected then don't add the -WHERE- condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If
'MsgBox strSQL

>>>>>>>

All replies are greatly appreciated

[Tom van Stiphout]

On 17 Jan 2007 18:27:56 -0800, "Intrepid_Yellow"
<ca*********@hotmail.comwrote:

One thing to realize is that you can create control names at runtime
rather than at (code) design time:
for i = 0 to 4
debug.print Me.Controls("cboFieldName" & i).Value
next i

-Tom.

>Hi,

I have created a basic report genertor, the user can choose a table
(from
cboTable), then fields from that table (from lstTableFields) and the
select a
specific value from one of those fields (select field from
cboFieldName1 then
a value from lstFieldValues1).

Everything works well. So now the issue... I have added a
cboFieldName2, 3 &
4 and a lstFieldValues2, 3 & 4. What is the best way to rework the code
below
so that the report reflects all conditions the user selects (anywhere
from
0-4)???

>>>>>>>>

' creates the -WhereIN- string by looping thru the listbox
For i = 0 To lstFieldValues1.ListCount - 1
If lstFieldValues1.Selected(i) Then
If lstFieldValues1.Column(0, i) = "All" Then
flgAll = True
End If

' checks data type of field for delimiting
If cboFieldName1.Column(1) >= 1 And cboFieldName1.Column(1)
<= 7 Then
' if the field holds numeric data
strWhereIN = strWhereIN & " " &
lstFieldValues1.Column(0, i) & ","
ElseIf cboFieldName1.Column(1) = 8 Then
' if the field is a date
strWhereIN = strWhereIN & "#" &
lstFieldValues1.Column(0, i) & "#,"
ElseIf cboFieldName1.Column(1) = 10 Then
'if the field is text
strWhereIN = strWhereIN & "'" &
lstFieldValues1.Column(0, i) & "',"
End If
End If
Next i
' creates the -WHERE- string, stripping off the last comma of the
-IN- string
strWhere = " WHERE " & Me!cboFieldName1 & " in (" &
Left(strWhereIN, Len(strWhereIN) - 1) & ")"
'MsgBox strWhere
' if "All" was selected then don't add the -WHERE- condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If
'MsgBox strSQL

>>>>>>>>

All replies are greatly appreciated

[Intrepid_Yellow]

Hi Tom,

Thanks for your reply but unfortunately that doesn't help me.

Ta.