Create a Recordset Using AddNew
This module demonstrates how to create a recordset with VBA. It reads data from one recordset to create a second recordset.
Program Code
Option Compare Database
Option Explicit
' ***********************************************************************
' Create The Min Max Factors By Style Color
' ***********************************************************************
Public Function CreateMinMaxFactorsStyleColor()
Dim db As DAO.Database
Dim recIn As DAO.Recordset
Dim recOut As DAO.Recordset
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteMinMaxFactorsStyleColor", acViewNormal, acEdit
DoCmd.SetWarnings True
' ***********************************************************************
' Open the AIMS Files
' ***********************************************************************
Set db = CurrentDb()
Set recIn = db.OpenRecordset("tblMinMaxImport")
Set recOut = db.OpenRecordset("tblMinMaxFactorsStyleColor")
If recIn.EOF Then
recIn.Close
recOut.Close
Set recIn = Nothing
Set recOut = Nothing
Set db = Nothing
Exit Function
End If
' ***********************************************************************
' Loop Through and Create The Min Max Factor Table
' ***********************************************************************
Do
recOut.AddNew
recOut!StyleColor = recIn!Style & recIn!Color
recOut!Style = recIn!Style
recOut!Color = recIn!Color
recOut!Min = recIn!Min
recOut!Max = recIn!Max
recOut.Update
recIn.MoveNext
Loop Until recIn.EOF
recIn.Close
recOut.Close
Set recIn = Nothing
Set recOut = Nothing
Set db = Nothing
End Function
' ***********************************************************************
' Copy All Fields With One Statement
' Note: This is not a full program... just a code segment that does
' not pertain to the last example.
' ***********************************************************************
Dim strSQL As String
strSQL = "INSERT INTO LocalTransactionHistory " & _
"SELECT * FROM qryListTransactionHistory " & _
"WHERE Style='12567'"
CurrentDb.Execute strSQL, dbFailOnError