Can users custom their own report in Access???

[Vimin2j]

I had my database of employee's info with forms and reports as requested. Now, a user ask me to create a report which he's able to customize his own report by selecting specific fields for each different reports.

For more specific, the report I already had including all info about an employee as name, employeeID, date hired, position held, personal info, business opportunity...etc.... The user wants a custom report for his different choices,
such as name and personal info only
or name, employeeID, and business oportunity
or employeeID, date hired, and position held....

Could anybody please help me with this. Thank you

 

[BobJacksonNCI]

If you allow anyone into the database in design mode, the person may be able to change ANYTHING ...

If I had to deal with the situation, I'd create a form and query combination that allowed various selections, like date ranges, department numbers, etc. But the query would extract all fields that might be of interest. Then, in On Click event code for a button, I'd run the query and export the result to Excel. The User could then save the fields needed and massage vigorously to heart's content without risk to the database.

HTH,
Bob

 

[dhookom]

Consider importing the forms and tables from the Query By Form applet at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[BSman]

I have a large data warehouse (actually uses a SQL back end because the data is about 4 gig) with an Access front end. I've taken the approach of giving users the ability to build their own queries using an ad hoc query builder form I wrote that is driven by tables that list the data tables, fields, descriptive names to display to users, type of data, combo box source table info if appropriate, how each field is used in a grouping query if appropriate (ex: sum, count), etc. When the users have specified their selection criteria and their sorting query (also used as the group by criteria to build a grouping query), they then click on one of two buttons on the form to either run the select query or the grouping query that is created (behind the scenes) based on their criteria. The results are displayed in the usual spreadsheet type of display. If they want to create reports they can copy the results over to Excel and play with it to make whatever they want for a report. (Also, when they close the ad hoc query builder form the selection (where) criteria and sorting (order by) criteria are passed to the next form/report and can be used there for the filter and order by properties.)

I realize that this is not the same as a user creating their own Access report, but it keeps them in a menu drive mode.

The alternate is to create a second front end application that only links to specific tables and only in a read only mode so the user cannot change any data. This is easy to do with a SQL back end, but messy for an Access back end database.

By the way, I've used this query builder, which was originally written for use with an Access back end, but has been expanded and rewritten to work with a SQL back end (for which it creates a pass through query).

 

[Vimin2j]

Thanks for all responses!
Bob, could you please be more specific step by step on your solution. This is my first time dealing with dB in MS Access, so I'm not really clear.
Thanks

Vimin

 

[BSman]

The most important question before you continue is if the spreadsheet type of display will satisfy your users. (If you open a table or query in Access you get what I am calling the spreadsheet display.)

While the query builder form I mention is difficult to build, if your users won't be happy with "just" custom spreadsheets of the data, then I would not pursure that any further.

 

[dhookom]

The link in my reply has a demo MDB that you can download and try. There are several tables, forms, and a report that can be imported into any front-end mdb (tables should be pushed to the back-end). Then, the developer need only create a few master queries of combined tables and possibly aliased field/column names.

It should take less than an hour to implement this. The end result is a custom query builder that saves the full SQL. The data results are displayed in a subform (datasheet) with buttons to send the results to several different file formats including Excel, CSV, Word Merge,...or even the printer.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Vimin2j]

I'm still working on it.
Thanks again for any advise

Vimin

 

[Vimin2j]

Duane,
I downloaded some zip.file from the link you posted. There is problem running the report.
It said, Complie error: Cannot file project ot library
And here is the error code"

=============================
Private Sub cmdRunReport_Click()
On Error GoTo Err_cmdRunReport_Click
Dim MyDB As DAO.Database
Dim qdf As QueryDef
Dim rst As DAO.Recordset, rst2 As DAO.Recordset
Dim i As Integer, j As Integer, k As Integer, strSQL As String
Dim strFieldList As String, strIN As String
Dim flgAll As Boolean

Set MyDB = CurrentDb()
Set rst = MyDB.OpenRecordset("tablefields")

strSQL = "SELECT "
j = 0
k = 0
rst.MoveFirst
'create the IN string by looping thru the listbox
For i = 0 To lstLocalAuthority.ListCount - 1
If lstLocalAuthority.Selected(i) Then
strIN = strIN & "[" & lstLocalAuthority.Column(0, i) & "] as Field" & k & ","
rst.Edit
rst!indexx = k
rst.Update
rst.MoveNext
k = k + 1
Else
rst.Edit
rst!indexx = Null
rst.Update
rst.MoveNext
End If
j = j + 1
Next i
For i = k To lstLocalAuthority.ListCount - 1
strIN = strIN & "null as Field" & i & ","
Next i
' stripoff the last comma of the IN string
strFieldList = Left (strIN, Len(strIN) - 1)

strSQL = strSQL & strFieldList & " FROM " & Me!cboTable
'MsgBox strSQL

MyDB.QueryDefs.Delete "qryLocalAuthority"
Set qdf = MyDB.CreateQueryDef("qryLocalAuthority", strSQL)

DoCmd.OpenReport "qryLocalAuthority", acPreview
============================
Please explain. Thank u in advance

Vimin

 

[dhookom]

This code requires that you have a reference set to the Microsoft DAO Object Library. Open any module and select Tools|References and scroll down to and check this library.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.