Append query with x-y number of records (1 Viewer)

Andrlimo · Jan 6, 2017

I am trying to create an append query where there are three fields. I am able to make two of the fields work. However, I am having trouble creating another field.

I will have a form that a user will input the beginning number and the ending number and I want the append query to create that number of records with ids between those numbers.

Is there anyway to do this?

plog · Jan 6, 2017

Yes, but you are going to need VBA. As a matter of fact, you'd be best served doing this entire thing in VBA--no query object. Here's what you are going to need to do (bolded items should be googled):

Create a function in a module in Access that reads the data from your form and executes an SQL INSERT statement using DoCmd.RunSQL inside a VBA for loop that executes however many times you want.

Andrlimo · Jan 6, 2017

I was wondering if that was going to be the case. I think that I should be able to make that work. I just thought there was an easier way than VBA.

stopher · Jan 6, 2017

How many records do you typically want to add? It's actually quite easily to do this using SQL.

Andrlimo · Jan 9, 2017

Below is the code I came up with to run the append query. However, I am having an issue with variable y in my Insert into query. Every time it runs through the loop it asks for the value of y. Do I have this written correctly or is there some other way to complete this?

Stopher there is the potential to be adding at least 1000 records at a time.

Code:

Private Sub Command4_Click()
    Dim BeginCounter As Integer
    Dim EndCounter As Integer
    Dim x As Integer
    Dim y As Integer
    
    EndCounter = Forms!frm_test!Text2
    BeginCounter = Forms!frm_test!Text0
    y = BeginCounter
    
    For x = BeginCounter To EndCounter
    DoCmd.RunSQL "INSERT INTO tblTest ( datereceived, [user], id ) SELECT Now() AS [date], GetUser() AS [user], y as [id];"
    y = BeginCounter + 1
    Next


End Sub

plog · Jan 9, 2017

It's asking for y because it doesn't know what y is.

SQL is not VBA. You can write SQL inside VBA, but that SQL has no idea what's around it. So, when you simply use 'y' inside your SQL string inside a VBA script, it (the SQL) has no idea what you are talking about. Instead you need to tell the SQL what the value is you want to use. Like so:

"... GetUser() AS [user], " & y & " AS [id]..."

You must escape out of the SQL string back into VBA, use the value in y, then go back into writing the SQL string.

Andrlimo · Jan 9, 2017

Plog, Thank you for the quick reply. However, I now have another issue. I'm sure that this issue is because it is appending one record to the table at a time, but is there a way in VBA to write it so that I don't have to hit okay every time to append a record to the table.

plog · Jan 9, 2017

File-->Options-->Client Settings

Scroll down to the Confirm section and uncheck all those boxes.

Append query with x-y number of records (1 Viewer)

Andrlimo

New member

plog

Banishment Pending

Andrlimo

New member

stopher

AWF VIP

Andrlimo

New member

plog

Banishment Pending

Andrlimo

New member

plog

Banishment Pending

Similar threads

Users who are viewing this thread