Report follows filtres from a form - is it possible or not?

[Giulio Benvenuti]

Hello,
I have a question about queries/form/reports.

tblProvolone = name, surname, job
qryProvlone from tblProvolone


frmProvolone and rptProvolone from qryProvolone

qryProvolone/frmProvolone/rptProvolone haven't filters, but i can filter them with combobox filters.

example - (cboName select "Ma") i have in frmProvolone: SELECT * FROM qryProvolone WHERE [name] Like "*Ma*";
I want have this results in rptProvolone automatically so i can print a report only what i want

[apr pillai]

Yes, you can create a separate Query, using qryProvolon as Source for Report as shown above with the criteria under the [Name] Column as shown below:

Like "'" & Forms!frmProvolon!cboName & "'"

On the cboName Update Event Procedure enter the Me.Refresh Statement to refresh the Query Recordset immediately after the combobox content is changed and to get the data filtered for the Report.

[Giulio Benvenuti]

I would like to make a report with all Names. Actually it's giving me just the selected record. For example i want Mario/Matteo/Emmanuela, etc. all in a report (not separated)

Private Sub Comand_Click()
DoCmd.OpenReport "RptProvolone" _
, acViewNormal, , "Name='" & Name _
& "'"
End Sub

[ssanfu]

Be aware that "Name" is a reserved word in Access and shouldn't be used as an object name.

[CarlettoFed]

If you post an example file it may be easier to understand how to help you.

[Giulio Benvenuti]

This is the link for access file i created:

https://1drv.ms/u/s!AjFLWnkU3pswhJM7...FMLKA?e=2dwVPJ

I would like to filter for Job, Name or Surname (with multiple records results) and open rptProvolone with all records results in frmProvolone

[CarlettoFed]

You need to replace all the code in the Form_frmProvolone module with the following:

Option Compare Database
Option Explicit

Dim strFiltro As String

Private Sub cboJob_AfterUpdate()
    FiltraDati
End Sub

Private Sub cboName_AfterUpdate()
    FiltraDati
End Sub

Private Sub Form_Load()
    FiltraDati
End Sub

Private Sub noFilter_Click()
    Me.cboJob = Null
    Me.cboName = Null
    FiltraDati
End Sub

Private Sub stmpProvolone_Click()
    DoCmd.OpenReport "RptProvolone", acViewPreview, , strFiltro
End Sub

Private Sub FiltraDati()
    strFiltro = ""
    If Len(Trim(Me.cboJob & "")) > 0 Then strFiltro = "[Job] Like ""*" & Me.cboJob & "*"""
    If Len(Trim(Me.cboName & "")) > 0 Then strFiltro = strFiltro & IIf(strFiltro = "", "", " AND ") & "[Name] Like ""*" & Me.cboName & "*"""
    Me.Filter = strFiltro
    If strFiltro = "" Then
        Me.FilterOn = False
    Else
        Me.FilterOn = True
    End If
    Me.stmpProvolone.Visible = Not IsNull(Me.ID)
End Sub

[Giulio Benvenuti]

Thank you !!!

[DittoBird]

Another possibility is to set up qryProvolone with criteria like this (repeat for name - see note following, and surname if you add a combo box for it). It is pretty much what @apr pillai has mentioned, except I added nz in case any of the combo boxes are null.

Like "*" & Nz([Forms]![frmProvolone].[cboJob],"") & "*"

I would then change your afterupdate events to:

Private Sub cboJob_AfterUpdate()
    Me.Requery
End Sub

Private Sub cboName_AfterUpdate()
    Me.Requery
End Sub

You may also want to add another combo box for surname.

NOTE YOU MUST change "name" in your tale to something else as @ssanfu has mentioned above.
--
Tim

[Giulio Benvenuti]

This is the code:

---

Option Compare Database
Option Explicit


Dim strFiltro As String


Private Sub cboAggiornamentoPratica_AfterUpdate()
FiltraDati
End Sub


Private Sub cboAvvocato_AfterUpdate()
FiltraDati
End Sub


Private Sub cboIncarico_AfterUpdate()
FiltraDati
End Sub


Private Sub cboPaziente_AfterUpdate()
FiltraDati
End Sub


Private Sub cboProcuratore_AfterUpdate()
FiltraDati
End Sub


Private Sub cboStatoPratica_AfterUpdate()
FiltraDati
End Sub


Private Sub cboConsulente_AfterUpdate()
FiltraDati
End Sub


Private Sub NoFilter_Click()
Me.cboAggiornamentoPratica = Null
Me.cboAvvocato = Null
Me.cboIncarico = Null
Me.cboPaziente = Null
Me.cboProcuratore = Null
Me.cboStatoPratica = Null
Me.cboConsulente = Null
FiltraDati
End Sub


Private Sub FiltraDati()
strFiltro = ""
If Len(Trim(Me.cboAggiornamentoPratica & "")) > 0 Then strFiltro = "[AggiornamentoStato] Like ""*" & Me.cboAggiornamentoPratica & "*"""
If Len(Trim(Me.cboAvvocato & "")) > 0 Then strFiltro = "[Avvocato] Like ""*" & Me.cboAvvocato & "*"""
If Len(Trim(Me.cboIncarico & "")) > 0 Then strFiltro = "[Incarico] Like ""*" & Me.cboIncarico & "*"""
If Len(Trim(Me.cboPaziente & "")) > 0 Then strFiltro = "[Title] Like ""*" & Me.cboPaziente & "*"""
If Len(Trim(Me.cboProcuratore & "")) > 0 Then strFiltro = "[Procuratore] Like ""*" & Me.cboProcuratore & "*"""
If Len(Trim(Me.cboStatoPratica & "")) > 0 Then strFiltro = "[StatoPratica] Like ""*" & Me.cboStatoPratica & "*"""
If Len(Trim(Me.cboConsulente & "")) > 0 Then strFiltro = "[Consulente/Specialista] Like ""*" & Me.cboConsulente & "*"""
Me.Filter = strFiltro
If strFiltro = "" Then
Me.FilterOn = False
Else
Me.FilterOn = True
End I
Me.rptPrint.Visible = Not IsNull(Me.Title)
End Sub


Private Sub rptPrint_Click()
DoCmd.OpenReport "RptPraticheElenco", acViewPreview, , strFiltro
End Sub

---

What should i change to filter more then 1. For example i want search cboConsulente = "Michael" and cboIncarico = "in corso". I would like to see all records for "Michael" "in corso"