refer a report from a form

A

Ac

Hi,

Is that possible that refer a report from a form?

I try to create a Search Form, there are some combo boxes on it to take the
input from a user; based on the multiple inputs, it will re-query the query
called qAll, then display the result on the report named ReportByStudents.
Below is the code, it does not work, could someone help me? Thanks!

Private Sub cmdReopenReportByStudents_Click()
Dim Where1 As String

Where1 = ""

If Not IsNull(cboStudentID) Then
Where1 = "Where CStr(StudentID) = """ + CStr(cboStudentID) + """"
End If

If Not IsNull(cboFirstName) Then
If Len(Where1) = 0 Then
Where1 = "Where FirstName = """ + cboFirstName + """"
Else

Where1 = Where1 + ("AND FirstName =""" + cboFirstName + """")
End If
End If


If Not IsNull(cboLastName) Then
If Len(Where1) = 0 Then
Where1 = "Where LastName = """ + (cboLastName) + """"
Else

Where1 = Where1 + ("AND LastName =""" + cboLastName + """")
End If
End If


If Not IsNull(cboCurEmployer) Then
If Len(Where1) = 0 Then
Where1 = "Where CurEmployer = """ + cboCurEmployer + """"
Else
Where1 = Where1 + ("AND CurEmployer = """ + cboCurEmployer + """")

End If
End If



If Not IsNull(cboClassID) Then
If Len(Where1) = 0 Then
Where1 = "Where CStr(ClassID) = """ + CStr(cboClassID) + """"
Else
Where1 = Where1 + ("AND Cstr(ClassID )= """ + CStr(cboClassID) + """")
End If
End If

If Not IsNull(cboClassName) Then
If Len(Where1) = 0 Then
Where1 = "Where ClassName = """ + cboClassName + """"
Else
Where1 = Where1 + ("AND ClassName = """ + cboClassName + """")
End If
End If


If Not IsNull(cboCompletionDate) Then
If Len(Where1) = 0 Then
Where1 = "Where CStr(CompletionDate) = """ +
CStr(cboCompletionDate) + """"
Else
Where1 = Where1 + ("AND CStr(CompletionDate) = """ +
CStr(cboCompletionDate) + """")
End If
End If


If Len(Where1) > 0 Then

Where1 = "Select * from qAll " + Where1
Report!ReportByStudents.Report.RecordSource = Where1
Report!ReportByStudents.Report.Requery
DoCmd.OpenReport "ReportByStudents", acViewPreview, , Where1

End If
End Sub
 
K

Klatuu

That is not the best way to do it. The OpenReport method has a Where
argument used to pass filtering parameters to a report. You put no filtering
in the report's record source unless it applies to the entire report
regardless of other filtering selections. Then you build the Where string,
sort of like you are doing, but below is a better way to accomplish it. Note
I have included a function named AddAnd that I use for just this sort of
thing:

Private Sub cmdReopenReportByStudents_Click()
Dim strWhere As String

If Not IsNull(Me.cboStudentID) Then
strWhere = "[StudentID] = " & Me.cboStudentID
End If

If Not IsNull(Me.cboFirstName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[FirstName] = """ & Me.cboFirstName & """"
End If

If Not IsNull(Me.cboLastName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[LastName] = """ & Me.cboLastName & """"
End If

If Not IsNull(Me.cboCurEmployer) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[CurEmployer] = """ & Me.cboCurEmployer & """"
End If

If Not IsNull(Me.cboClassID) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ClassID] = " + Me.cboClassID
End If

If Not IsNull(Me.cboClassName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ClassName] = """ & Me.cboClassName & """"
End If

If Not IsNull(Me.cboCompletionDate) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[CompletionDate = #" & Me.cboCompletionDate
& "#"
End If

DoCmd.OpenReport "ReportByStudents", acViewPreview, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If


AddAnd_Exit:

Exit Function
On Error GoTo 0

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmDashBoard"
GoTo AddAnd_Exit

End Function
 
A

Ac

Thanks Dave, it is a great idea to use the function; it works well.

Klatuu said:
That is not the best way to do it. The OpenReport method has a Where
argument used to pass filtering parameters to a report. You put no filtering
in the report's record source unless it applies to the entire report
regardless of other filtering selections. Then you build the Where string,
sort of like you are doing, but below is a better way to accomplish it. Note
I have included a function named AddAnd that I use for just this sort of
thing:

Private Sub cmdReopenReportByStudents_Click()
Dim strWhere As String

If Not IsNull(Me.cboStudentID) Then
strWhere = "[StudentID] = " & Me.cboStudentID
End If

If Not IsNull(Me.cboFirstName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[FirstName] = """ & Me.cboFirstName & """"
End If

If Not IsNull(Me.cboLastName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[LastName] = """ & Me.cboLastName & """"
End If

If Not IsNull(Me.cboCurEmployer) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[CurEmployer] = """ & Me.cboCurEmployer & """"
End If

If Not IsNull(Me.cboClassID) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ClassID] = " + Me.cboClassID
End If

If Not IsNull(Me.cboClassName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ClassName] = """ & Me.cboClassName & """"
End If

If Not IsNull(Me.cboCompletionDate) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[CompletionDate = #" & Me.cboCompletionDate
& "#"
End If

DoCmd.OpenReport "ReportByStudents", acViewPreview, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If


AddAnd_Exit:

Exit Function
On Error GoTo 0

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmDashBoard"
GoTo AddAnd_Exit

End Function


--
Dave Hargis, Microsoft Access MVP


Ac said:
Hi,

Is that possible that refer a report from a form?
Click to expand...
Click to expand...
 
K

Klatuu

Glad I could help.
--
Dave Hargis, Microsoft Access MVP


Ac said:
Thanks Dave, it is a great idea to use the function; it works well.

Klatuu said:
That is not the best way to do it. The OpenReport method has a Where
argument used to pass filtering parameters to a report. You put no filtering
in the report's record source unless it applies to the entire report
regardless of other filtering selections. Then you build the Where string,
sort of like you are doing, but below is a better way to accomplish it. Note
I have included a function named AddAnd that I use for just this sort of
thing:

Private Sub cmdReopenReportByStudents_Click()
Dim strWhere As String

If Not IsNull(Me.cboStudentID) Then
strWhere = "[StudentID] = " & Me.cboStudentID
End If

If Not IsNull(Me.cboFirstName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[FirstName] = """ & Me.cboFirstName & """"
End If

If Not IsNull(Me.cboLastName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[LastName] = """ & Me.cboLastName & """"
End If

If Not IsNull(Me.cboCurEmployer) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[CurEmployer] = """ & Me.cboCurEmployer & """"
End If

If Not IsNull(Me.cboClassID) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ClassID] = " + Me.cboClassID
End If

If Not IsNull(Me.cboClassName) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[ClassName] = """ & Me.cboClassName & """"
End If

If Not IsNull(Me.cboCompletionDate) Then
strWhere = AddAnd(strWhere)
strWhere = strWhere & "[CompletionDate = #" & Me.cboCompletionDate
& "#"
End If

DoCmd.OpenReport "ReportByStudents", acViewPreview, , strWhere

End Sub


Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If


AddAnd_Exit:

Exit Function
On Error GoTo 0

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmDashBoard"
GoTo AddAnd_Exit

End Function


--
Dave Hargis, Microsoft Access MVP


Ac said:
Hi,

Is that possible that refer a report from a form?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search using alternative multiple fields 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Crosstab Query Building Report 2
Report based on form filter? 1
MS ACCESS: SEARCH in a split form 0
VBA works in orignal but fails in copy of database 1
Function to convert CUSIP code to ISIN code cusipconv() 1
Error 3085: Undefined function 'N' in expression. - Access 2010 1

Top