wa**********@hotmail.com (Dave) wrote in message news:<7e**************************@posting.google. com>...
Hi
I'm creating a fairly simple Staff database using Access 97.
There is a table, which stores all of the staff details (initials,
firstname, surname, tel. no. etc.) and I want to design a dynamic
reporting system whereby users can select which fields they want to
report on. i.e. users select surname and tel. no. and these details
are displayed in a report for them.
I created a form with a multi-select listbox, which lists the field
names (initials, firstname, surname, tel. no. etc.) and the selected
values arestored in a table.
My problem is getting the report to only disply the required fields. I
*think* I may need some sort of IIF statement within a query, but I'm
not too sure.
Any advice greatly received.
Dave
I use a separate form for the user to specify the field order and
custom headings in tblFlexReportColumnOrder. Once the column order
has been specified, use the SQL string to get just the field names
that are actually used.
tblFlexReportColumnOrder:
ColumnName Text
ListOrderNumber Long
ReportColumnName Text
ValsIndex Long
FieldType Text
MaxWidth Long
ActualWidth Long
The code is placed in Sub Report_Open
strSQL = "SELECT * FROM tblFlexReportColumnOrder WHERE "
strSQL = strSQL & "[ListOrderNumber] > 0 "
strSQL = strSQL & "ORDER BY ListOrderNumber;"
I use field names as part of the names of the textboxes on the report
LabelName(lngI) = "lbl" & ColumnName(lngI)
TextBoxName(lngI) = "txt" & ColumnName(lngI)
TotalBoxName(lngI) = "txtTotal" & ColumnName(lngI)
I only show the textboxes that are actually used
Report_rptFlexLegal.Controls(LabelName(lngI)).Prop erties("Caption")
= ReportColumnName(lngI)
Report_rptFlexLegal.Controls(LabelName(lngI)).Prop erties("Visible")
= True
Report_rptFlexLegal.Controls(TextBoxName(lngI)).Pr operties("Visible")
= True
Report_rptFlexLegal.Controls(TotalBoxName(lngI)).P roperties("Visible")
= True
I also size them and slide them over (note: sizes are in twips--1440
per inch)
Report_rptFlexLegal.Controls(TextBoxName(lngJ)).Pr operties("Width")
= Int(FieldWidth(lngJ))
Report_rptFlexLegal.Controls(LabelName(lngJ)).Prop erties("Width") =
Int(FieldWidth(lngJ))
Report_rptFlexLegal.Controls(TotalBoxName(lngJ)).P roperties("Width")
= Int(FieldWidth(lngJ))
Report_rptFlexLegal.Controls(TextBoxName(lngJ)).Pr operties("Left")
= LeftNumber(lngJ)
Report_rptFlexLegal.Controls(LabelName(lngJ)).Prop erties("Left") =
LeftNumber(lngJ)
Report_rptFlexLegal.Controls(TotalBoxName(lngJ)).P roperties("Left")
= LeftNumber(lngJ)
Check for older postings in this NG since other people have come up
with other ways of doing this.
James A. Fortune
