How to filter report via combo box and date range - Microsoft Community

hello microsoft community

i'm trying build form accepts information , opens report filtering 2 combo boxes , date range criteria. i've been looking @ 1 of public databases called "reportdialogue.accdb" , method 1 method i've been trying follow. believe have identical, in terms of code, merely changed variable names. example, sql report in reportdialogue is:

[code]

parameters forms!frmmultifielddlg_1!txtdatefrom datetime, forms!frmmultifielddlg_1!txtdateto datetime;
select employees.*, firstname & " " & lastname fullname, projects.project
from projects right join (employees left join projectemployees on employees.employeeid=projectemployees.employeeid) on projects.projectid=projectemployees.projectid
where (city=forms![frmmultifielddlg_1]!cbocity or forms![frmmultifielddlg_1]!cbocity null) , (department=forms![frmmultifielddlg_1]!cbodepartment or forms![frmmultifielddlg_1]!cbodepartment null) , (dateappointed>=forms![frmmultifielddlg_1]!txtdatefrom or forms![frmmultifielddlg_1]!txtdatefrom null) , (dateappointed<forms![frmmultifielddlg_1]!txtdateto+1 or forms![frmmultifielddlg_1]!txtdateto null);

[/code]

this have...

[code]

parameters forms!frmreport!txtdatefrom datetime, forms!frmreport_1!txtdateto datetime;
select tblagency.agencyname, tblagency.address, tblagency.province, tblsubsidiary.subsidiary, tblagency.affiliationagreementtype, tblagency.aaenddate, tblagency.liabilityinsurance, tblagency.wsibemployeedeclaration, tblprograms.code
from tblsubsidiary inner join (tblprograms inner join tblagency on tblprograms.id = tblagency.programs.value) on tblsubsidiary.id = tblagency.subsidiary.value
where (tblagency=forms![frmreport]!cboaffiliationagreementtype or forms![frmreport]!cboaffiliationagreementtype null) , (tblagency=forms![frmreport]!cbowsibempdeccom or forms![frmreport]!cbowsibempdeccom null) , (dateappointed>=forms![frmreport]!txtdatefrom or forms![frmreport]!txtdatefrom null) , (dateappointed<forms![frmreport]!txtdateto+1 or forms![frmreport]!txtdateto null);

[/code]

every time select 1 of drop down items , select random date range, hit enter, message saying the action or method requires report name argument. vba mentioned identical in reportdialogue database, report pops open regardless of user selects. know problem here might caused more 1 thing if has recommendations or guidance, appreciated!

in reportdialogue demo first method cancels opening of report , opens dialogue form if not open, passing name of report the  dialogue form via openargs mechanism with:

private sub report_open(cancel integer)

    const formnotopen = 2450
    dim frm form
    
    on error resume next
    set frm = forms!frmmultifielddlg_1
    if err = formnotopen then
        docmd.openform "frmmultifielddlg_1", openargs:=me.name
        cancel = true
    else
        if err <> 0 then
            ' unknown error
            msgbox err.description, vbexclamation, "error"
        end if
    end if
    
end sub

code in behind button in dialogue form is:

private sub cmdopenreport_click()

   const reportcancelled = 2501
    
    on error resume next
    docmd.openreport me.openargs, acviewpreview
    select case err.number
        case 0
        ' no error nothing
        case reportcancelled
        ' anticipated error nothing
        case else
        'unknown error inform user
        msgbox err.description
    end select
    
end sub

in value passed form openargs property used reportname argument when openreport method called.  

error you've incurred suggests me dialogue form has been opened independently, rather result of opening report.  in demo report opened button on opening form following code:

private sub cmdmethod1_click()

    on error resume next
    me.visible = false
    docmd.openreport "rptemployees_multifieldparam_1", view:=acviewpreview
    
end sub

hides opening form (not important point of view), , attempts open report.  dialogue form not open @ stage opening of report cancelled , dialogue form opened.  when opening of report cancelled raise error on docmd.openreport…. line above, error ignored virtue of the, admittedly rather crude, error handling.  

premise behind demo report opened directly, not dialogue form.  latter opened report, if attempt made independently error you've experienced occur.  if method used, application's user interface must designed report opened directly.  if want pursue alternative of opening dialogue form first, there examples of in multiselect demo in same onedrive folder.  these use similar dialogue form, methodology behind form differs.

Office / Access / Other/unknown / Office 2010