Need help with report generation

[sc2227]

Hi,



I have a form that will create a report based on selected criteria in my form. The form pulls from tables and the report is generated based off a query from these tables. My problem is the report generates, but it shows up as a blank report and I also don't know how to ensure it will report only the selected information I chose in the drop down menu.

I attached my access database. I use Access 2016. I had to compress it as a zip file because it is slightly over the attachment size limits of the forum.

Please open 'data entry' form and make selections in the drop down. Please make sure to click on the buttons next to the drop downs as I had to do this to ensure the menus refreshed properly.

Now as I said, when you click on Open Report button, it only opens an empty report. It doesn't create a report based on the information selected in the drop down menus. If you open my query in Design View, you will see the criteria I wrote. I placed the same criteria for all the drop down menus.

Where is it going wrong? Where do I update? Is the criteria correct in the query? Please advise. Again, I am a beginner and use Design View. I don't use too much coding.

This is the last step of my report generation and urgent. If I can bring up a report based on drop down menu selections, it will be completed. thank you in advance.

[ranman256]

your query should pull all the data,
the form will build a WHERE clause to filter the query.
If you have more than 1 box for criteria, then you must use VB to build the clause.

sub btnPrint_click()
if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

if sWhere <>"" then   sWhere= mid(sWhere,4)

       'apply filter
  docmd.OpenReport "myReport",acViewPreview,,sWhere

end sub

[sc2227]

thank you. is this code complete? I do not know how to code very well. Can I copy and paste what you wrote into VBA with no changes?

And do I open up the query and then paste in the code? Where exactly do I paste this code?

[ranman256]

the code goes in the BUTTON CLICK event, to open the report.
you must alter all my objects to the names of your object controls....textbox, combo box, checkbox, etc.
all fields, [field], must exist on the report too.

[sc2227]

I tried your code and I received the following error. I attached both the screen shot of the error and the updated database containing your code. I updated all my fields. I only have 3 combo boxes, so I made three lines for that and removed your text and checkboxes because I do not have those in my form. Please help!

Please let me know if I can use the other events like Macro or Expression Builder to achieve the same outcome as well. Since you are using this code in an Event tool under OnClick, you should be able to use the other Event tools to achieve the same outcome, correct?