Setting a Query Property from a Report??

[StayAtHomeMom]

Hello,
I have inherited a report that is based on a query. When this report is run (OnOpen event), it calls a macro that uses a form to prompt the user for some inputs (which are used as criteria for the underlying query). I have a new request where I have to prompt the user for how many rows the query will return (setting the Top Values Property of that query). It's used for showing "Top 50 Customers" or "Top 100 Customers", etc. How can I capture that user input to set that Query property?

OR

Is there some Report property somewhere that limits the number of records to include from query results?

Note: There's no VBA associated with this set up. It's all macros, forms, queries, reports, etc.

I'd appreciate any hints you could provide! THANK YOU!

 

[thanosgr]

You can limit the amount an SQL statement returns using TOP ie

"SELECT TOP 100 tbl_names.names
FROM tbl_names
WHERE names = 'Bob'"

The same way you are using a form to set the Where clause use the form to prompt for the amount of records the statement will return

 

[StayAtHomeMom]

Thanosgr,

I understand your SQL statement, but it's that "100" that needs to be turned into a variable so the user can determine which "TOP" they want. I tried

SELECT TOP [Forms]![F002AA Dialog]![NumbCust] ... but Access didn't like that. It said my SELECT statment had an argument name that was misspelled or missing.

Any other suggestions?

 

[MakeItSo]

I think I got what you need:

Create a basic query (e.g. Que_Top50) as record source for the report. Then implement this in the report opening button:

Dim dbs As Database, qdf As QueryDef
Dim SQLString As String, TopWhat as String

TopWhat=inputbox("Enter desired max number of hits: ")
SQLString="SELECT TOP " & TopWhat & " tbl_names.names FROM tbl_names WHERE names = 'Bob'"

DoCmd.DeleteObject acQuery, "Que_Top50"
            Set dbs = CurrentDb
            Set qdf = dbs.CreateQueryDef("Que_Top50", SQLString)

This will rewrite the query with your conditions... ;-)

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP: [URL unfurl="true"]http://home.arcor.de/andygalambos[/URL]