Your help has been invaluable. I now have most of the cost working for my specifics, however, I'm getting an error I can't seem to identify and correct.
Here is my actual code"
Public Sub DuplicateLots()
Dim strSQL As String
Dim strSQL2 As String
Dim rs As DAO.Recordset
Dim rsChild As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim qdfChild As DAO.QueryDef
Dim OldID
Dim NewID
Dim ActualYear As Integer
Dim NextYear As Integer
Dim AYear As String
Dim NYear As String
Dim SpecStatus As String
Dim StudyStatus As String
Dim Blanks As String
SpecStatus = "Active"
StudyStatus = "Scheduled"
Blanks = ""
ActualYear = Format(Now(), "YY")
NextYear = ActualYear + 1
AYear = "A" & Str(ActualYear)
NYear = "A" & Str(NextYear)
Set qdf = CurrentDb.QueryDefs("CreateAnnualStudyforVBA")
qdf.Parameters("[ActualYear]").Value = AYear
Set rs = qdf.OpenRecordset
Do While Not rs.EOF
strSQL = "INSERT INTO Studies (Product, MBRStatus, Dating, StudyStatus, StudyLot, Distributor, Manufacturer, SupplierNDC, Method, Lab, BlisterMaterial, FormTool, SealTool, QuotedIntervalCost, SpecialInstructions, QtySmpPerInterval, Bracketed, GenericPC, ProductCode, FamilyID, DateCreated, MBRRev, QuotedConsumables ) values ('" & rs!Product & "','" & SpecStatus & "','" & rs!Dating & "','" & StudyStatus & "','" & NYear & "','" & rs!Distributor & "' ,'" & rs!Manufacturer & "' ,'" & rs!SupplierNDC & "' ,'" & rs!Method & "' ,'" & rs!Lab & "' ,'" & rs!BlisterMaterial & "' ,'" & rs!FormTool & "' ,'" & rs!SealTool & "' ,'" & rs!QuotedIntervalCost & "' ,'" & rs!SpecialInstructions & "' ,'" & rs!QtySmpPerInterval & "' ,'" & rs!Bracketed & "' ,'" & Left(rs!GenericPC, 5) & "' ,'" & rs!ProductCode & "' ,'" & rs!FamilyID & "' ,'" & Date & "' ,'" & rs!MBRRev & "' ,'" & rs!QuotedConsumables & "')"
CurrentDb.Execute strSQL
OldID = rs![StudyID]
NewID = DMax("[StudyID]", "Studies")
Set qdfChild = CurrentDb.QueryDefs("CreateAnnualIntervals")
qdfChild.Parameters("[OldID]").Value = OldID
Set rsChild = qdfChild.OpenRecordset
Do While Not rsChild.EOF
strSQL2 = "Insert into StudyIntervalData (StudyIDLink, Timepoint) Values ('" & NewID & "','" & rsChild!TimePoint & "')"
CurrentDb.Execute strSQL2
rsChild.MoveNext
Loop
rs.MoveNext
Loop
End Sub
The error is Run Time error 3075. The error starts with the field rs!Distributor. I first thought the issue was this field could contain an ' or .. So, I modified my query to remove ',.,( and )… trying to cover all bases. However, I am still getting the error,
Any thoughts? Could hyphens in that field cause a problem?
Again, I certainly appreciate all the help!!!