Reports - Access 2007

**AlfBar** · 09-29-2014, 11:33 AM

Hello, I am pretty new to Access. I have used databases before and even created some very basic ones many years ago, but new to relational databases. I have been trying to learn the basic concepts of Access for the following task:

I have an Access Database to store information about a project which consists in the Volunteers details. These are the fields in the tblVolunteers:

Role, Volunteer Name, Address, Telephone, Mobile, Email, Borough, Age, Gender, Sexual Orientation, Employment, Disability, Religion, Ethnic Background, Language, Previous Volunteering, Aspirations, Worker Name and 8 Actions.

I would produce an Access Report based on user selection. The user selects the field(s) that they would like to report to. Is this possible?

Thanks in advance

AlfBar

**June7** · 09-29-2014, 11:49 AM

Not without a lot of VBA code.

Build various reports and offer users choice to run.

Sexual orientation is relevant data - really?

**ranman256** · 09-29-2014, 11:55 AM

Yes. You 1st build a QUERY to get the info you want.
Then make a report off this, and produce the sort order, sums, etc.

For user selections...build a FORM with the text boxes or combo boxes for users to select.
Then the query in the report would read these.

But some VB may be needed...on the REPORT button click event, you would have to determine the filters needed...
If you need complex form filters THEN make a query from it, do this:

Code:

Public Sub btnReport_Click()
Dim sSql As String, sWhere As String
Dim qdf As querydef
Const kQRY = "qsFormFilter"    'here is the query we use ...in the report too

    'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value
    'remove 1st And
sWhere = Mid(sWhere, 4)
Set qdf = currentdb.querydefs(kQRY)
qdf.Sql = "SELECT * FROM tblCompany"
If sWhere <> "" Then qdf.Sql = qdf.Sql & " WHERE " & sWhere
qdf.Close

'open the query or report here!
'DOCMD.openquery kQRY
'DOCMD.OPENREPORT "rMyReport"
End Sub

**AlfBar** · 09-30-2014, 03:30 AM

Originally Posted by June7

Not without a lot of VBA code.

Build various reports and offer users choice to run.

Sexual orientation is relevant data - really?

Thank you for the quick reply. I am going to try it and let you know the outcome.

Cheers
AlfBar

**AlfBar** · 09-30-2014, 03:59 AM

Originally Posted by AlfBar

Thank you for the quick reply. I am going to try it and let you know the outcome.

Cheers
AlfBar

Hello,

I tried the code, but an error occour - Run Time - Error 424 - Object Required

Here is the code:
Private Sub btnReport_Click()
Dim sSql As String, sWhere As String
Dim qdf As QueryDef
Const kQRY = "qsFormFilter" 'here is the query we use ...in the report too
'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If Not IsNull(txtName) Then sWhere = sWhere & " and [Name]='" & txtName & "'"
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value
'remove 1st And
sWhere = Mid(sWhere, 4)
Set qdf = CurrentDb.QueryDefs(kQRY)
qdf.SQL = "SELECT * FROM tblCompany"

If sWhere <> "" Then qdf.SQL = qdf.SQL & " WHERE " & sWhere
qdf.Close
'open the query or report here!
'DoCmd.OpenQuery kQRY
'DOCMD.OPENREPORT "rMyReport"

End Sub

************
The red bit it is where the error occours.

Thanks
AlfBar

**June7** · 09-30-2014, 11:43 AM

Not sure why that line errors. Is Contact a yes/no type field and chkContact is a checkbox? Do you want to filter by Contact only if the checkbox is true? Try:

If chkContact = True Then sWhere = sWhere & " AND [Contact]=" & chkContact

Suggest removing the code referencing QueryDef. Don't open query. Don't modify query def. Just apply filter string to report. Uncomment the OpenReport line and use your actual report name.

DoCmd.OpenReport "actual report name here", , , sWhere

**AlfBar** · 10-01-2014, 01:40 PM

Originally Posted by AlfBar

Thank you for the quick reply. I am going to try it and let you know the outcome.

Cheers
AlfBar

Hi ranman56,

Thank you for the code that you provided. I have some questions: what is cboState, txtName and chkContact?

The does not work. It comes up with an error Run -Time Error 424 - Object Required in the following line:
If Not IsNull(chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value

Thank you but I need some help
AlfBar

**June7** · 10-01-2014, 01:44 PM

Those are names of combobox, textbox, checkbox - used as examples.

You need to modify the code to use your field and control names. Adapt.

Did you review post 6?

**AlfBar** · 10-01-2014, 02:21 PM

Originally Posted by June7

Those are names of combobox, textbox, checkbox - used as examples.

You need to modify the code to use your field and control names. Adapt.

Did you review post 6?

Hi June7,

Thanks for your reply. Yes I reviewed post 6 and I took it in consideration.

AlfBar

**June7** · 10-01-2014, 03:01 PM

So did you modify code as appropriate for your db?

**AlfBar** · 10-02-2014, 01:42 PM

Originally Posted by June7

So did you modify code as appropriate for your db?

Hi June7,

I adapted the code to match my database. Here is the code:
Public Sub btnReport_Click()
Dim sSql As String, sWhere As String
Dim qdf As QueryDef
Const kQRY = "qsFormFilter" 'here is the query we use ...in the report too
'the query is built depending on the various filters the user picks...

If Not IsNull(Check13) Then sWhere = sWhere & " and [Role]=" & Check13.Value
If Not IsNull(Check11) Then sWhere = sWhere & " and [VolName]=" & Check11.Value
If Not IsNull(Check9) Then sWhere = sWhere & " and [VolAddress]=" & Check9.Value
'If Not IsNull(Check7) Then sWhere = sWhere & " and [Telephone]=" & Check7.Value
If Not IsNull(Check19) Then sWhere = sWhere & " and [VolMobile]=" & Check19.Value
If Not IsNull(Check15) Then sWhere = sWhere & " and [VolMail]=" & Check15.Value
If Not IsNull(Check21) Then sWhere = sWhere & " and [VolBorough]=" & Check21.Value
If Not IsNull(Check23) Then sWhere = sWhere & " and [VolAge]=" & Check23.Value
If Not IsNull(Check25) Then sWhere = sWhere & " and [VolGender]=" & Check25.Value
If Not IsNull(Check27) Then sWhere = sWhere & " and [VolSexsualOrientation]=" & Check27.Value
If Not IsNull(Check29) Then sWhere = sWhere & " and [VolEmployment]=" & Check29.Value
If Not IsNull(Check31) Then sWhere = sWhere & " and [VolDisability]=" & Check31.Value
If Not IsNull(Check33) Then sWhere = sWhere & " and [VolReligion]=" & Check33.Value
If Not IsNull(Check35) Then sWhere = sWhere & " and [VolEthnicBackground]=" & Check35.Value
If Not IsNull(Check37) Then sWhere = sWhere & " and [VolLanguage]=" & Check37.Value
If Not IsNull(Check39) Then sWhere = sWhere & " and [VolPeviousVolunteering]=" & Check39.Value
If Not IsNull(Check43) Then sWhere = sWhere & " and [VolAspirations]=" & Check43.Value
If Not IsNull(Check45) Then sWhere = sWhere & " and [Workername]=" & Check45.Value
If Not IsNull(Check47) Then sWhere = sWhere & " and [VolActions]=" & Check47.Value
'remove 1st And
sWhere = Mid(sWhere, 4)
Set qdf = CurrentDb.QueryDefs(kQRY)
qdf.SQL = "SELECT * FROM tblVolunteer"
If sWhere <> "" Then qdf.SQL = qdf.SQL & " WHERE " & sWhere
qdf.Close
'open the query or report here!
'DoCmd.OpenQuery kQRY
DoCmd.OpenReport "Report1"
End Sub

I have a Run-Time Error 3265 - Item not found in this collection.
The line that is the culprit is:

Set qdf = CurrentDb.QueryDefs(kQRY)

The values between the square brackets [], are the names of the fields in tblVolunteer?

Thanks in advance
AlfBar

**June7** · 10-02-2014, 01:44 PM

See third line of post 6.

The controls are all checkboxes? The fields are what data type? Why would you apply a True/False parameter to an Address, Telephone, Gender, etc. field?

**AlfBar** · 10-02-2014, 02:02 PM

Yes, all controls are checkboxes. They are presented to users for them to choose which fields they want to include in the report. The fields are all the Text data type.

**June7** · 10-02-2014, 02:10 PM

Okay, I was confused. I thought you were building filter criteria string.

But how will this work with report? The report is not dynamic as far as the textboxes and the fields they are bound to.

**AlfBar** · 10-02-2014, 02:29 PM

Okay, let me explain.
I have a database that records volunteers details. I want to be able to create a report, based on a query that allows the user to be able to report on fields from tblVolunteer which they would like to report.
I presented one form to the users with checkboxes for each field to allow them to select (or not) accordingly the requirement.

Is this possible to do it?

Reports - Access 2007

Thread Tools

Reports - Access 2007

Reports - Accesss 2007

Reports - Access 2007

Similar Threads

Access 2007: Using new TrueType font in forms/reports.

2010 and 2007 Access Queries, Forms & Reports

create forms and reports in microsoft access 2007 help

Access 2007 Reports Based on ID

Cannot edit reports in Access 2007

Posting Permissions