Steve,
Great job, the code seems to working like I need it, I have further commented
the code trying to understand and document all the logic behind it. As I said
I am still new, so trying to get an understanding to how it all works.
Hopefully I am correct on the process and comments. One question I have is on
the inner and outer loops, can you explain the difference and the use of them?
Sure do appreciate all your help!
Russ
'******************************************************************************************
' For this codde to work we need a reference set to Microsoft DAO 3.6 Object
Library
'
' Step 1 We will need to set-up some recordsets. i.e. "rsDataViaCode" &
rsMASTER
' Create / Open First recordset "rsDataViaCode"
' Create / Open Second recordset "rsMASTER"
' Create / Open Third recordset "rsDataViaCode"
' What is a Recordset?
' ' A recordset represents the entire set of records from a base table or
the results
' ' of an executed command. At any time, the Recordset object refers to
only a single
' ' record within the set as the current record.
'
' Step 2 We need to add / insert new record into the "tblMaster" table
' To Accomplish this we will use the AddNew method.
' What does the AddNew method do?
' ' It tells Microsoft Access database engine to prepare a new blank
record.
' ' The new record is then made the current record
'
' Step 3 We will need to set a bookmark.
' What is a bookmark?
' ' A Bookmark sets or returns a bookmark that uniquely identifies
' ' the current record in a Recordset object.
'
' Step 4 We will need to insert records into thet "tblChild" Table.
' How do we use the insert?
' ' There are essentially two methods for adding records to a table. The
first
' ' is to add one record at a time; the second is to add many records at a
time.
' ' In both cases, you use the SQL statement INSERT INTO to accomplish the
task.
' ' INSERT INTO statements are commonly referred to as append queries.
' ' To add one record to a table, you must use the field list to define
which
' ' fields to put the data in, and then you must supply the data itself in
a
' ' value list. To define the value list, use the VALUES clause.
'
' Step 5 Ok, were done. Time to clean up, we need to close the recordsets.
' To Accomplish this we will use the Close method & Nothing Keyword.
' ' The Close method is used to Closes an open Recordset
' ' The Nothing keyword is used to disassociate an object
' ' variable from an actual object.
'******************************************************************************************
'Declares variables and allocates storage space
Dim db As DAO.Database
Dim rsDataViaCode As DAO.Recordset
Dim rsMASTER As DAO.Recordset
Dim sSQL1 As String
Dim sSQL2 As String
Dim sSQL3 As String
Dim vEmp As String ' Can be text or number.
Dim vDepart As String ' Can be text or number.
Dim vStartDate As Date ' Can only be a date.
Dim vStartTime As Date ' Can only be a date.
Dim vProc As String ' Can be text or number.
Dim rsMASTER_PK As Long ' Can only be a number.
' Set db (DAO.Database) using the CurrentDb method which returns an
' object variable of type Database that represents the database
' currently open in the Microsoft Access window.
Set db = CurrentDb
'******************************************************************************************
' Open the DataViaCode table recordset.
' Step 1) Set-up our query (sSQL1)
' Step 2) Set Recprdset "rsDataViaCode"
'******************************************************************************************
' Set-up the sSQL1 Select query.
' Here we will select all fields / records in the table.
sSQL1 = "SELECT * FROM DataViaCode"
' Set the Recordset rsDataViaCode using db.OpenRecordset which creates a
' new Recordset object and appends it to the Recordsets collection.
Set rsDataViaCode = db.OpenRecordset(sSQL1)
'******************************************************************************************
' Open the MASTER table recordset.
' Step 1) Set-up our query (sSQL2)
' Step 2) Set Recprdset "rsMASTER"
'******************************************************************************************
' Set-up the sSQL2 Select query.
' sSQL2 statement includes parameters for the query.
' Here we will select fields we want to update from the "tblMASTER" table.
sSQL2 = "SELECT ID, Emp, Department, StartDate, StartTime, Proc"
sSQL2 = sSQL2 & " FROM tblMASTER"
' Set Recordset rsMASTER using db.OpenRecordset which creates a new
' Recordset object and appends it to the Recordsets collection.
Set rsMASTER = db.OpenRecordset(sSQL2)
'******************************************************************************************
' Check for records in rsDataViaCode.
' We will use the BOF and EOF properties to determine whether a
' Recordset object contains records or whether you've gone beyond the
' limits of a Recordset object when you move from record to record.
'******************************************************************************************
' If rsDataViaCode is not at the beginning of the file or is not at
' the end of the file then continue and move to first record.
If Not rsDataViaCode.BOF And Not rsDataViaCode.EOF Then
' Here we move to the first record in our Recordset object
' and make that record the current record.
rsDataViaCode.MoveFirst
' outer loop - for tblMASTER table records
Do
vEmp = rsDataViaCode.Fields("EMP")
vDepart = rsDataViaCode.Fields("Department")
vStartDate = rsDataViaCode.Fields("StartDate")
vStartTime = rsDataViaCode.Fields("StartTime")
vProc = rsDataViaCode.Fields("Proc")
'******************************************************************************************
' Insert a new record into the ONE (tblMASTER) table.
' We will accomplish this by doing the following steps;
' 1) Use the AddNew method to create a record you can edit.
' 2) Assign values to each of the record's fields.
' 3) Use the Update method to save the new record.
'******************************************************************************************
' The With statement allows you to perform a series of statements on
a specified
' object without requalifying the name of the object. Allows us to
refer to the
' object once instead of referring to it with each property
assignment.
With rsMASTER ' Here we are referring to the object just once.
' Step 1) Use the AddNew method
' Database engine prepares a new blank record & makes it the
current record.
.AddNew 'since we used the With statement we do not have to refer
to "rsMaster"
' Step 2) Assign values to each of the record's fields.
.Fields("emp") = rsDataViaCode.Fields("EMP")
.Fields("Department") = rsDataViaCode.Fields("Department")
.Fields("StartDate") = rsDataViaCode.Fields("StartDate")
.Fields("StartTime") = rsDataViaCode.Fields("StartTime")
.Fields("Proc") = rsDataViaCode.Fields("Proc")
' Step 3) Use the Update method to save the new record.
.Update 'saves new record
' Step 4) Find the last record added.
' We will use this bookmark to move the current record pointer
' to the most recently changed or added record.
.Bookmark = .LastModified
' get the PK (Primary Key) for use in the child table
rsMASTER_PK = .Fields("ID")
End With
' inner loop for child table records
' the previous value must equal the current values
' to add related records to the child table
Do While vEmp = rsDataViaCode.Fields("EMP") And _
vDepart = rsDataViaCode.Fields("Department") And _
vStartDate = rsDataViaCode.Fields("StartDate") And _
vStartTime = rsDataViaCode.Fields("StartTime") And _
vProc = rsDataViaCode.Fields("Proc")
'******************************************************************************************
' Insert / Append a new record into the "tblChild" (MANY) table
' Use INSERT INTO This Adds a record or multiple records to a table.
This is referred
' to as an append query. Single-record append query:
'INSERT INTO target [(field1[, field2[, …]])] VALUES (value1[, value2[, …])
' TARGET
' The name of the table or query to append records to.
' VALUES
' The values to insert into the specific fields of the new record.
' Each value is inserted into the field that corresponds to the value's
' position in the list: value1 is inserted into field1 of the new record,
' value2 into field2, and so on. You must separate values with a comma, and
' enclose text fields in quotation marks (' ').
'******************************************************************************************
sSQL3 = "INSERT INTO tblChild (Proc, ItemScan, Qty, LinkFK)"
sSQL3 = sSQL3 & " VALUES ('" & rsDataViaCode.Fields("Proc") & "',
'" & rsDataViaCode.Fields("ItemScan")
sSQL3 = sSQL3 & "', " & rsDataViaCode.Fields("Qty") & ", " &
rsMASTER_PK & ") ;"
'Executes the specfied specifications in sSQL3
db.Execute sSQL3, dbFailOnError '(Runs the append query sSQL3)
' MoveNext - Moves to the next record in a specified Recordset
' object and make that record the current record
rsDataViaCode.MoveNext
' Check if at EOF (End Of File) of Recordset (rsDataViaCode)
' if at EOF (End Of File) Exit Do.
If rsDataViaCode.EOF Then
Exit Do ' Exit loop
End If
Loop ' inner
' if at EOF (End Of File) Exit Do.
If rsDataViaCode.EOF Then
Exit Do ' Exit loop
End If
Loop ' outer
End If
'******************************************************************************************
' Clean up, time to close the recordsets
' The Close Method is used to Closes an open Recordset
'******************************************************************************************
rsDataViaCode.Close
rsMASTER.Close
' The Nothing keyword is used to disassociate an object
' variable from an actual object.
Set rsDataViaCode = Nothing
Set rsMASTER = Nothing
Set db = Nothing
End Sub
I guess I didn't do so well explaining the code......
[quoted text clipped - 70 lines]
Part one the main record - [emp], [department],[date],[start time]
Part two details the record – [item scan], [qty]
