Button to automatically create a report? (1 Viewer)

[Denise2020]

Hi all,

I must preface this with that all I know about Access, I have learned from googling and youtube.

I have a database with a split form based on a query. In that form, I have a dropdown combobox that shows one of my fields in order for users to be able to easily select one item from the dropdown list and filter the results (I hope that is clear).

I suppose I should explain the list is of objects that are loaned out by coworkers. There are five people, so the dropdown list enables us to see all of the items loaned out, or items loaned out by one person. I originally created five reports from this form so that I could create a button to easily print out the results filtered in a certain way. But having five extra buttons and five extra objects in the db is messy and not doesn't really make for a nice looking or easy to look at and navigate form.

What I am looking for, if possible, is how to create a single button that would print out in report form the results of whatever filter is selected, whether it be all records or just the one person chosen from the dropdown. And it also needs to print in landscape mode due to its width. Again, I hope this is clear. Is this possible?

I could of course make a basic print button but that prints the form and not in landscape mode. I do need to be able to "pre define" how the report looks so that it prints correctly, including turning to landscape mode. Is there a way to do this or do I have to stick with the five pre-made report buttons?

I apologize if this is muddle and confusing and will gladly answer any questions. Thanks so much!

 

[arnelgp]

you only need 1 report.
create new report or modify the existing one.
do not add Filter on the report's Recordsource.

on the Click event of the button on your Form, open the Report with "Where Clause"
thereby, filtering the report to your combobox:

docmd.OpenReport ReportName:="reportname",View:=acViewPreview,WhereCondition:="FieldToFilter='" & [yourCombo] & "'"

 

[Denise2020]

This is wonderful! It created the report in preview mode which is lovely. However, my syntax must be wrong because it showed all results and not the filtered I chose with the combobox. This is what I have:

DoCmd.OpenReport "rptInUseBy", acViewPreview, InUseBy = "" & [cboFilterInUseBy] & ""

Could I have typed it incorrectly? Or could it be that the field to filter (InUseBy) needs to be prefaced with the query or table it originates on?

Thank you so much! I have spent hours in two days on this single button.

 

[bob fitz]

You don't quite have what arnelgp gave you. Try:
DoCmd.OpenReport "rptInUseBy", acViewPreview, InUseBy = '" & [cboFilterInUseBy] & "'"

 

[Gasman]

Shouldn't that be

DoCmd.OpenReport "rptInUseBy", acViewPreview, "InUseBy = '" & [cboFilterInUseBy] & "'"
[code]

 

[Denise2020]

Okay so I have tried both of those and I get an error 2465 "can't find the field in the expression".

I have
DoCmd.OpenReport "rptInUseBy", acViewPreview, "InUseBy = '" & [cboFilterInUseBy] & "'"

I apologize for continuing to ask, it is probably quite apparent to you guys. I've been trying to find classes but there is just nothing available right now. Thanks again so much for your help!

 

[Gasman]

Ok, the field in the table is meant to be called InUseBy.
The control with the value you want to look up is meant to be called cboFilterInuseBy ?

If this is not the case, adjust to suit.

 

[Denise2020]

That is correct, the field to look up is InUseBy, and the combobox I am using to select it is the cboFilterInUseBy.

 

[Minty]

I'll jump in briefly as everyone has done the hard work already
Try

DoCmd.OpenReport "rptInUseBy", acViewPreview, , "InUseBy = '" & me.cboFilterInUseBy & "'"

Edit Syntax Check;
Docmd.OpenReport name, acViewPreview, filter ,where, acWindowNormal, openargs

This assumes that your combo is returning a Name and not an ID associated with the Name

 

[theDBguy]

Okay so I have tried both of those and I get an error 2465 "can't find the field in the expression".

I have
DoCmd.OpenReport "rptInUseBy", acViewPreview, "InUseBy = '" & [cboFilterInUseBy] & "'"

I apologize for continuing to ask, it is probably quite apparent to you guys. I've been trying to find classes but there is just nothing available right now. Thanks again so much for your help!

Hi Denise. You could have tried the syntax @arnelgp provided. If you stick with your syntax, I think you're missing one more comma after acViewPreview (it needs to be two).

 

[Denise2020]

You are all so helpful, thank you! I am off work now but will try this first thing Monday morning. I cannot express how great you all are to try to help!

 

[Denise2020]

By jove, I've got it! Thank you all so very kindly!

 

[theDBguy]

By jove, I've got it! Thank you all so very kindly!

Congratulations! Glad to hear you got it sorted out. Good luck with your project.