Full Version: Run a report based on a form combo box
UtterAccess Forums > Microsoft® Access > Access Forms
AmandaA
I'm new to this forum & would like to thank you for the help I've received so far.
would like to run a customer owned equipment report based on a customer selected from a combo box on a form. For instance, if the user drops down to "customerX" and then clicks a command button, a report should open showing all equipment owned by "customerX". I've created a form called "CustomerOwnedEquipment" which gets it's customer selections from a query "CustomerSort". The equipment is listed in a table called "Assets".
Can this be done?
Thanks in advance for your help!
AmandaA
freakazeud
Hi,
welcome to UA forums.
Create a query based on your table.
Then in the customer field's criteria area reference your combobox you created on the form with:
[Forms]![YourForm]![YourCombo]
How build a report based on that query. Add a button or use the after update event of the combobox on your form to open the report. It should now filter out all records applying to the selection in the combobox.
HTH
Good luck
AmandaA
freakazeud... thanks for your quick response. I'll give it a try!
manda
freakazeud
You're welcome.
Glad I could assist.
Good luck on future projects!
AmandaA
Ok, I've created a query "CustomerOwnedQuery", referenced combobox "Combo13" from "CustomerOwnedEquipment" form and built report "CustomerOwnedEqpt". Added a button to the form to open the report. Here is the error I'm receiving:
The expression On Click you entered as the event property setting produced the following error: Ambiguous name detected: ShowCustomerReport_Click.
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro."
Thanks...
Amanda
AmandaA
Well, I've eliminated the "Ambiguous name" error. Now after I select the customer from the drop-down and then click the "Preview Report" button, I receive an "Enter Parameter Value" dialog box which asks for [Forms]![CustomerOwnedEquipment]![SelectCustomer]. That is what I referenced in the customer fields criteria area.
esides that, everything is functioning great!
Thanks,
Amanda
Jack Cowley
Is the name of your control that has the CustomerID "SelectCustomer"? Check the Name property in the property sheet for you combo box to be sure that is the name of the control. What Access is telling you is that it cannot find either the Form "CustomerOwnedEquipment" or the Control "SelectCustomer".
think Mr. Freakazeud has left for the day so that is why I am jumping in....
Jack
AmandaA
Hi Jack, thanks for jumping in on this... I'm still stumped! I think this is the important information:
uery name: CustomerOwnedQuery
CustName table: Assets
CustName criteria: [Forms]![CustomerOwnedEquipment]![SelectCustomer]
Form: CustomerOwnedEquipment
ComboBox name: SelectCustomer
Row Source: CustomerSort
Command Button name: Customer Report
OnClick event: ShowReport
Report: CustOwnedEqpt
Record source: CustomerOwnedQuery
Can you help me out based on that?
Thanks!
Amanda
Jack Cowley
Amanda -
Are you using a macro as the On Click event of the command button? If so then try this:
1. Click in the On Click event and click the down arrow at the right of the field.
2. From the list select [Event Procedure].
3. To the right will be a button with 3 dots. Click that to open the code editor.
4. The code editor will open with the cursor flashing between two lines of code.
5. Type this where the cursor is flashing:
DoCmd.OpenReport "CustOwnedEqpt"
6. Close the code editor by clicking the X in the upper right corner.
7. Cross your fingers and try your code....
I have a question - If you open your form, make a selection from the combo box and without closing the form open the query.... Does the query show the data you want? If so then the form/query is working and the code above should open the report and show you the correct data, as I assume the report is based on this query.
One more hint - Use standard naming conventions for you objects as it will be easier to keep track of them... Your form CustomerOwnedEquipment would become frmCustomerOwnedEquipment. Your report would be rptCustOwnedEqpt. The combo box would be cboSelectCustomer... Start using code if you are currently using macros as code is so much more powerful and is not nearly as hard as you might think... Just a couple of suggestions for your next database...
I will be gone most of today so I hope this fixes your problem. If not, let me know as I will be here until 9am Pacific Time....
Jack
AmandaA
Hi Jack,
Thanks for the help!
Before I had a chance to test your tips, my boss was kind enough to try his hand with it. Unfortunately, he hosed up the form, query and report so now I'll need to start over!
Wish me luck!
Amanda
Jack Cowley
Amanda -
Sorry for laughing, but the expression that your boss 'hosed up the form, query and report' made me laugh out loud! I'm sorry that he did that, but that is how bosses are sometimes. Anyway, good luck with it and if you hit a wall you know were to come for help!!!
Jack
AmandaA
roflmao... thanks Jack. And yeah, I had to lol as well, that's what bosses are good at isn't it? I'll give it another shot & will definately turn to UA for help if I need it!
manda
Jack Cowley
Amanda -
Thank goodness you were able to retain your sense of humor after the boss 'hosed' all of your hard work.... Life can be cruel... Anyway, have a wonderful New Years Eve and may the coming year be free of any and all Access problems!!!
Jack
AmandaA
Jack,
ooHoo... Failure is NOT an option! I've got everything to run perfectly & totally impressed the boss. I have another question though. This is regards to the report output.
In my report, I have a column titled "Calibration Not Required" which is based on a checkbox on my Asset form. The data shows as "Yes" calibration not required or "No" calibration not required (it all makes sense on my form !).
My question is this: Can I add a function or something to display "No" if the data is Yes and "Yes" if the data is No without changing the data? I know that's confusing, but would make the report more understandable for the user.
Thanks & hope your holidays were wonderful,
Amanda
Jack Cowley
Amanda -
On your query you can add a column and in the top row of that colum put code like this:
Calibration: IIF([Checkbox] = True, "No", "Yes")
Oassume your checkbox just show as checked rather than saying "Yes"... Change the name of the Checkbox to the actual name of your checkbox and if I have the No and Yes reversed, then change them as well. Now you can use this column to display Yes or No in your report. I have assumed that the report is based on a query, which may be a bad assumption. You can still create a query or you can write code it the On Format or On Print event of the Detail section of the report.
hth,
Jack
AmandaA
Jack,
Thank you so much for all your help... it works perfectly now! Here I go to start training CPS on how to use it...
Amanda
Jack Cowley
Amanda -
You are very welcome! Enjoy your teaching job!!!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.