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

 

[James A. Fortune]

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

See (multipost):

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/b8ef401bed9c3081

or

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/e5f9ed5253e232c1

although that only covers fields being selected from a single table
where all the fields are already on the report. Perhaps for your 0-4
situation you can change the Control Sources dynamically in the
Report_Open code and hide the rest of the Detail controls but I have not
tried to see if the Control Sources can be changed there. Changing the
position and visibility of report controls works well from that event.

James A. Fortune
(e-mail address removed)