Callins a report from a form in MS Access 2013 and passing multiple ids 1

[InspectorHound]

Hi,

I have an expense report based on a complex query. The report is called from a form that displays a list of people. How do I call the report and pass to it all of the people shown in the form. I am passing a person id so the report will show the expenses related to that person -- but I would like to pass more than one person id.

Thanks

 

[dhookom]

Do you have any idea why you would get an error? Are [AuthorizationId] and VoucherID both numeric fields?

What have you tried in order to troubleshoot?

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

They are both data type 'Short Text'
This is a pretty simple command so I don't understand what I am doing wrong. When I hard-code the value passed from the form (from Debug.Print) directly in the report query, it works. The Open Report command is opening the report, but not passing the Where clause.

 

[MajP]

They are both data type 'Short Text

"[AuthorizationId] = '" & Me.VoucherID & "'

 

[InspectorHound]

Yes, that works. But I still can't get the where clause to be passed when the Where clause AuthorizationID is in a set of values returned from a query.
I have:
DoCmd.OpenReport "Expense Report", acViewPreview, , strWhere

strWhere = "[A.AuthorizationId] in (" & reportSQL & ")"

reportSQL = "Select AuthID from (" & strSQL & ")"

strSQL= SELECT * FROM qryUserAuth WHERE 1 = 1 AND [traveler last name] Like "*Bojko*

 

[dhookom]

Are there similar fields name AuthorizationID, AuthID, VoucherID that all store the same text values?

What do you get when you view the results of

debug.Print strWhere

I would expect strWhere to be something like:

AuthorizationID in (SELECT AuthID FROM qYourQueryName)

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

I posted it on June 12:

[AuthorizationId] in (Select AuthID from (SELECT * FROM qryUserAuth WHERE 1 = 1 AND [traveler last name] Like "*Bojko*" ))

 

[dhookom]

Does that work if you add it to the SQL of a query?
Why not just use:

[AuthorizationId] in (Select AuthID FROM qryUserAuth WHERE 1 = 1 AND [traveler last name] Like "*Bojko*" )

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

I can't do that because the second SELECT clause will change depending on what the user does a search on.

 

[dhookom]

What about something like:

strSQL= "SELECT AuthID FROM qryUserAuth WHERE 1 = 1 AND [traveler last name] Like '*Bojko*' "
strWhere = "[A.AuthorizationId] in (" & strSQL & ")"

Duane
Hook'D on Access
MS Access MVP

 

[InspectorHound]

Thanks, but this does not work.

 

[dhookom]

What do you see if you debug.print strWhere?

Do you get an error message?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Here is how you can open a report based on a filtered form. This includes allowing the user to apply a filter with the filter menu choice. In other words I do not care how the form gets filtered or how complex that filter is. I just read the keys that are showing on the form.

Private Sub Command9_Click()
  OpenFilteredReport
End Sub

Public Sub OpenFilteredReport()
  Dim IDs As String
  IDs = GetIDs
  Debug.Print IDs
  ' looks like: ('Company A', 'Company E', 'Company G')
  If IDs = "" Then
    MsgBox "No records selected"
  Else
    DoCmd.OpenReport "rptCustomers", acViewPreview, , "Company in " & IDs
  End If
End Sub

Private Function GetIDs() As String
  Const IDfield = "Company"
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
  Do While Not rs.EOF
    If GetIDs = "" Then
      GetIDs = "'" & rs.Fields(IDfield) & "'"
    Else
      GetIDs = GetIDs & ", '" & rs.Fields(IDfield) & "'"
    End If
    rs.MoveNext
  Loop
  If Not GetIDs = "" Then GetIDs = "(" & GetIDs & ")"
End Function

 

[InspectorHound]

Thanks! It worked