View only selected fields

[Dave]

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

[James Fortune]

Re: View only selected fields

warsteiner77@ho tmail.com (Dave) wrote in message news:<7eb55305. 0406080632.3a16 975d@posting.go ogle.com>...[color=blue]
> 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[/color]

I use a separate form for the user to specify the field order and
custom headings in tblFlexReportCo lumnOrder. Once the column order
has been specified, use the SQL string to get just the field names
that are actually used.

tblFlexReportCo lumnOrder:
ColumnName Text
ListOrderNumber Long
ReportColumnNam e Text
ValsIndex Long
FieldType Text
MaxWidth Long
ActualWidth Long

The code is placed in Sub Report_Open

strSQL = "SELECT * FROM tblFlexReportCo lumnOrder 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(lng I) = "txt" & ColumnName(lngI )
TotalBoxName(ln gI) = "txtTotal" & ColumnName(lngI )

I only show the textboxes that are actually used
Report_rptFlexL egal.Controls(L abelName(lngI)) .Properties("Ca ption")
= ReportColumnNam e(lngI)
Report_rptFlexL egal.Controls(L abelName(lngI)) .Properties("Vi sible")
= True
Report_rptFlexL egal.Controls(T extBoxName(lngI )).Properties(" Visible")
= True
Report_rptFlexL egal.Controls(T otalBoxName(lng I)).Properties( "Visible")
= True

I also size them and slide them over (note: sizes are in twips--1440
per inch)
Report_rptFlexL egal.Controls(T extBoxName(lngJ )).Properties(" Width")
= Int(FieldWidth( lngJ))
Report_rptFlexL egal.Controls(L abelName(lngJ)) .Properties("Wi dth") =
Int(FieldWidth( lngJ))
Report_rptFlexL egal.Controls(T otalBoxName(lng J)).Properties( "Width")
= Int(FieldWidth( lngJ))
Report_rptFlexL egal.Controls(T extBoxName(lngJ )).Properties(" Left")
= LeftNumber(lngJ )
Report_rptFlexL egal.Controls(L abelName(lngJ)) .Properties("Le ft") =
LeftNumber(lngJ )
Report_rptFlexL egal.Controls(T otalBoxName(lng J)).Properties( "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

[Dave]

Re: View only selected fields

jafortun@oaklan d.edu (James Fortune) wrote in message news:<a6ed3ce7. 0406081209.178c 936f@posting.go ogle.com>...[color=blue]
> warsteiner77@ho tmail.com (Dave) wrote in message news:<7eb55305. 0406080632.3a16 975d@posting.go ogle.com>...[color=green]
> > 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[/color]
>
> I use a separate form for the user to specify the field order and
> custom headings in tblFlexReportCo lumnOrder. Once the column order
> has been specified, use the SQL string to get just the field names
> that are actually used.
>
> tblFlexReportCo lumnOrder:
> ColumnName Text
> ListOrderNumber Long
> ReportColumnNam e Text
> ValsIndex Long
> FieldType Text
> MaxWidth Long
> ActualWidth Long
>
> The code is placed in Sub Report_Open
>
> strSQL = "SELECT * FROM tblFlexReportCo lumnOrder 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(lng I) = "txt" & ColumnName(lngI )
> TotalBoxName(ln gI) = "txtTotal" & ColumnName(lngI )
>
> I only show the textboxes that are actually used
> Report_rptFlexL egal.Controls(L abelName(lngI)) .Properties("Ca ption")
> = ReportColumnNam e(lngI)
> Report_rptFlexL egal.Controls(L abelName(lngI)) .Properties("Vi sible")
> = True
> Report_rptFlexL egal.Controls(T extBoxName(lngI )).Properties(" Visible")
> = True
> Report_rptFlexL egal.Controls(T otalBoxName(lng I)).Properties( "Visible")
> = True
>
> I also size them and slide them over (note: sizes are in twips--1440
> per inch)
> Report_rptFlexL egal.Controls(T extBoxName(lngJ )).Properties(" Width")
> = Int(FieldWidth( lngJ))
> Report_rptFlexL egal.Controls(L abelName(lngJ)) .Properties("Wi dth") =
> Int(FieldWidth( lngJ))
> Report_rptFlexL egal.Controls(T otalBoxName(lng J)).Properties( "Width")
> = Int(FieldWidth( lngJ))
> Report_rptFlexL egal.Controls(T extBoxName(lngJ )).Properties(" Left")
> = LeftNumber(lngJ )
> Report_rptFlexL egal.Controls(L abelName(lngJ)) .Properties("Le ft") =
> LeftNumber(lngJ )
> Report_rptFlexL egal.Controls(T otalBoxName(lng J)).Properties( "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[/color]


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