Passing Query Result to a Table 1

[samn265]

Below is a function to step through a query, one record at a time until the end of the file. I am having a trouble inserting the value of each record “ActiveEmployeeName” to a table. I do not want to append to the table. I just want to have one record in that table which is the value that is passed from the function. I tried to use Make_Table query but it did not work. Any idea of how to do this code?

Function ActiveEmployeeName() As String
Dim rstError As DAO.Recordset
Set rstError = CurrentDb.OpenRecordset("qrySelectCountofActiveEmployees&quot
Do Until rstError.EOF
'MsgBox "Last Name is: " & rstError!LastName
DoCmd.OpenQuery "qryAppendEmployeesQuery"
ActiveEmployeeName = rstError!LastName
MsgBox "Last Name is: " & ActiveEmployeeName
rstError.MoveNext
Loop
rstError.Close
Set rstError = Nothing
End Function


I appreciate your help. Thanks,
Sam

 

[smuckers]

Good evening Samn265!

I had to make a couple of assumptions or guesses in order to answer your question. I created code both to update and to append the same table, named tblTest. You stated that you had trouble inserting the value into a table. The language of SQL and DAO can be very confusing, so I did it both ways. Please use just what you need.

The Employees table was used from the Northwind.mdb database.

I created a 2 field table, named tblTest. Field one was named ID_Number with it being the Primary with Autonumbering. The second field of tblTest was named LastName being a text type field.

Here it is:
*******************************************************
[Option Compare Database
Option Explicit

Function Smuckers_ActiveEmployeeName()
' This is a Function that assigns an Active Employee name to a table.
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strLastName As String
Dim intCount As Integer

' A recordset set is created for the existing Active Employees.
' The data is taken from the Employees table of NorthWind.mdb.
Set db = CurrentDb
Set rst = db.OpenRecordset("Smuckers_qrySelectCountOfActiveEmployees&quot

' Assign each Active Employee to a table named tblTest.
rst.MoveFirst


Do Until rst.EOF
intCount = intCount + 1
strLastName = rst![LastName]

' *** Disable the Dialog boxes notifying of the update ***
DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE tblTest " & _
"SET ActiveEmployeeName = '" & strLastName & "'" & _
" WHERE ID_NUMBER = " & intCount & ";"

rst.MoveNext
Loop
DoCmd.SetWarnings True

' OK, half way there.
' Wasn't sure if you wanted to add values to the table or not
' Comment it out if you want.

DoCmd.SetWarnings False
rst.MoveFirst

Do Until rst.EOF
strLastName = rst![LastName]

DoCmd.RunSQL _
"INSERT INTO tblTest(ActiveEmployeeName)" & _
"VALUES ('" & strLastName & "')" & ";"
rst.MoveNext

Loop
DoCmd.SetWarnings True

rst.Close
db.Close

Set rst = Nothing
Set db = Nothing

End Function]
**********************************************

Good luck, I hope this helps.

Smuckers

 

[samn265]

Thank you, Smuckers, for your help. The information you have provided was very helpful.
Sam