By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,486 Members | 1,692 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,486 IT Pros & Developers. It's quick & easy.

View only selected fields

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...
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

Thanks James. I'll give it a try. I had a look through this NG before
I posted, but couldn't find a solution

Dave
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.