Report field(s) selection.

[pekajo]

Hi,

SOLVER please see solution below.

Can you help.
I would like to be able to select any fields from a table to print on a report at time of printing.
Was think of a popup with tick boxes but not to sure where to go from there.
Peter

 

[Deleted member 147267]

You should be basing a report on a query that contains the fields you need from that table (if not the table itself). It almost sounds like you are saying you want to decide which fields you want on a report just before you decide to open it. That's practically impossible, especially if the number of fields would vary from one time to another.

 

[The_Doc_Man]

First, I concur with Micron. But second, I don't think you realize the size of the can of worms you are opening with that stated goal.

The problem is that to dynamically create or alter a report, you have to consider data sources. You build a report with specific sources in mind and then either use a report wizard or hand-build what you want to see. Access statically links those fields to specific controls. Then you get to adjust sizing and such so that you can determine what looks good on a report. You can't forget that reports are useful if and only if they present data in a way that the user considers useful.

To dynamically select even ONE field, you are adjusting a .ControlSource property and you HOPE that the adjustment will fit - because of course each field potentially has different sizes. But then you talk about selecting ANY fields from a table. Now you have to consider sizing and formatting for each, because you potentially deal with different data representation sizes between numeric and text fields. And doing by automation, you have NO idea how it will look. You have to decide in which order to present the fields (i.e. which one is left-most, which one is next to it, etc), and I can tell you that the odds are that you will have some issues with whatever order those items are selected.

But then... what about the issues of ordering or grouping? If you select a handful of fields, you might end up with something that looks awfully much like a randomly ordered spreadsheet that hasn't even been sorted yet. To decide if you should do grouping, you would have to explore the various defined relationships involving your table to see if one of the selected fields is involved in a one-many relationship (implying a grouping case). You would have to look at the field sources to see if an index was supplied (implying a sort order case).

Now, to be fair, yes it is possible. But to be honest, no it is not easy. I'm a user of Access since version 2.0 that was out 20+ years ago, I have been a Microsoft Most Valued Professional in the past, I have gotten praise for some of my work, and I wouldn't touch this puppy with a 10-foot pole. Because of the field-size issues and aesthetic issues, this would be a monster of a project.

 

[plog]

I would like to be able to select any fields from a table to print on a report at time of printing.
Was think of a popup with tick boxes but not to sure where to go from there.

Ribbon->Create->Report Wizard

 

[The_Doc_Man]

Of course, Paul, the contrary issue is that we always tell folks that if they want to have control over their app, they need to hide the ribbon and the navigation panel. (Or whatever they call that in newer versions.)

 

[Cronk]

Alternatively, export all fields to a spreadsheet and let the user delete unwanted columns.

In some situations (where the user had plenty of money to pay for the time involved), I have provided a customized form (like the wizard) to select the fields, change the order and sort, then generate the appropriate query in code, export the data, format the columns, and add sort buttons to the top of the spreadsheet columns. This definitely not a trivial exercise.

 

[pekajo]

Hi,
I have a solution that works for me and was limited to 5 fields but can increase of required.
I created a table for selection of fields
FieldDesc
FieldName
FieldType
FieldSelectNo

I then Created a popup where they place a 1 to 5 in the FieldSelectNo

Using the dlookup on the FieldSelectno I can get the FieldType.
With this I create a table with right data types but always called the field names Blank1 to Blank5,
I used the SELECT function to build the table if they needed less then 5

vFt1 = DLookup("[FieldType]", "ReportSelect", "[FieldSelectNo] = 1")
vFt2 = DLookup("[FieldType]", "ReportSelect", "[FieldSelectNo] = 2")
Vft3 = DLookup("[FieldType]", "ReportSelect", "[FieldSelectNo] = 3")
vFt4 = DLookup("[FieldType]", "ReportSelect", "[FieldSelectNo] = 4")
vFt5 = DLookup("[FieldType]", "ReportSelect", "[FieldSelectNo] = 5")


'******************************** Create new table '******************************************************************
On Error Resume Next
DoCmd.DeleteObject acQuery, "AAAANew"
'**********************************************************

Dim strSQL As String
Set db = DBEngine(0)(0)
strSQL = "CREATE TABLE [AAAANew] (Surname Text, Given Text, Volunteer Text, Blank1 " & vFt1 & ",Blank2 " & vFt2 & ", Blank3 " & Vft3 & ", Blank4 " & vFt4 & ", Blank5 " & vFt5 & ");"
db.Execute strSQL
Set db = Nothing

Then I created a INSERT query.

'**********************************************************
vFn1 = DLookup("[FieldName]", "ReportSelect", "[FieldSelectNo] = 1")
vFn2 = DLookup("[FieldName]", "ReportSelect", "[FieldSelectNo] = 2")
vFn3 = DLookup("[FieldName]", "ReportSelect", "[FieldSelectNo] = 3")
vFn4 = DLookup("[FieldName]", "ReportSelect", "[FieldSelectNo] = 4")
vFn5 = DLookup("[FieldName]", "ReportSelect", "[FieldSelectNo] = 5")


Set db = CurrentDb
Dim qryDef As DAO.QueryDef
Dim selectSQL As String

On Error Resume Next
DoCmd.DeleteObject acQuery, "Q_AAAANew"
'**********************************************************

selectSQL = "INSERT INTO AAAANew ( Surname, Given, Blank1, Blank2, Blank3, Blank4, Blank5 ) " & _
"SELECT Volunteers.Surname, Volunteers.Given, Volunteers.Vol, Volunteers." & vFn1 & ", Volunteers." & vFn2 & ", Volunteers.DatewExp, Volunteers.Mobile " & _
"FROM Volunteers;"

The only issue is that the report field widths are all the same, in my case it was not an issue as the fields were either a data or yes/no.

Hope someone can expand on this as I am a very much a 'cut and paste' programmer.

Regards
Peter

 

[MajP]

I think you can get a pretty workable solution.
1. Create a table that has these fields
tblFieldInfo
fieldName ' real name
fieldLabel ' nice looking name instead of dtmDOB just DOB or First Name instead of strFirstName
fieldWidth ' How big you want it. That you will have to play with
2. Have a form where they can pick Fields. I have solutions to do this, that maybe a cut and paste person can use. The trick is each time you add a field you need to determine the max width is not exceeded. I would use one of my From To classes where you select from one listbox and move to the second.
3. configure a set amount of textboxes and labels with naming like txt1, txt2... lbl1, lbl2 ...
4. you can at runtime dynamically size, locate, and set the rowsource. You hide any unused textboxes. I would not use dlookup. Simply bind the textboxes. The code is based on the above table.

How much time you have to do this? This would likely take me an hour or two, but if you are not vba savy you have a few days of work.

I am assuming here this is for a hardcopy. If not there are far better dynamic solutions using a datasheet.

 

[pekajo]

Hi,

Like the idea of combining the text box's on the report.
There is always an easier way.