Query to append x number of records from a control

[3Mark3]

Hello all,
I have a form that contains two drop down unbound controls. One of these controls selects how many records in a subform (below) that one can view. What I've been trying to figure out is, I want to create an append query to append however many records that are indicated on the drop down control. For instance, if someone selectss to view say 300 records, I would like to be able to append that number of records to another table. How can I go about this? Any help would be greatly appreciated. Thank you.

 

[hneal98]

In the properties of the query there is a Top property, you can enter the number there.

Programmatically, you would enter "Select Top 300 ..."

So you can add the 300 to a variable and put it in your query string.

hope that makes sense.

 

[3Mark3]

Thanks for your help. I'm having some trouble formatting the code so it grabs form my form. Can you suggest a modification to my code? Here it is...

INSERT INTO RBC_user_CL ( loan, fund_date, fldman )
SELECT TOP 100 RBC_main.loan, RBC_main.fund_date, RBC_main.fldman
FROM RBC_main;


Here is my code from that drop down box that decides how many loans will be displayed in the subform...

Private Sub number_select_AfterUpdate()
Dim SQL As String
Dim Num As Integer
Num = Me.number_select.Value
SQL = "SELECT TOP " & Num & " * FROM Loans_to_assign"
Me.rpt_74_subform.Form.RecordSource = SQL
End Sub

 

[dhookom]

I would base the report on a saved query and use:

Private Sub number_select_AfterUpdate()
   Dim SQL As String
   Dim Num As Integer
   Num = Me.number_select.Value
   SQL = "SELECT TOP " & Num & " * FROM Loans_to_assign"
   Currentdb.QueryDefs("qReportRecordSource").SQL = SQL
End Sub

Then open the report to see the top records. BTW: in order for this to work, you might need to add an ORDER BY to the query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[3Mark3]

Actually this would be an append query. I really wouldn't use a report. I'm basically trying to click a button so that on click function runs an append query. That append query would look at a forms drop down list to determine how many records it's going to append.

 

[3Mark3]

One more thing.....I tried inserting a popup function so the person enters the number of loans to transfer. This would also work just fine for me (I don't necessarily need to use the drop down control).

However, my code brings up an error. Can you tell me if this could work?

INSERT INTO RBC_user_CL ( loan, fund_date, fldman )
SELECT TOP [Enter Number of Loans to transfer] RBC_main.loan, RBC_main.fund_date, RBC_main.fldman
FROM RBC_main;


The error I get:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

 

[Golom]

In essence you can use a parameter only where a field name would also be valid. That means that it can't be (among other things) an operator or an SQL predicate ("DISTINCT" and "TOP n" are examples of predicates.)

You will need to construct the query as a string and then run it from code.

 

[dhookom]

Private Sub number_select_AfterUpdate()
   Dim SQL As String
   Dim Num As Integer
   Num = Me.number_select.Value
   SQL = "INSERT INTO RBC_user_CL ( loan, fund_date, fldman ) " & _
    "SELECT TOP " & Me.Number_Select & _
    " RBC_main.loan, RBC_main.fund_date, RBC_main.fldman " & _
    "FROM RBC_main;"
   Currentdb.QueryDefs("qappLoans").SQL = SQL
End Sub

Then run the query when needed. Again, I don't know what kind of results you would get with no ORDER BY. There is no "TOP" without a sort order.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]