Reports in access, user input box, graph? 2

[GoldenFireTiger]

Hi there, I have a few sales reports, but I want to have an input box whereby the user enters the month and year, and a report is shown/generated including a graph. is there any way to automate this process or am I asking too much?

 

[GingerR]

Sure--I suggest making a form, and putting a text box or two text boxes (depending on how you want the input). Then a button which launches a report. Build a report with a chart on it. The report/chart will reference the text box controls on the form to filter on which months/years you want to view. You can search forums here, HELP and text books to begin working on these features. So try getting started and let us know if you need more help.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Reports and graphs are both based on tables, queries, or sql statements. Queries and sql statements can have criteria supplied from controls on forms. Consider reviewing the information at

http://www.fontstuff.com/access/acctut08.htm.

Come back if you have issues.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GoldenFireTiger]

GINGER R

Thanks for the help. Ive created a form with a text box on it, and a button to open a report. Ive then built a report with a chart on it. So far, the text box isn't linked to anything. How do I get the report to change according to what the user types in to the text box? Is there a feature of the report which would allow me to state the value of the text box as the x axis of the graph? I'm new to reports, so sorry if i've missed something.

DHOOKOM

My report is based on the query qryAllSales, which displays all sales figures for the past year (all previous sales are archived, but the user wont need a report for these figures, only the ones within 12 months). Am I right in thinking that by typing something like "Like(forms]![frmReportOptions]![txtUserInput]&"*") beneath the date of sale in the design of the query I can automatically change my report? I will check out the site you reccommended, thanks for the help!

 

[GingerR]

You wouldn't put LIKE *.
You'd just put Forms!frmReportOptions!txtUserInput

sounds like you're on the right track and have done everything you need to do. Do you still need help?


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GoldenFireTiger]

Ginger R

I haven't updated the query yet, but thank you ever so much for your help. Once I change my query, will the chart and report automatically update? and should I set a macro on rptSales so that the new form opens when the user tries to view a sales report?

 

[GingerR]

I would launch the report from the form. That's a typical set up.

The form is open first. Pretty text box(es) and a button that says VIEW on it. User enters in a date or whatever criteria you're looking form. They push the button. The code in the button's OnClick event opens the report (Docmd.OpenReport "Blah", acViewPreview).

The report's criteria will not prompt the user for info, instead the criteria references the text boxes on the form. Yes--once you change the criteria in the query that the report is based on, the report will only show that data. Try it out and see.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GoldenFireTiger]

Ginger R

Ive updated my query, but when I type in 02/05, the query returns nothing. Why is this? (In the tables my sales are stored in the following format 11-Feb-05 but my input mask is 00/00/00 so 11/02/05 changes to 11-Feb-05. Is this causing a problem?

 

[GingerR]

What is your query? What is some sample data/table structure?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GoldenFireTiger]

the query is qryAllSales, and is based on tblSale and tblSaleDetails. tblSale consists of SaleNo and DateOfSale, and tblSaleDetails consists of SaleNo, ItemCode, and ItemQuantitySold. All fields are present in the query (saleNo only once), and it is in the criteria of DateOfSale I have typed Forms!frmReportOptions!txtUserInput.

Sample data from tblSale:

SaleNo DateOfSale
S121 11-Feb-05 (11/02/05 is typed)
S122 12-Feb-05 (12/02/05 is typed)
S123 13-Feb-05 (13/02/05 is typed)

Sample data from tblSaleDetails:

SaleNo ItemCode ItemQuantitySold
S121 12345 1
S121 23456 3
S122 78910 4

 

[GoldenFireTiger]

Also, on the main menu there is a reports button, whereby a dialogue box is displayed when the reports button is pressed. In the dialogue box a list box displays the reports and the user chooses the one they want and presses view. Should I take the sales report out of this list and make a seperate button for this which would open frmReportOptions?

 

[GingerR]

you said you are typing in "02/05", but your data is not in that format. You have to input data type consistant with the data type in your table, or change your query/report to translate your table's data into what you are inputting (i.e. user enters "02/05" and you translate that into "...between 2/1/05 and 2/28/05").

as for how you launch the report, i don't see why you'd need to change how it currently is set up. Why do you think so? By the way, I wish you would have told the whole story in your first post...I thought from your first post that you didn't have a report built, or any idea of how to use a form with a text box and a button on it!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GoldenFireTiger]

Ginger R

Sorry about the confusion, I thought it would put people off if my first message was too detailed, i did mention that i had a few sales reports in the first message though.

I am no means an expert in access, but i have a basic understanding in that i can create simple forms etc, im not too good on queries and reports though.

How do I use a query translate 02/05 into between 1/2/05 and 2/28/05?

i thought I might have to change how the reports are currently set up as at the minute if the user selects rptSales from the list box a message box appears (called Enter Parameter value) and says forms!frmReportOptions!txtUserInput and has a text box. I think this would confuse the user if they launched the report in this way.

 

[GingerR]

as for launching reports: the way i do it is on the same form, i have text boxes and such invisible when the form opens. then depending on what kind of report the user picks from an option box or combo box or whatever, certain applicable text boxes and other controls become visible. that way only that ones that apply to the certain report.

The reason i put text boxes and such on forms to get parameters vs. prompting users with a criteria such as "[Enter Date:]" is because usually many reports feed off of the same info, and i don't want to have to ask the user for the same month or date or year or anything else over and over again.

So i wouldn't have anything pop up. I'd make a text box visible on the form, with an accompanying label which says "Enter mm/yy" or something like that.

Ok, so say your data entry text box is called 'txt1'.
make two new text boxes (which will always be invisible to the user). in the first one (called txtReportDateFrom), put

=CDate(Left([txt1],InStr([txt1],"/")-1) & "/01/" & Right([txt1],2))

in the second one (called txtReportDateTo) put

=DateAdd("m",1,[txtReportDateFrom])-1

so if someone puts in '02/05' or even '2/05', it should put into these boxes:

2/1/05 and 2/28/05

respectively.

then in your report/query, for the criteria, put

between forms!Formname!txtReportDateFrom and Forms!FormName!txtReportDateTo.

if you have dates in funky (european?) formats, you'll have to fiddle with it.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GoldenFireTiger]

Ginger R

Im in the UK, so I guess it'll need tweaking, but thanks a bunch for your help today. I wouldn't have been able to do this without your help, and it's much appreciated. One last question, how do you make the text boxes invisible?

 

[GingerR]

view the properties window. every control (text box, combo box, etc) has a property called VISIBLE. I suggest that if this new date text box is not needed for whatever is listed as the first report in your list, you set it to NO. Then in the list box's AfterUpdate event, put something like

if me.ListBoxName = "MonthlyReport" then
me.txt1.visible = true
me.lblTxt1.visible = true
me.txt1.setfocus 'puts the cursor in the box
else
me.txt1.visible = false
me.lblTxt1.visible = false
end if

also, in the button's OnClick event, you may wish to also make sure the user has put something in the box:

if me.ListBoxName = "MonthlyReport" then
if me.txt1 = "" or isnull(me.txt1) then
msgbox "Please enter a month!",vbokonly,"Error"
me.txt1.setfocus
exit sub
end if
end if

also, you'll have to make sure people enter in data in the format you want, i.e. [a number between 1 and 12, then a slash, then a 2-digit year that actually exists]. I'm sure you'll figure it out

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GoldenFireTiger]

Thanks for ur help, you've been tremendous! I'll work on it right away!

 

[GoldenFireTiger]

Hi there, can I just say thanks for your help, but I was having difficulty tweaking the code to work in the UK. However, I found a much easier way of doing this, and I didn't have to change the way the reports were launched. On the query I had, I typed between [first date (dd/mm/yy)] and [last date (dd/mm/yy)], when the query is run, a message box automatically pops up asking the user to enter the first date and last date. And all by altering a query!

 

[GingerR]

That's great! Yes that will work. But what if they don't fill in a date, but just hit the enter key? Or type in "aaaaa". Or a date that doesn't exist or is out of range? What then?!?! A report that pops up blank...and a confused user!!

My suggestion was based on my experience....it will help in the future, when users complain that they have to enter in the same date range for many reports...well now you know how to go ahead and put text boxes on the form and reference them in your query instead of asking the user for the same dates over and over! You can even default a date text box on a form to the current date, or the current months start and end dates...things like that to make it easier (and fewer mistakes) from the users. You can also validate the users' inputs PRIOR to running the report and having it error out because of bad or non-existant inputs.

Anyhow, you have the answer now!!

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GoldenFireTiger]

Thanks Ginger R, you've helped tremendously.
I'll certainly use your advice in the future, in fact I'm designing another report where it will come in very handy indeed. Thanks again!