| | |
|
|
|
Dropdown Box in a Report |
|---|
| message from =?Utf-8?B?bG5pYg==?= on 5 May 2004 |
Here's what I'm trying to do: I have a small Access database. Records equal individual shipments with unique invoice numbers.
Fields (apart from identifying ones) are yes/no checkboxes to indicate particular problems with the shipment. Shipments often have more than one problem, therefore, option boxes/toggles or drop-downs can't be used.
If a department member wants to know, say, how many shipments have been problematic due to VendorA, he/she comes to me -- I set up a simple query with the "VendorA" field set with criteria "yes", link it to a report and there you go. If he/she wants to find out about a different problem I either set up a duplicate query and change the "VendorA" field for the other problem, i.e. "Recorder Ran Out", or just modify the original query. Yes, I could set up 14 separate queries, with 14 separate linked reports, but that seems silly.
What I'm trying to do is set up a generic query (linked to a generic report) so that a department user can just go to "Problem Report", click on it and have a dialog box pop up into which she types the field name (i.e. problem) OR (best case scenario) the user sees a dropdown box and they choose which problem they want to run the "yes" query on. Access goes only to that field name, filters for "yes" and returns a report with just that problem. I have the sneaking suspicion that this is going to involve a macro or VB and is not a simple control.
Help on this would be greatly appreciated.
Lee-ann
|
| Albert D. Kallal replied to =?Utf-8?B?bG5pYg==?= on 5 May 2004 |
As you can see, putting paramters in a query can make for a lot of
prompts..and often some of the promtps you don't want.
In addtion, if you put forms refs direclty into the query..then again the
problem ariese that the query is now attached to ONE form for the prompts.
The best solution is build a nice prompt form (this is un-bound).
So, place a few check marks, combo boxes etc on this form for the users to
select/set.
You now have to write a bit code (the hard part) for the "buttion" that will
launch the report. It is with this code that you PASS the conditions to the
reprot. I find this approach about the best (exept you do have to write some
code).
So, remove all paramters from the query....make it just clean sql.
So, say you make a combo box with the vendor name (returns the vendor id).
The code looks like:
dim strWhere as string
if isnull(cboVender) = false then
strwhere = "VendorID = " & me.cboVendor
endif
' now, lets assume you field for "wasLate"
if ckWasLate = true then
if strwhere <> "" then
strWhere = strwhere & " and "
endif
strWhere = strwhere & "WasLate = True"
end if
' you can add as many contorls/prompts on the form as you wish here...
docmd.OpenRePort "yourreport",acviewPreview,,strWhere
Here is some screen shots that use the above code....
http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html
|
| =?Utf-8?B?bG5pYg==?= replied to Albert D. Kallal on 5 May 2004 |
Hi Albert -- I tried to access the page with the screenshots on it but was not allowed access (it seems that I need to be an attcanada member). I think seeing the screenshots would be helpful to me because when I read your response you indicated using a form, which I'm not -- just the report so I think if I could get a visual of the output of your suggested solution I'd know better if my problem is on the way to being solved.
Thanks for your help.
Lee-ann
|
|
Archived message: Dropdown Box in a Report (Microsoft Access Forms)
