Control Source

[nsfred2001]

I am a newbie, and am having trouble making a report work correctly.
I have a query (query1) that displays about 20 columns of numbers.
Col1 Col2 Col3 Col4 etc
I would like to develop a single report based on query1 where the user can
select the name of the column to display on the report, without having to
write 20 different reports.
Your help will be appreciated.
Using MS Access XP

 

[Klatuu]

First order of business. query1 tells nobody what this query is about. Give
it a meaningful name so others (and you 3 months from now) can guess at what
it is. Same goes for Col1, Col2, etc.

Now, how many columns will the user be able to select? There are a lot of
ways to do this.

 

[nsfred2001]

Thanks for replying so quickly.

The query name is actually "FreeForm Query". This is a query based on a
table with imported data from a Unix report. The column headings vary by
what the users have selected from a Unix proprietary program. They may be
called Price1, Price2, Price3, etc.

The end user is trying to migrate their data from Unix to Windows, so that
they may use Windows functionality. (Including transferring this data to a
PDA). This will be facilitated by using the import features of Access.

I have successfully imported the data into tables, and written the query to
eliminate extraneous data, but am having difficulty in selecting the proper
columns at runtime.

The question is really how is a column selected by the user at runtime?
Without coding a separate report for each instance that can be selected.

The user will be able to select upto 5 columns, this is numeric data in the
format of dollars and cents.

 

[Klatuu]

This is not a simple task, but not rocket science either, I assume you will
have a way of knowing which columns the user has selected. You will have to
keep track of that. I don't know what your plan is for allowing the user to
select fields, but you may want to consider a multiselect list box. Using
this technique, you will have an easy way of know which columns the user has
selected.

To do this, create your list box, in the RowSourceType property, select
Field List and in the RowSource property enter the name of the table or query
you will be selecting fields from.

Now you will need to construct the SQL string to use for the report. You
will use the selections in the list box to do that. This code is not tested,
but was modified from something similar that does work

Private Function BuildSQL(strControl As String) As String
Const conTblName = "MyTableName."
Dim varItem As Variant
Dim strSQL As String
Dim ctl As Control
Dim lngCtr as Long

Set ctl = Me.MyListBoxName

If ctl.ItemsSelected.Count = 0 Then
MsgBox "No Columns Selected"
strSQL = ""
Else
lngCtr = 1
strSQL = "SELECT "
With ctl
For Each varItem In .ItemsSelected
strSQL = strSQL & conTblName & .ItemData(varItem) & " _
As Col" & Cstr(lngCtr) & ", "
lngCtr = lngCtr + 1
Next varItem
End With

'So the report doesnt blow of for lack of column names, we need to dummy up
' anything left over
For lngCtr = lngCtr To 5
strSQL = strSQL & "Null As Col" & Cstr(lngCtr) & ", "
Next lngCtr

'Take off the last comman and add the table name
strSQL = Left(strSQL, Len(strWhere) - 2) & " FROM " & conTblName
'Take the period off the end and add the ;
strSQL = Left(strSQL, Len(strWhere) - 1) & ";"


BuildSQL = strSQL
set ctl = nothing

End Function

Now you have an SQL string to support your report. Note that each field in
the query will be named Col1, Col2, Col3, etc. Bad naming, but since it is
temporary, it doesn't matter and we need it to be generic. You will want to
name your control sources in you the same.

Next, we need to make it into a Saved Query so the report can find it. We
can do that by creating a QueryDef using the sql string

The following code should be in the Click event of a command button to
produce the report.

Dim qdfs As QueryDefs
Dim qdf As QueryDef
Dim strSQL as String

'Delete the old query
For Each qdf In qdfs
If qdf.Name = "_BPOTemp" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf

'Create the Query
strSQL = BuildSQL()
'Save it
Set qdf = dbf.CreateQueryDef("zzReport", strSQL)
'Now Do your report Here

I hope I haven't left anything out. If you have questions, please post back.

 

[nsfred2001]

Thanks for the quick response, again.
I will try your suggestion, and post back when I have a chance to test it.