How to change Top Values with form input?

[tlew19]

How can I return a specified number of records in query based on a number that a user inputs into a form? I'm trying to generate a random sample of records. I know how to do this with the Top Value function, however this has to be manually changed everytime. Long story short I have a lot of queries that need this so manually changing the Top Value would not be ideal.

Does anyone know how to change the Top Values number based on a form input? or is there another way to do this?

Lets assume the object names are:

Query1
Form1
FormInputBox1

[Micron]

vba code.
Build the sql and concatenate the form field number in to it.
Run/execute the sql or whatever it is you need to do with it.

If you have a form, why do you need an input box also? I started to post a bit of an example, but can't tell for sure if there is an input box or not because that is an actual type of input all on its own.

based on a number that a user inputs into a form

[tlew19]

My bad, it would just be a form field.

[Micron]

Assuming form has a button to initiate this:

Private Sub SomeButton_Click()
Dim sql As String

If Not IsNumeric(Me.txtBoxName) Or Me.txtBoxName = 0 Then
  msbgbox "Value must be a number and cannot be zero"
  exit sub
End If

If Not Nz(Me.txtBoxName,"") = "" Then 
  sql = "SELECT TOP " & Me.txtBoxName & " tbl1.Field1, tbl1.Field2 FROM..."
  '** now do what?? 
End If

End Sub

** I have to assume you have this part covered because you still didn't say what you want to do with the sql.
Often you can just get a form value in a query field by referencing the form and control. However, in this case the value is not being assigned to a field but to a query property.

[orange]

You may get some ideas from this post.

[tlew19]

** I have to assume you have this part covered because you still didn't say what you want to do with the sql.

Thank you for your help Micron. I am not a programmer so I don't understand a lot of this.
Ultimately I am trying to return a random sample of records based on criteria. I have 20+ queries that all return different samples based on different criteria. Often times the number of records returned needs to be adjusted for different reasons.
I would like to have a form that I can say return 10 random records from query1, 5 from query2, etc.

If I already have the queries working like I want them to, can I somehow call the query with the code you wrote and still be able to change the Top Value property with the form input?

[Micron]

See if link helps you or not.
You have several select queries that are different because they act on different fields and/or tables? Or are they just different because the TOP value is different for each, otherwise the query would be the same? The latter is much simpler.

You will have to be very specific for me and patient. I had hand surgery and find typing is a pain with a cast on. Will help as much as I can but may take a bit longer than usual.

[June7]

The TOP N parameter cannot be dynamic in a saved query object. VBA code will be required.

[Micron]

The TOP N parameter cannot be dynamic in a saved query object. VBA code will be required.

That's what

vba code.
Build the sql and concatenate the form field number in to it.
Run/execute the sql or whatever it is you need to do with it.

and

Often you can just get a form value in a query field by referencing the form and control. However, in this case the value is not being assigned to a field but to a query property

was supposed to imply.

[tlew19]

Thanks guys. I was able to complete this using vba code but its still a little buggy. I'll work on refining it.