Dropdown Box in a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Back
Top