You are here:

Using MS Access/Running a specialized report/query from a form

Advertisement


Question
QUESTION: I think I have all of the parts I need for this process but I cannot figure out how to put it all together.

I have a query called IndReportQuery which has all the fields I need and is run off a table of our entire database.  I created a report called WeeklyData Detailed that I would like to use as a template which shows all the same fields but is set up to just show last week's data for every single employee.

What I want to do now is let the employees come to a form where they select their own name, which dates they want to use (DateReceived and Date Taken) and just see their own work.  If they want to use everything that was Taken on Friday and don't care about Received, then they should just be able to put in the one date.  This will be open so any one of the employees can pull up anyone's reports but that's fine.  I just need to give them some control over their stats.

Do I put a drop down on the form?  Does that run a query or is there a standard query I can use?  I'm kinda lost and need to complete this part in the next four days before a two week vacation.  Can you point me in the right direction?

ANSWER: You can do this in two ways. Either supply criteria to a query and/or filter the report. In either case, you create a form with a combobox and 2 textboxes and a button. The combobox will allow the user to select an employee. The textboxes the date range and the button launch the report.

I would actually use a combination of the methods. I would name the 2 textboxes; txtStart and txtEnd. I would set the default values as follows:

txtStart: DMin("[datefield]","tablename")
txtEnd: DMax("[datefield]","tablename")

This way, when the form is open the two are set to the earliest and latest dates so, if the user doesn't change them, they will get all records. I would then add the following criteria to the query's date field:

BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd

This will filter the query just for the dates selected (again, if they don't select any dates, they get all the records.

The combobox will return the selected Employee's ID. Or it can be left blank for all employees.

The button will kick off the report and will use code like the following:

Dim strCriteria As String

If IsNull(Me.cboEmployee) Then
   strCriteria = ""
Else
   strCriteria = "[EmployeeID] = " & Me.cboEmployee
End If

DoCmd.OpenReport "reportname", acPreview,,strCriteria



So when the button is pressed the report will preview filtered for the selected dates and selected employee(s).

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA


---------- FOLLOW-UP ----------

QUESTION: Thank you so much for responding so quickly, I really appreciate that!

I spent a good part of yesterday putting this in place and testing and I keep coming up with the same error message; "The specified field '[Date Taken]' could refer to more than one table listed in the FROM clause of your SQL statement."  I have two date fields, 'Date Taken' and 'Date Received' and I added the BETWEEN command to both of those fields on the query.

When the report displays the found data, it displays the Date Taken and Date Received fields but did I just mess those up by adding the Between?  Do you know why this happens and how I can change it so my report will display correctly?

ANSWER: The error message means that that the query you are using combines two (or more tables) that have a Date Taken field. So in your criteria, you need to specify which table. Can you post the SQL for your query?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: When you said I was calling it from different tables, I checked the SQL and found I'd clicked the entire query then selected the Date Taken field separately so thank you for your help there!  That problem is fixed.

Now here's the next one!
This is the code from the button that calls the report:
''Private Sub cboAnalyst_Click()
Dim strCriteria As String
''If IsNull(Me.cboAnalyst) Then
  strCriteria = ""
Else
  strCriteria = "[Analyst Name] = " & Me.cboAnalyst
End If
''DoCmd.OpenReport "Ad Hoc Reporting", acPreview, , strCriteria
End Sub''
..and I get this error:  Run time error '438': Object doesn't support this property or method. and the first Else is in yellow when I hit debug.

What did I do wrong here?

Answer
Are the Bolded quote marks actually there? They should be.

'' Private Sub cboAnalyst_Click()
Dim strCriteria As String
'' If IsNull(Me.cboAnalyst) Then
 strCriteria = ""
Else
 strCriteria = "[Analyst Name] = " & Me.cboAnalyst
End If
'' DoCmd.OpenReport "Ad Hoc Reporting", acPreview, , strCriteria
End Sub''

Is Analyst Name a numeric or text value?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.