sanders720
Programmer
I have a program that is supposed to create a table, and then append to it. I am searching fields by dates. In rsBOM, the date field is a string. In rsECO the date field is a date. In rsRH2 and rsRH3, the date field is a date. Unfortunately, the string date needs to stay this way.
1. The program is working to a point. The in rsRH2, the date field is getting populated. I enter a range from 9/1/2004 to 9/30/2004 – but the program uses 9/1 of all years to 9/30 of all years instead. The date range in the query must be wrong. Any thoughts?
sqlRH2 = "INSERT INTO tblReleasedHistory2 " & _
"SELECT tblBOM.ReleasedDate " & _
"FROM tblBOM " & _
"WHERE (((tblBOM.ReleasedDate)>= #" & Forms!frmReportsListing.txtStartDate & "# AND (tblBOM.ReleasedDate) <= #" & Forms!frmReportsListing.txtEndDate & "#))"
sqlBOM = "SELECT tblBOM.JobNo, tblBOM.SubAssy, tblBOM.PartNo, tblPartsListing.PartDescription, tblPartsListing.ManufacturedBy, " & _
"tblPartsListing.Code, tblPartsListing.RevisionLevel, tblBOM.ReleasedBy, tblBOM.ReleasedDate, tblBOM.QTY " & _
"FROM tblPartsListing INNER JOIN tblBOM ON tblPartsListing.PartNo = tblBOM.PartNo " & _
"WHERE (((tblBOM.ReleasedDate)>= '" & Forms!frmReportsListing.txtStartDate & "' AND (tblBOM.ReleasedDate) <= '" & Forms!frmReportsListing.txtEndDate & "'))"
sqlRH3 = "INSERT INTO tblReleasedHistory2 " & _
"SELECT tblECOList.ECODate " & _
"FROM tblECOList " & _
"WHERE (((tblECOList.ECODate)>= #" & Forms!frmReportsListing.txtStartDate & "# AND (tblECOList.ECODate) <= #" & Forms!frmReportsListing.txtEndDate & "#))"
2. The other problem is that only the date (ReleasedDate) field, and not the others are being populated. I don’t get it.
3. Finally the program runs to a point and then produces a runtime error – 3704 – Operation is not allowed when the object is closed. It seems to have a problem with the rsRH2.AddNew – but at the time, rsRH2 has not yet been closed.
.
.
rsRH2.AddNew
If Len(rsBOM!JobNo) > 0 Then rsRH2!JobNo = rsBOM!JobNo
If Len(rsBOM!SubAssy) > 0 Then rsRH2!SubAssy = rsBOM!SubAssy
If Len(rsBOM!PartNo) > 0 Then rsRH2!PartNo = rsBOM!PartNo
.
.
Please let me know if any help can be provided with this, and thanks in advance for the help.
Psuedo Code showing nested recordsets:
rsRH1.Open - open to create new table from old table
rsRH2.Open - open to append data into from rsBOM
rsBOM.Open - open to read existing data and place into rsRH2
rsBOM.Close
rsRH3.Open - open (similar data to rsRH2) to append data into from rsECO
rsECO.Open - open to append data from rsECO and place into rsRH3
rsECO.Close
rsRH3.Close
rsRH2.Close
rsRH1.Close
Program Code
Private Sub cmdDispRelParts_Click()
Dim rsRH1 As ADODB.Recordset
Set rsRH1 = New ADODB.Recordset
Dim sqlRH1 As String
' sqlRH1 = "SELECT tblReleasedHistory.JobNo, tblReleasedHistory.SubAssy, tblReleasedHistory.PartNo, tblReleasedHistory.PartDescription, tblReleasedHistory.ManufacturedBy, " & _
"tblReleasedHistory.Code, tblReleasedHistory.RevisionLevel, tblReleasedHistory.ReleasedBy, tblReleasedHistory.ReleasedDate, tblReleasedHistory.ECORefNum, tblReleasedHistory.NewPart, " & _
"tblReleasedHistory.Modify, tblReleasedHistory.Remake, tblReleasedHistory.QtyChg, tblReleasedHistory.SubChg, tblReleasedHistory.OldQty, tblReleasedHistory.NewQty " & _
"INTO tblReleasedHistory2 " & _
"FROM tblReleasedHistory"
' Create tblReleasedHistory2
sqlRH1 = "SELECT tblReleasedHistory.* INTO tblReleasedHistory2 FROM tblReleasedHistory"
Debug.Print "sqlRH1: " & sqlRH1
rsRH1.Open sqlRH1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Debug.Print "Table was Created..."
' BOM Comparison
Dim rsBOM As ADODB.Recordset
Set rsBOM = New ADODB.Recordset
Dim rsRH2 As ADODB.Recordset
Set rsRH2 = New ADODB.Recordset
' Append from tblBOM
Dim sqlRH2 As String
sqlRH2 = "INSERT INTO tblReleasedHistory2 " & _
"SELECT tblBOM.ReleasedDate " & _
"FROM tblBOM " & _
"WHERE (((tblBOM.ReleasedDate)>= #" & Forms!frmReportsListing.txtStartDate & "# AND (tblBOM.ReleasedDate) <= #" & Forms!frmReportsListing.txtEndDate & "#))"
Debug.Print "sqlRH2: " & sqlRH2
rsRH2.Open sqlRH2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Dim sqlBOM As String
sqlBOM = "SELECT tblBOM.JobNo, tblBOM.SubAssy, tblBOM.PartNo, tblPartsListing.PartDescription, tblPartsListing.ManufacturedBy, " & _
"tblPartsListing.Code, tblPartsListing.RevisionLevel, tblBOM.ReleasedBy, tblBOM.ReleasedDate, tblBOM.QTY " & _
"FROM tblPartsListing INNER JOIN tblBOM ON tblPartsListing.PartNo = tblBOM.PartNo " & _
"WHERE (((tblBOM.ReleasedDate)>= '" & Forms!frmReportsListing.txtStartDate & "' AND (tblBOM.ReleasedDate) <= '" & Forms!frmReportsListing.txtEndDate & "'))"
Debug.Print sqlBOM
rsBOM.Open sqlBOM, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rsBOM.MoveFirst
Do While Not rsBOM.EOF
rsRH2.AddNew
If Len(rsBOM!JobNo) > 0 Then rsRH2!JobNo = rsBOM!JobNo
If Len(rsBOM!SubAssy) > 0 Then rsRH2!SubAssy = rsBOM!SubAssy
If Len(rsBOM!PartNo) > 0 Then rsRH2!PartNo = rsBOM!PartNo
If Len(rsBOM!PartDescription) > 0 Then rsRH2!PartDescription = rsBOM!PartDescription
If Len(rsBOM!ManufacturedBy) > 0 Then rsRH2!ManufacturedBy = rsBOM!ManufacturedBy
If Len(rsBOM!Code) > 0 Then rsRH2!Code = rsBOM!Code
If Len(rsBOM!RevisionLevel) > 0 Then rsRH2!RevisionLevel = rsBOM!RevisionLevel
If Len(rsBOM!ReleasedBy) > 0 Then rsRH2!ReleasedBy = rsBOM!ReleasedBy
If Len(rsBOM!ReleasedDate) > 0 Then rsRH2!ReleasedDate = rsBOM!ReleasedDate
rsRH2!NewPart = True
If Len(rsBOM!OldQty) > 0 Then rsRH2!OldQty = 0
If Len(rsBOM!NewQty) > 0 Then rsRH2!NewQty = rsBOM!QTY
rsRH2.Update
rsBOM.MoveNext
Loop
rsBOM.Close
Set rsBOM = Nothing
' ECO Comparison
Dim rsECO As ADODB.Recordset
Set rsECO = New ADODB.Recordset
Dim rsRH3 As ADODB.Recordset
Set rsRH3 = New ADODB.Recordset
' Append from tblECOList
Dim sqlRH3 As String
sqlRH3 = "INSERT INTO tblReleasedHistory2 " & _
"SELECT tblECOList.ECODate " & _
"FROM tblECOList " & _
"WHERE (((tblECOList.ECODate)>= #" & Forms!frmReportsListing.txtStartDate & "# AND (tblECOList.ECODate) <= #" & Forms!frmReportsListing.txtEndDate & "#))"
Debug.Print "sqlRH3: " & sqlRH3
rsRH3.Open sqlRH3, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Dim sqlECO As String
sqlECO = "SELECT tblECOMain.ECORefNum, tblECOMain.JobNo, tblECOList.SubAssy, tblECOList.PartNo, tblECOList.PartDescription, tblECOList.ManufacturedBy, " & _
"tblPartsLIsting.Code, tblECOLIst.RevisionLevel, tblECOList.ECOBy, tblECOLIst.ECODate, tblECOList.NewPart, tblECOList.Modify, tblECOList.Modify, " & _
"tblECOList.Remake, tblECOList.QtyChg, tblECOList.SubChg, tblECOLIst.OldQty, tblECOList.NewQty " & _
"FROM tblECOMain INNER JOIN (tblPartsListing INNER JOIN tblECOList ON tblPartsListing.PartNo = tblECOList.PartNo) " & _
"ON tblPartsListing.PartNo = tblECOList.PartNo) ON tblECOMain.ECORefNum = tblECOList.ECORefNum " & _
"WHERE (((tblECOList.ECODate)>= #" & Forms!frmReportsListing.txtStartDate & "# AND (tblECOList.ECODate) <= #" & Forms!frmReportsListing.txtEndDate & "#))"
Debug.Print sqlECO
rsECO.Open sqlECO, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rsECO.MoveFirst
Do While Not rsECO.EOF
rsRH3.AddNew
If Len(rsECO!JobNo) > 0 Then rsRH3!JobNo = rsECO!JobNo
If Len(rsECO!SubAssy) > 0 Then rsRH3!SubAssy = rsECO!SubAssy
If Len(rsECO!PartNo) > 0 Then rsRH3!PartNo = rsECO!PartNo
If Len(rsECO!PartDescription) > 0 Then rsRH3!PartDescription = rsECO!PartDescription
If Len(rsECO!ManufacturedBy) > 0 Then rsRH3!ManufacturedBy = rsECO!ManufacturedBy
If Len(rsECO!Code) > 0 Then rsRH3!Code = rsECO!Code
If Len(rsECO!RevisionLevel) > 0 Then rsRH3!RevisionLevel = rsECO!RevisionLevel
If Len(rsECO!ReleasedBy) > 0 Then rsRH3!ReleasedBy = rsECO!ECOBy
If Len(rsECO!ReleasedDate) > 0 Then rsRH3!ReleasedDate = rsECO!ECODate
If Len(rsECO!ECORefNum) > 0 Then rsRH3!ECORefNum = rsECO!ECORefNum
rsRH3!NewPart = rsECO!NewPart
rsRH3!Modify = rsECO!Modify
rsRH3!Remake = rsECO!Remake
rsRH3!QtyChg = rsECO!QtyChg
rsRH3!SubChg = rsECO!SubChg
If Len(rsECO!OldQty) > 0 Then rsRH3!OldQty = rsECO!OldQty
If Len(rsECO!NewQty) > 0 Then rsRH3!NewQty = rsECO!NewQty
rsRH3.Update
rsECO.MoveNext
Loop
rsECO.Close
Set rsECO = Nothing
rsRH3.Close
Set rsRH3 = Nothing
rsRH2.Close
Set rsRH2 = Nothing
rsRH1.Close
Set rsRH1 = Nothing
End Sub
1. The program is working to a point. The in rsRH2, the date field is getting populated. I enter a range from 9/1/2004 to 9/30/2004 – but the program uses 9/1 of all years to 9/30 of all years instead. The date range in the query must be wrong. Any thoughts?
sqlRH2 = "INSERT INTO tblReleasedHistory2 " & _
"SELECT tblBOM.ReleasedDate " & _
"FROM tblBOM " & _
"WHERE (((tblBOM.ReleasedDate)>= #" & Forms!frmReportsListing.txtStartDate & "# AND (tblBOM.ReleasedDate) <= #" & Forms!frmReportsListing.txtEndDate & "#))"
sqlBOM = "SELECT tblBOM.JobNo, tblBOM.SubAssy, tblBOM.PartNo, tblPartsListing.PartDescription, tblPartsListing.ManufacturedBy, " & _
"tblPartsListing.Code, tblPartsListing.RevisionLevel, tblBOM.ReleasedBy, tblBOM.ReleasedDate, tblBOM.QTY " & _
"FROM tblPartsListing INNER JOIN tblBOM ON tblPartsListing.PartNo = tblBOM.PartNo " & _
"WHERE (((tblBOM.ReleasedDate)>= '" & Forms!frmReportsListing.txtStartDate & "' AND (tblBOM.ReleasedDate) <= '" & Forms!frmReportsListing.txtEndDate & "'))"
sqlRH3 = "INSERT INTO tblReleasedHistory2 " & _
"SELECT tblECOList.ECODate " & _
"FROM tblECOList " & _
"WHERE (((tblECOList.ECODate)>= #" & Forms!frmReportsListing.txtStartDate & "# AND (tblECOList.ECODate) <= #" & Forms!frmReportsListing.txtEndDate & "#))"
2. The other problem is that only the date (ReleasedDate) field, and not the others are being populated. I don’t get it.
3. Finally the program runs to a point and then produces a runtime error – 3704 – Operation is not allowed when the object is closed. It seems to have a problem with the rsRH2.AddNew – but at the time, rsRH2 has not yet been closed.
.
.
rsRH2.AddNew
If Len(rsBOM!JobNo) > 0 Then rsRH2!JobNo = rsBOM!JobNo
If Len(rsBOM!SubAssy) > 0 Then rsRH2!SubAssy = rsBOM!SubAssy
If Len(rsBOM!PartNo) > 0 Then rsRH2!PartNo = rsBOM!PartNo
.
.
Please let me know if any help can be provided with this, and thanks in advance for the help.
Psuedo Code showing nested recordsets:
rsRH1.Open - open to create new table from old table
rsRH2.Open - open to append data into from rsBOM
rsBOM.Open - open to read existing data and place into rsRH2
rsBOM.Close
rsRH3.Open - open (similar data to rsRH2) to append data into from rsECO
rsECO.Open - open to append data from rsECO and place into rsRH3
rsECO.Close
rsRH3.Close
rsRH2.Close
rsRH1.Close
Program Code
Private Sub cmdDispRelParts_Click()
Dim rsRH1 As ADODB.Recordset
Set rsRH1 = New ADODB.Recordset
Dim sqlRH1 As String
' sqlRH1 = "SELECT tblReleasedHistory.JobNo, tblReleasedHistory.SubAssy, tblReleasedHistory.PartNo, tblReleasedHistory.PartDescription, tblReleasedHistory.ManufacturedBy, " & _
"tblReleasedHistory.Code, tblReleasedHistory.RevisionLevel, tblReleasedHistory.ReleasedBy, tblReleasedHistory.ReleasedDate, tblReleasedHistory.ECORefNum, tblReleasedHistory.NewPart, " & _
"tblReleasedHistory.Modify, tblReleasedHistory.Remake, tblReleasedHistory.QtyChg, tblReleasedHistory.SubChg, tblReleasedHistory.OldQty, tblReleasedHistory.NewQty " & _
"INTO tblReleasedHistory2 " & _
"FROM tblReleasedHistory"
' Create tblReleasedHistory2
sqlRH1 = "SELECT tblReleasedHistory.* INTO tblReleasedHistory2 FROM tblReleasedHistory"
Debug.Print "sqlRH1: " & sqlRH1
rsRH1.Open sqlRH1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Debug.Print "Table was Created..."
' BOM Comparison
Dim rsBOM As ADODB.Recordset
Set rsBOM = New ADODB.Recordset
Dim rsRH2 As ADODB.Recordset
Set rsRH2 = New ADODB.Recordset
' Append from tblBOM
Dim sqlRH2 As String
sqlRH2 = "INSERT INTO tblReleasedHistory2 " & _
"SELECT tblBOM.ReleasedDate " & _
"FROM tblBOM " & _
"WHERE (((tblBOM.ReleasedDate)>= #" & Forms!frmReportsListing.txtStartDate & "# AND (tblBOM.ReleasedDate) <= #" & Forms!frmReportsListing.txtEndDate & "#))"
Debug.Print "sqlRH2: " & sqlRH2
rsRH2.Open sqlRH2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Dim sqlBOM As String
sqlBOM = "SELECT tblBOM.JobNo, tblBOM.SubAssy, tblBOM.PartNo, tblPartsListing.PartDescription, tblPartsListing.ManufacturedBy, " & _
"tblPartsListing.Code, tblPartsListing.RevisionLevel, tblBOM.ReleasedBy, tblBOM.ReleasedDate, tblBOM.QTY " & _
"FROM tblPartsListing INNER JOIN tblBOM ON tblPartsListing.PartNo = tblBOM.PartNo " & _
"WHERE (((tblBOM.ReleasedDate)>= '" & Forms!frmReportsListing.txtStartDate & "' AND (tblBOM.ReleasedDate) <= '" & Forms!frmReportsListing.txtEndDate & "'))"
Debug.Print sqlBOM
rsBOM.Open sqlBOM, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rsBOM.MoveFirst
Do While Not rsBOM.EOF
rsRH2.AddNew
If Len(rsBOM!JobNo) > 0 Then rsRH2!JobNo = rsBOM!JobNo
If Len(rsBOM!SubAssy) > 0 Then rsRH2!SubAssy = rsBOM!SubAssy
If Len(rsBOM!PartNo) > 0 Then rsRH2!PartNo = rsBOM!PartNo
If Len(rsBOM!PartDescription) > 0 Then rsRH2!PartDescription = rsBOM!PartDescription
If Len(rsBOM!ManufacturedBy) > 0 Then rsRH2!ManufacturedBy = rsBOM!ManufacturedBy
If Len(rsBOM!Code) > 0 Then rsRH2!Code = rsBOM!Code
If Len(rsBOM!RevisionLevel) > 0 Then rsRH2!RevisionLevel = rsBOM!RevisionLevel
If Len(rsBOM!ReleasedBy) > 0 Then rsRH2!ReleasedBy = rsBOM!ReleasedBy
If Len(rsBOM!ReleasedDate) > 0 Then rsRH2!ReleasedDate = rsBOM!ReleasedDate
rsRH2!NewPart = True
If Len(rsBOM!OldQty) > 0 Then rsRH2!OldQty = 0
If Len(rsBOM!NewQty) > 0 Then rsRH2!NewQty = rsBOM!QTY
rsRH2.Update
rsBOM.MoveNext
Loop
rsBOM.Close
Set rsBOM = Nothing
' ECO Comparison
Dim rsECO As ADODB.Recordset
Set rsECO = New ADODB.Recordset
Dim rsRH3 As ADODB.Recordset
Set rsRH3 = New ADODB.Recordset
' Append from tblECOList
Dim sqlRH3 As String
sqlRH3 = "INSERT INTO tblReleasedHistory2 " & _
"SELECT tblECOList.ECODate " & _
"FROM tblECOList " & _
"WHERE (((tblECOList.ECODate)>= #" & Forms!frmReportsListing.txtStartDate & "# AND (tblECOList.ECODate) <= #" & Forms!frmReportsListing.txtEndDate & "#))"
Debug.Print "sqlRH3: " & sqlRH3
rsRH3.Open sqlRH3, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Dim sqlECO As String
sqlECO = "SELECT tblECOMain.ECORefNum, tblECOMain.JobNo, tblECOList.SubAssy, tblECOList.PartNo, tblECOList.PartDescription, tblECOList.ManufacturedBy, " & _
"tblPartsLIsting.Code, tblECOLIst.RevisionLevel, tblECOList.ECOBy, tblECOLIst.ECODate, tblECOList.NewPart, tblECOList.Modify, tblECOList.Modify, " & _
"tblECOList.Remake, tblECOList.QtyChg, tblECOList.SubChg, tblECOLIst.OldQty, tblECOList.NewQty " & _
"FROM tblECOMain INNER JOIN (tblPartsListing INNER JOIN tblECOList ON tblPartsListing.PartNo = tblECOList.PartNo) " & _
"ON tblPartsListing.PartNo = tblECOList.PartNo) ON tblECOMain.ECORefNum = tblECOList.ECORefNum " & _
"WHERE (((tblECOList.ECODate)>= #" & Forms!frmReportsListing.txtStartDate & "# AND (tblECOList.ECODate) <= #" & Forms!frmReportsListing.txtEndDate & "#))"
Debug.Print sqlECO
rsECO.Open sqlECO, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rsECO.MoveFirst
Do While Not rsECO.EOF
rsRH3.AddNew
If Len(rsECO!JobNo) > 0 Then rsRH3!JobNo = rsECO!JobNo
If Len(rsECO!SubAssy) > 0 Then rsRH3!SubAssy = rsECO!SubAssy
If Len(rsECO!PartNo) > 0 Then rsRH3!PartNo = rsECO!PartNo
If Len(rsECO!PartDescription) > 0 Then rsRH3!PartDescription = rsECO!PartDescription
If Len(rsECO!ManufacturedBy) > 0 Then rsRH3!ManufacturedBy = rsECO!ManufacturedBy
If Len(rsECO!Code) > 0 Then rsRH3!Code = rsECO!Code
If Len(rsECO!RevisionLevel) > 0 Then rsRH3!RevisionLevel = rsECO!RevisionLevel
If Len(rsECO!ReleasedBy) > 0 Then rsRH3!ReleasedBy = rsECO!ECOBy
If Len(rsECO!ReleasedDate) > 0 Then rsRH3!ReleasedDate = rsECO!ECODate
If Len(rsECO!ECORefNum) > 0 Then rsRH3!ECORefNum = rsECO!ECORefNum
rsRH3!NewPart = rsECO!NewPart
rsRH3!Modify = rsECO!Modify
rsRH3!Remake = rsECO!Remake
rsRH3!QtyChg = rsECO!QtyChg
rsRH3!SubChg = rsECO!SubChg
If Len(rsECO!OldQty) > 0 Then rsRH3!OldQty = rsECO!OldQty
If Len(rsECO!NewQty) > 0 Then rsRH3!NewQty = rsECO!NewQty
rsRH3.Update
rsECO.MoveNext
Loop
rsECO.Close
Set rsECO = Nothing
rsRH3.Close
Set rsRH3 = Nothing
rsRH2.Close
Set rsRH2 = Nothing
rsRH1.Close
Set rsRH1 = Nothing
End Sub
