Creating report on criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm looking for assistance as I have been researching this problem by
reading the threads for the last few days and I'm unable to resolve myself.

On my startup form there is a section where one can request one of three
reports using either command button for Preview or Print. However, the
audience may not want to see all data for all employees or centres;
therefore, I created unbound textboxes for the audience to select what
criteria to base the report on.. with the intent that they can use more than
one criteria at once. The criteria is: AgentName, Coach, Centre,
CreatedDate, Period or PeriodWeek. These are all dropdown boxes except for
CreatedDate where the data is manually entered.

When just trying to get a report to work based on Agent Name alone, this is
the code for Preview button:

Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click:

Dim pstcriteria As String
pstrcriteria = "[2nd Level Support
Table.AgentName]=[forms]![DataEntryForm]!me!AgentNameSelect"
Select Case SelectReport
Case 1
DoCmd.OpenReport "Employee Case Creation Details", acViewPreview, ,
pstrcriteria
Case 2
DoCmd.OpenReport "Pending Cases Report", acViewPreview, , pstrcriteria
Case 3
DoCmd.OpenReport "Employee Coaching Received", acViewPreview, ,
pstrcriteria
End Select
Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Error$
Resume Exit_PreviewReport_Click

End Sub


Coding for AgentName txtbox:

Private Sub AgentNameSelect_AfterUpdate()
Dim pstrcriteria As String
pstrcriteria = "[2nd Level Support
Table.AgentName]=[forms]![DataEntryForm]!me!AgentNameSelect"

Select Case SelectReport
Case 1
DoCmd.OpenReport "Employee Case Creation Details", acViewPreview, ,
pstrcriteria
Case 2
DoCmd.OpenReport "Pending Cases Report", acViewPreview, , pstrcriteria
Case 3
DoCmd.OpenReport "Employee Coaching Received", acViewPreview, ,
pstrcriteria
End Select

End Sub


When I run the Preview button, I receive a Parameter Value error :
Forms!DataEntryForm!me!AgentNameSelect

Is someone able to advise what the correct coding is, especially if I want
to have a number of criteria selections.

Thank You.
 
The Me section of this line makes no sense.
[forms]![DataEntryForm]!me!AgentNameSelect

If this code live within the form called DataEntryForm, you should use:
pstrCriteria = "[2nd Level Support Table].[AgentName] '" &
Me.AgentNameSelect & "'"

If the code lives in a different form you should use:
"[2nd Level Support Table].[AgentName] '" &
Forms![DataEntryForm]![AgentNameSelect] & "'"

Notice that the table's field names is inside the quotes and I appended
single quote before and after the reference to the form's control? This is
important, assuming the field AgentName is a string. Also notice the way I
bracketed the table name and field name.

To deal with multiple criteria, you'll need to build the criteria string.
Something like:

If Not Isnull(Me.AgentNameSelect) Then
pstrCriteria = "[2nd Level Support Table].[AgentName]= '" &
me!AgentNameSelect & '" AND "
End If

' Repeat this method for each criteria field...
If Not IsNull(Me.OtherTextBox) Then
pstrCriteria = pstrCriteria & "[2nd Level Support].[OtherFieldName] = '" &
Me.OtherTextBox & "' AND "
End If

' At the end, strip off the trailing AND
If Right$(pstrCriteria,4) + " AND" Then
pstrCriteria = Left$(pstrCriteria, Len(pstrCriteria)-4))
End If
DoCmd.OpenReport "My Report Name" acPreview, pstrCriteria

Barry


GailK said:
Hi,

I'm looking for assistance as I have been researching this problem by
reading the threads for the last few days and I'm unable to resolve myself.

On my startup form there is a section where one can request one of three
reports using either command button for Preview or Print. However, the
audience may not want to see all data for all employees or centres;
therefore, I created unbound textboxes for the audience to select what
criteria to base the report on.. with the intent that they can use more than
one criteria at once. The criteria is: AgentName, Coach, Centre,
CreatedDate, Period or PeriodWeek. These are all dropdown boxes except for
CreatedDate where the data is manually entered.

When just trying to get a report to work based on Agent Name alone, this is
the code for Preview button:

Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click:

Dim pstcriteria As String
pstrcriteria = "[2nd Level Support
Table.AgentName]=[forms]![DataEntryForm]!me!AgentNameSelect"
Select Case SelectReport
Case 1
DoCmd.OpenReport "Employee Case Creation Details", acViewPreview, ,
pstrcriteria
Case 2
DoCmd.OpenReport "Pending Cases Report", acViewPreview, , pstrcriteria
Case 3
DoCmd.OpenReport "Employee Coaching Received", acViewPreview, ,
pstrcriteria
End Select
Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Error$
Resume Exit_PreviewReport_Click

End Sub


Coding for AgentName txtbox:

Private Sub AgentNameSelect_AfterUpdate()
Dim pstrcriteria As String
pstrcriteria = "[2nd Level Support
Table.AgentName]=[forms]![DataEntryForm]!me!AgentNameSelect"

Select Case SelectReport
Case 1
DoCmd.OpenReport "Employee Case Creation Details", acViewPreview, ,
pstrcriteria
Case 2
DoCmd.OpenReport "Pending Cases Report", acViewPreview, , pstrcriteria
Case 3
DoCmd.OpenReport "Employee Coaching Received", acViewPreview, ,
pstrcriteria
End Select

End Sub


When I run the Preview button, I receive a Parameter Value error :
Forms!DataEntryForm!me!AgentNameSelect

Is someone able to advise what the correct coding is, especially if I want
to have a number of criteria selections.

Thank You.
Click to expand...
 
GailK said:
I'm looking for assistance as I have been researching this problem by
reading the threads for the last few days and I'm unable to resolve myself.

On my startup form there is a section where one can request one of three
reports using either command button for Preview or Print. However, the
audience may not want to see all data for all employees or centres;
therefore, I created unbound textboxes for the audience to select what
criteria to base the report on.. with the intent that they can use more than
one criteria at once. The criteria is: AgentName, Coach, Centre,
CreatedDate, Period or PeriodWeek. These are all dropdown boxes except for
CreatedDate where the data is manually entered.

When just trying to get a report to work based on Agent Name alone, this is
the code for Preview button:

Private Sub PreviewReport_Click()
On Error GoTo Err_PreviewReport_Click:

Dim pstcriteria As String
pstrcriteria = "[2nd Level Support
Table.AgentName]=[forms]![DataEntryForm]!me!AgentNameSelect"
Select Case SelectReport
Case 1
DoCmd.OpenReport "Employee Case Creation Details", acViewPreview, ,
pstrcriteria
Case 2
DoCmd.OpenReport "Pending Cases Report", acViewPreview, , pstrcriteria
Case 3
DoCmd.OpenReport "Employee Coaching Received", acViewPreview, ,
pstrcriteria
End Select
Exit_PreviewReport_Click:
Exit Sub

Err_PreviewReport_Click:
MsgBox Error$
Resume Exit_PreviewReport_Click

End Sub


Coding for AgentName txtbox:

Private Sub AgentNameSelect_AfterUpdate()
Dim pstrcriteria As String
pstrcriteria = "[2nd Level Support
Table.AgentName]=[forms]![DataEntryForm]!me!AgentNameSelect"

Select Case SelectReport
Case 1
DoCmd.OpenReport "Employee Case Creation Details", acViewPreview, ,
pstrcriteria
Case 2
DoCmd.OpenReport "Pending Cases Report", acViewPreview, , pstrcriteria
Case 3
DoCmd.OpenReport "Employee Coaching Received", acViewPreview, ,
pstrcriteria
End Select

End Sub


When I run the Preview button, I receive a Parameter Value error :
Forms!DataEntryForm!me!AgentNameSelect
Click to expand...


Your criteria string is improperly constructed.

pstrcriteria = "AgentName=""" & Me!AgentNameSelect & """"
 
Back
Top