Solved Duplicate multiple records AND their subrecords (1 Viewer)

[AJGarriton]

All of your help is so valuable! This has certainly been an education for me.

I am still getting an error. The error now is 3061, expecting 2. I put the location of the error in red text below. I have double checked all the spelling of my fields. I'm using the csql function as provided above. My query only has 1 parameter which I do define below.

Again, thank you all so much for the help!!

Here is my latest 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 Product As String
Dim MBRStatus As String
Dim Dating As String
Dim StudyStatus As String
Dim StudyLot As String
Dim Distributor As String
Dim Manufacturer As String
Dim SupplierNDC As String
Dim Method As String
Dim Lab As String
Dim BlisterMaterial As String
Dim FormTool As String
Dim SealTool As String
Dim QuotedIntervalCost As String
Dim SpecialInstructions As String
Dim QtySmpPerInterval As String
Dim Bracketed As String
Dim GenericPC As String
Dim ProductCode As String
Dim FamilyID As String
Dim DateCreated As String
Dim MBRRev As String
Dim QuotedConsumables As String

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

Product = CSql(rs!Product)
MBRStatus = "Active"
Dating = CSql(rs!Dating)
StudyStatus = "Scheduled"
StudyLot = CSql(NYear)
Distributor = CSql(rs!Distributor)
Manufacturer = CSql(rs!Manufacturer)
SupplierNDC = CSql(rs!SupplierNDC)
Method = CSql(rs!Method)
Lab = CSql(rs!Lab)
BlisterMaterial = CSql(rs!BlisterMaterial)
FormTool = CSql(rs!FormTool)
SealTool = CSql(rs!SealTool)
QuotedIntervalCost = CSql(rs!QuotedIntervalCost)
SpecialInstructions = CSql(rs!SpecialInstructions)
QtySmpPerInterval = CSql(rs!QtySmpPerInterval)
Bracketed = CSql(rs!Bracketed)
GenericPC = CSql(Left(rs!GenericPC, 5))
ProductCode = CSql(rs!ProductCode)
FamilyID = CSql(rs!FamilyID)
DateCreated = CSql(Date)
MBRRev = CSql(rs!MBRRev)
QuotedConsumables = CSql(rs!QuotedConsumables)


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 (" & Product & "," & MBRStatus & "," & Dating & "," & StudyStatus & "," & StudyLot & "," & Distributor & "," & Manufacturer & "," & SupplierNDC & "," & Method & "," & Lab & "," & BlisterMaterial & "," & FormTool & "," & SealTool & "," & QuotedIntervalCost & "," & SpecialInstructions & "," & QtySmpPerInterval & "," & Bracketed & "," & GenericPC & "," & ProductCode & "," & FamilyID & "," & DateCreated & "," & MBRRev & "," & 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

rs.Close
Set rs = Nothing
rsChild.Close
Set rsChild = Nothing

End Sub

 

[MajP]

please put a
debug.print strSql
before the execute so we can see how the string resolves.

 

[AJGarriton]

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 ('Gabapentin Capsules USP 400 mg',Active,'9 months',Scheduled,'A 22','Amneal','Amneal','65162-103-50','Current USP','Midwest','10 mil PVC','125-106','227-030',2452.5,'None','20','Yes','00782',8342,69,#06/03/2021#,'8',0)

You just help me answer my own question and taught me how to use that Debug.print thing
Problem was I'm assigning Active and Scheduled to 2 fields. I had those in quotes, but needed single quoted inside.

I corrected the problem and my code has run without errors.
Thank you all so much, especially MajP!

 

[MajP]

Mbrstatus = "'active'"
Same with studystatus

 

[MajP]

" ' text ' "
Literals have to be surrounded too.

 

[AJGarriton]

got it!!!

thanks again