changing the TopValues integer

[ngan]

I have a select query called qryRandom that grabs a random number of
clientID. In that query, i set the top value to 100.

In another query called qryViewTrips, I linked the qryRandom with tblTrips
(via clientID). This query will grab all the trips of the top 100 random
clients.

If a user wants to be able to enter in the number of clients to return
randomly, how can I do that via code? For instance, one month, the user may
only want to see the top 50 vs top 75 in another month.

I know I can't code the top n in qryRandom. But I don't want the user to
have to edit the topvalues in qryRandom manually.

Thanks.

 

[Ofer]

You can do that using code that change the SQL of the query using the new TOP

Dim NewNumber As String
NewNumber = InputBox("Please select a new TOP")
Application.CurrentDb.QueryDefs("QueryName").SQL = "Select Top " &
IIf(NewNumber = "", 100, NewNumber) & " TableName.* From TableName"

' Now you can use the query with the new TOP

 

[ngan]

Thanks for the code. After I enter that part of the code, what next? I
assume doing that code changes the query qryRandom. Do I then open the
qryViewRandomTrips?

 

[Ofer]

Yes, but save the query "qryRandom" first

Now run the code to change the SQL of qryRandom query, and then run the
OpenQuery

Docmd.OpenQuery "qryViewRandomTrips"