Make New Table Query requiring user input

[KevW]

I have been asked to design a query that when run will ask the user how many records they require to be selected and apended to a new table. The total number of records in the database that the query will be based on will vary, as will the total number of records required in the new table each time the query is run. Once I have designed the query I intend to then use a command button on a form fro the user to run it.

 

[ted.martin]

Not clear on what you need to do. Having got the response to the number of records to be appended, where is this value used? If the value is (say) 20, is it the first 20 records to be appended? If not - what is the relevance of the need to ask for the number of records?

 

[KevW]

Selecting required number

It would not matter if it was the first 100 records or the last 100 records. Did suggest that the user would be better with a random sample but they are happy to have the first 100 records or whatever the amount they require. this amount thuogh will vary from each time the data is requested.

So what should happen is the user selects a command button.

A message box is displayed asking how many records are required.

A query is run creating a new table containing this number of records.

 

[DCrake]

Simple Software Solutions

Hi

Think I know what you mean?

You have a table that contains lets say 1000 records, the user want to run a query based on this table, but does not want to run it with all 1000 records, and to this effect they are prompted to supply the number of records they want to sample.

Dim iWant As Integer
Dim strSQL As String

iWant = InputBox(.....)

strSQL = "SELECT TOP " & CStr(iWant) & " FROM MyTable;"

If so, then there is an easy solution, use the QueryDefs object to change the SQL statement of the underlying query so that it changes




Sample Code:

Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Employee List")
qryTest.SQL = strSQL

To summarise.

The user provides a value
This is parsed in a string to create a valid SQL statement
This string is then used to recreate the SQL statement for the query
The user runs the query.

You can also use PERCENTS as well as numbers, so the user could enter a value as a percentage amount, the strSQL would read:-

strSQL = "SELECT TOP 10 PERCENT FROM MyTable;"


Code Master