Run Report From Subform (1 Viewer)

June7

June7

AWF VIP
Local time
Yesterday, 23:00
Joined
Mar 9, 2014
Messages
5,466
Try using Like with wildcard.

Code: 
DoCmd.OpenReport cboReports, acViewReport, , "Facility Like '" & Me.cboFacility & "*'"
Or set a variable:
Code: 
Dim strWhere As String
If Not IsNull(cboFacility) Then strWhere = "Facility='" & cboFacility & "'"
...
DoCmd.OpenReport cboReports, acViewReport, , strWhere

BTW, I did more than remove brackets (could have included for same result) from the dynamic parameter. The path referencing is different from what was in the query of posted db, essentially what you say worked before.
 
Last edited:
P

psyc0tic1

Access Moron
Local time
Today, 02:00
Joined
Jul 10, 2017
Messages
360
June7 said:
BTW, I did more than remove brackets (could have included for same result) from the dynamic parameter. The path referencing is different from what was in the query of posted db, essentially what you say worked before.
Click to expand...

The query code I said worked last week
Code: 
Forms![frm_home]![ReportCenter].Form![cboFacility]
The query code you said to use:
Code: 
Forms!frm_home!ReportCenter.Form!cboFacility
Is that not the same minus the brackets?

I copied and pasted your code into my query and had the same result unless I am missing something by having looked at so much code I am blind to subtle changes.
 
June7

June7

AWF VIP
Local time
Yesterday, 23:00
Joined
Mar 9, 2014
Messages
5,466
Yes, but that was not the parameter in qry_waitvis. Which query are you editing?
 
P

psyc0tic1

Access Moron
Local time
Today, 02:00
Joined
Jul 10, 2017
Messages
360
June7 said:
Or set a variable:
Code: 
Dim strWhere As String
If Not IsNull(cboFacility) Then strWhere = "Facility='" & cboFacility & "'"
...
DoCmd.OpenReport cboReports, acViewReport, , strWhere
Click to expand...

This worked but I lost the ability to require them to choose a report. Leaving the facility blank and leaving the report choice blank produced a "run-time error 2497. The action or method requires a Report Name argument"

I tried leaving in the else code but it errored saying there was an else without an if.
 
P

psyc0tic1

Access Moron
Local time
Today, 02:00
Joined
Jul 10, 2017
Messages
360
June7 said:
Yes, but that was not the parameter in qry_waitvis. Which query are you editing?
Click to expand...

That actually was the parameter in qry_waitvis before posting post #1. After reading post #1 I realize there was missing code in the query I showed there.

This was the complete query code in qry_waitvis when it worked and then quit working:
Code: 
SELECT tbl_auditdata.AuditID, tbl_auditdata.Status, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.RecDate, tbl_auditdata.PartProdDate, TotalReceived/10 AS TenPercent, tbl_auditdata.RecEntryDate
FROM tbl_auditdata
WHERE (((tbl_auditdata.Status)="Waiting on Visual Inspection")) AND ((tbl_auditdata.Facility)=Forms![frm_home]![ReportCenter].Form![cboFacility] OR Forms![frm_home]![ReportCenter].Form![cboFacility] IS NULL)
ORDER BY tbl_auditdata.RecDate;
 
June7

June7

AWF VIP
Local time
Yesterday, 23:00
Joined
Mar 9, 2014
Messages
5,466
No, you should still include the code that checks cboReports. It will be another If Then.

See the 3 dots in my example, that's your code - with a change to use the variable.

Did query stop working after you built VBA code to use WHERE argument?
 
Last edited:
P

psyc0tic1

Access Moron
Local time
Today, 02:00
Joined
Jul 10, 2017
Messages
360
June7 said:
No, you should still include the code that checks cboReports.

See the 3 dots in my example, that's your code - with a change to use the variable.
Click to expand...

Okay... so here is the code:
Code: 
Private Sub cmdGenerateReport_Click()

    Dim strWhere As String

   If Not IsNull(cboFacility) Then strWhere = "Facility='" & cboFacility & "'"

     DoCmd.OpenReport cboReports, acViewReport, , strWhere
     
'   Else
'     MsgBox ("You Must First Select a Report To Print!")
'     cboReports.SetFocus
'   End If

   cboReports = ""
End Sub

I commented out the Else and it worked like I said above. I tried moving it to before the DoCmd part but that doesn't work.

I do not know what to do to that code to make it function.
 
June7

June7

AWF VIP
Local time
Yesterday, 23:00
Joined
Mar 9, 2014
Messages
5,466
Apparently I edited my post after you read it. Use another If Then.
Code: 
Private Sub cmdGenerateReport_Click()

    Dim strWhere As String

   If Not IsNull(cboFacility) Then strWhere = "Facility='" & cboFacility & "'"
   If Me.cboReports & "" <> "" Then
     DoCmd.OpenReport cboReports, acViewReport, , strWhere    
   Else
     MsgBox ("You Must First Select a Report To Print!")
     cboReports.SetFocus
   End If

   cboReports = ""
End Sub
 
P

psyc0tic1

Access Moron
Local time
Today, 02:00
Joined
Jul 10, 2017
Messages
360
June7 said:
Apparently I edited my post after you read it. Use another If Then.
Code: 
Private Sub cmdGenerateReport_Click()

    Dim strWhere As String

   If Not IsNull(cboFacility) Then strWhere = "Facility='" & cboFacility & "'"
   If Me.cboReports & "" <> "" Then
     DoCmd.OpenReport cboReports, acViewReport, , strWhere    
   Else
     MsgBox ("You Must First Select a Report To Print!")
     cboReports.SetFocus
   End If

   cboReports = ""
End Sub
Click to expand...

Thank you this fixed it.

As for your edit to a previous post... the query stopped working before I ever did any code edits anywhere. However with all you have helped me with the query isn't the same anymore and that part became irrelevant.
 
June7

June7

AWF VIP
Local time
Yesterday, 23:00
Joined
Mar 9, 2014
Messages
5,466
Or don't use variable:

DoCmd.OpenReport cboReports, acViewReport, , IIf(IsNull(Me.cboFacility), "", "Facility='" & Me.cboFacility & "'")

Always more than one ....
 
P

psyc0tic1

Access Moron
Local time
Today, 02:00
Joined
Jul 10, 2017
Messages
360
June7 said:
Or don't use variable:

DoCmd.OpenReport cboReports, acViewReport, , IIf(IsNull(Me.cboFacility), "", "Facility='" & Me.cboFacility & "'")

Always more than one ....
Click to expand...

This was perfect... thank you so much... here is the final code that works perfect
Code: 
Private Sub cmdGenerateReport_Click()

    If Me.cboReports & "" <> "" Then
        DoCmd.OpenReport cboReports, acViewReport, , IIf(IsNull(Me.cboFacility), "", "Facility='" & Me.cboFacility & "'")
    Else
     MsgBox ("You Must First Select a Report To Print!")
     cboReports.SetFocus
   End If

   cboReports = ""
End Sub
 
P

psyc0tic1

Access Moron
Local time
Today, 02:00
Joined
Jul 10, 2017
Messages
360
If I may ask another question on this subject (well sort of)

I added a field to my reports that will display the facility in the header when you choose one from the first combobox and it works as expected.

However when I choose to not choose a facility it will display a random facility in that field even though the report is showing all records from all facilities.

The field is a combobox that is displaying like a text box. probably doing that wrong but how do I make it show nothing if a facility was not chosen in the first combobox on the report generating form?

I do not know what all to post as far as code so I will post all I can think is relevant.

Code for form frm_newreportcenter):
Code: 
Option Compare Database
Option Explicit

Private Sub Form_Load()
   If Credentials.AccessLvlID = 1 Then
     Me.cboReports.RowSource = "SELECT MsysObjects.Name FROM MsysObjects WHERE ((MsysObjects.Type)=-32764) ORDER BY MsysObjects.Name DESC;"
   Else
     Me.cboReports.RowSource = "SELECT MsysObjects.Name FROM MsysObjects WHERE (((Left([Name],5))<>'Admin') AND ((MsysObjects.Type)=-32764)) ORDER BY MsysObjects.Name DESC;"
   End If
End Sub

Private Sub cmdGenerateReport_Click()

    On Error GoTo Error
    If Me.cboReports & "" <> "" Then
        DoCmd.OpenReport cboReports, acViewReport, , IIf(IsNull(Me.cboFacility), "", "Facility='" & Me.cboFacility & "'")
    Else
     MsgBox ("You Must First Select a Report To Print!")
     cboReports.SetFocus
   End If

   cboReports = ""
   Exit Sub
Error:
   
End Sub

Report to use as example (Admin Completed POs) record source (qry_complete)
Code: 
SELECT tbl_auditdata.AuditID, tbl_auditdata.Status, tbl_auditdata.Facility, tbl_auditdata.PONumber, tbl_auditdata.PartNumber, tbl_auditdata.TotalReceived, tbl_auditdata.RecDate, tbl_auditdata.VisInspectDate, tbl_auditdata.TotalVisInspected, tbl_auditdata.TotalVisBad, tbl_auditdata.TotalVisGood, tbl_auditdata.TotalFunctTested, tbl_auditdata.LabTestDate, tbl_auditdata.TotalFunctTested, tbl_auditdata.TotalFunctBad, tbl_auditdata.TotalFunctGood, IIf(TotalVisInspected=0,0,TotalVisInspected/TotalReceived) AS PercentVisInspected, IIf(TotalFunctTested=0,0,TotalFunctTested/TotalReceived) AS PercentLabTested
FROM tbl_auditdata
WHERE (((tbl_auditdata.Status)="Complete"))
ORDER BY tbl_auditdata.RecDate DESC;
Field on report name (Combo62) (I know don't kill me) control source (Facility) row source (imbedded query)
Code: 
SELECT tbl_facilities.[FacilityNumber] AS Expr1, tbl_facilities.[FacilityName] AS Expr2 FROM tbl_facilities ORDER BY tbl_facilities.[FacilityNumber];

As you can see it put a random facility in the header but I did not choose one
 

Attachments

  • Capture.JPG
    Capture.JPG
    48.3 KB · Views: 112
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 08:00
Joined
Sep 21, 2011
Messages
14,232
You could pass the name of the facility in as an OpenArgs parameter, and "All" if none supplied.?
 
P

psyc0tic1

Access Moron
Local time
Today, 02:00
Joined
Jul 10, 2017
Messages
360
Gasman said:
You could pass the name of the facility in as an OpenArgs parameter, and "All" if none supplied.?
Click to expand...

The OpenArgs has always been an enigma for me... time for some heavy reading
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 08:00
Joined
Sep 21, 2011
Messages
14,232
This allowed me to use the same report layout where I added a WHERE clause against a basic report. The description of the report became the title which is passed as OpenArgs.

HTH

In my report
Code: 
Private Sub Report_Load()
If Nz(Me.OpenArgs, "") <> "" Then
    Me.Auto_Title0.Caption = Me.OpenArgs
End If
End Sub

In my form
Code: 
Private Sub cmdOpen_Click()
' Either open a basic report/query, or open form for same with parameters
Dim strDescription As String, strName As String, strForm As String, strWhere As String
Dim iPreview As Integer
strDescription = Me.cboObject.Column(1)
strName = Me.cboObject.Column(2)
strForm = Me.cboObject.Column(3)
strWhere = Nz(cboObject.Column(4), "")

If Me.chkPreview Then
     iPreview = 2 'acPreview
Else
    iPreview = 0 ' aacNormal
End If

If Nz(strForm, "") = "" Then
    Select Case Me.txtObjectType
        Case "Report"
            If strWhere = "" Then
                DoCmd.OpenReport strName, iPreview, , , , strDescription
            Else
                DoCmd.OpenReport strName, iPreview, , strWhere, , strDescription
            End If
        Case "Query"
            DoCmd.OpenQuery strName
        Case "Form"
            DoCmd.OpenForm strName
        Case Else
            MsgBox "Object Type not catered for"
    End Select
Else
    DoCmd.OpenForm strForm, , , , , , strName
End If
End Sub
 
P

psyc0tic1

Access Moron
Local time
Today, 02:00
Joined
Jul 10, 2017
Messages
360
Gasman said:
This allowed me to use the same report layout where I added a WHERE clause against a basic report. The description of the report became the title which is passed as OpenArgs.
Click to expand...

Gasman thank you for your help... I actually decided to ditch my OCD and remove the field from my report header since the same field was in the detail section and when generating the reports for all facilities it displayed correctly there.
 
You must log in or register to reply here.

Similar threads

C
Run-time error '2455' when reading control names from a subform.
Replies
4
Views
565
Uncle Gizmo
Uncle Gizmo
K
Open Report from Search SubForm
Replies
6
Views
999
King21
K
P
Slow Loading Tab Control
Replies
10
Views
1,600
psyc0tic1
P
S
View and print report from subform
Replies
9
Views
762
theDBguy
theDBguy
R
Accessing controls from navigation form
Replies
2
Views
499
reynier09
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom