VBA - Select certain entries and paste contents into another table

[zipaway]

Hello, using VBA I would like to select entries in a table where a certain criteria is met. I would then like a count of those entries.

Those selected entries would then be pasted into another table but not all of the columns, only a select few.



How would I go about doing this? Would I need to store the entries as a list and then populate the other table using a for statement and DAO code similar to the one below?

Dim rstProjects As DAO.Recordset
Dim dbTable As DAO.Database
Dim pnumber As Integer
pnumber = Text198.Value
        
Set dbTable = CurrentDb
Set rstChanges = dbTable.OpenRecordset("Active Projects")
rstChanges.AddNew
rstChanges("Project_Number").Value = pnumber
rstChanges.Update

[ranman256]

Select the items on a form, the execute an append query.
One way would be to allow users to click from a list. The action is an append query to the target table.

[zipaway]

I don't want the user to be able to select which items, the items will always be the same.

[AnandKhinvasara]

Insert into table2
select column1, column2
where column1 = <criteria>

[AnandKhinvasara]

In VBA you can assign the SQL to a string, and then fire it using docmd.runsql function.
Docmd.RunSQL strQuery

[zipaway]

Insert into table2
select column1, column2
where column1 = <criteria>

Ok thanks, I will give this a try.

[AnandKhinvasara]

To be very specific, heres the right syntax. The column names in Insert statement should match with select statement. Here you can only mention the columns you actually want to add data to. Hope this helps !

INSERT INTO Employees ([First Name], [LAst Name], [Employee Code])
SELECT FirstName, LastName, RefCode
FROM Candidates WHERE InterviewPassed = TRUE;

[zipaway]

To be very specific, heres the right syntax. The column names in Insert statement should match with select statement. Here you can only mention the columns you actually want to add data to. Hope this helps !

INSERT INTO Employees ([First Name], [LAst Name], [Employee Code])
SELECT FirstName, LastName, RefCode
FROM Candidates WHERE InterviewPassed = TRUE;

Ok thanks so much I kinda have this working.

Now lets say the Employees have a project number assigned to them and I have the project number in my vba code that also needs to be inserted.

I attempted at doing that but I don't think I've done it right

SQL = "INSERT INTO Employees ([First Name], [Last Name], [Employee Code], [Project Number))" & _
"SELECT FirstName, LastName, RefCode," & Project_Number & _
"FROM Candidates WHERE InterviewPassed = TRUE;"

[AnandKhinvasara]

"SELECT FirstName, LastName, RefCode," & Project_Number & _

above will work if [project number] of employees table is actually a number field.

If not use,

"SELECT FirstName, LastName, RefCode,'" & Project_Number & "'" & _

[zipaway]

Thank you so much for your help AnandKhinvasara, I finally got this working.

[AnandKhinvasara]

Good to know )