Good morning all. I'm stuck. I'm trying to copy some data from a result set into a temp table that is created on the fly. After I coded this and tried to run it I get an error dialog box pop up that says: Compile error: Method or data member not found. .cnn1 is highlighed with the error. These are my references, Visual Basic For Applications, Microsoft Access 12.0 Object Library and Microsoft ActiveX Data Objects 2.8 Library. Can someone please give me a hand with this?
Private Sub Command0_Click()
Dim MySQL As String
Dim CreateSql As String
Dim cnn1 As ADODB.Connection
Dim MyRecordSet As New ADODB.Recordset
Set cnn1 = CurrentProject.Connection
''''here .cnn1 is highlighed with the error
MyRecordSet.cnn1 = cnn1
Dim cnTempTbl As ADODB.Connection
Dim targetRs As New ADODB.Recordset
Set cnTempTbl = CurrentProject.Connection
targetRs.cnTempTbl = cnTempTbl
targetRs.Open CreateSql, , adOpenDynamic, adLockOptimistic
CreateSql = "CREATE TABLE tempTable ([Description] text (255), [Brand] text (255))"
DoCmd.SetWarnings False
DoCmd.RunSQL CreateSql
DoCmd.SetWarnings True
MySQL = "SELECT [Inventory Master].Description, [Inventory Master].Brand, [Inventory Master].[Special Order Item], [Inventory Master].[Vendor Item Number], [Inventory Master].[Item Key], [Inventory Master].Par, [Vendor Master].[Vendor Key], [Vendor Master].[Vendor Name], [Vendor Sales Items].[PO Number], [Vendor Sales Items].[Qty Ordered], [Vendor Sales Items].[Vendor Key], [Vendor Sales Orders].[PO Number], [Vendor Sales Orders].Status, [Vendor Sales Orders].[Received Date], [Back Orders].ID, [Back Orders].[Customer Key], [Back Orders].[BO Quantity], [Back Orders].[Order Date], [Back Orders].[Item Key], [Vendor Sales Orders].[Vendor Delivery Date]"
MySQL = MySQL + " FROM (([Inventory Master] INNER JOIN [Vendor Master] ON [Inventory Master].[Vendor Key]=[Vendor Master].[Vendor Key]) INNER JOIN ([Vendor Sales Items] INNER JOIN [Vendor Sales Orders] ON [Vendor Sales Items].[PO Number]=[Vendor Sales Orders].[PO Number]) ON ([Vendor Master].[Vendor Key]=[Vendor Sales Items].[Vendor Key]) AND ([Inventory Master].[Item Key]=[Vendor Sales Items].[Item Key]) AND ([Vendor Master].[Vendor Key]=[Vendor Sales Orders].[Vendor Key])) LEFT JOIN [Back Orders] ON [Inventory Master].[Item Key]=[Back Orders].[Item Key]"
MySQL = MySQL + " WHERE ((([Inventory Master].[Special Order Item]) = True) And (([Inventory Master].[Vendor Delivery Date]) = #8/30/2019#) And (([Inventory Master].Par) = 0) And (([Vendor Sales Orders].Status) = 'Placed') And (([Vendor Sales Orders].[Vendor Delivery Date]) = #8/30/2019#)) "
MySQL = MySQL + " ORDER BY [Vendor Master].[Vendor Name], [Vendor Sales Items].[PO Number]"
MyRecordSet.Open MySQL, , adOpenDynamic, adLockOptimistic
Dim targetRs As ADODB.Recordset
Do While Not MyRecordSet.EOF
targetRs.Fields(0) = MyRecordSet.Fields(0)
targetRs.Fields(1) = MyRecordSet.Fields(1)
targetRs.AddNew
targetRs.Update
Loop
Report_LabelsTempTable.RecordSource = MySQL
DoCmd.Close acReport, "LabelsTempTable", acSaveYes
DoCmd.OpenReport "LabelsTempTable", acViewReport, , , acWindowNormal
End Sub
Private Sub Command0_Click()
Dim MySQL As String
Dim CreateSql As String
Dim cnn1 As ADODB.Connection
Dim MyRecordSet As New ADODB.Recordset
Set cnn1 = CurrentProject.Connection
''''here .cnn1 is highlighed with the error
MyRecordSet.cnn1 = cnn1
Dim cnTempTbl As ADODB.Connection
Dim targetRs As New ADODB.Recordset
Set cnTempTbl = CurrentProject.Connection
targetRs.cnTempTbl = cnTempTbl
targetRs.Open CreateSql, , adOpenDynamic, adLockOptimistic
CreateSql = "CREATE TABLE tempTable ([Description] text (255), [Brand] text (255))"
DoCmd.SetWarnings False
DoCmd.RunSQL CreateSql
DoCmd.SetWarnings True
MySQL = "SELECT [Inventory Master].Description, [Inventory Master].Brand, [Inventory Master].[Special Order Item], [Inventory Master].[Vendor Item Number], [Inventory Master].[Item Key], [Inventory Master].Par, [Vendor Master].[Vendor Key], [Vendor Master].[Vendor Name], [Vendor Sales Items].[PO Number], [Vendor Sales Items].[Qty Ordered], [Vendor Sales Items].[Vendor Key], [Vendor Sales Orders].[PO Number], [Vendor Sales Orders].Status, [Vendor Sales Orders].[Received Date], [Back Orders].ID, [Back Orders].[Customer Key], [Back Orders].[BO Quantity], [Back Orders].[Order Date], [Back Orders].[Item Key], [Vendor Sales Orders].[Vendor Delivery Date]"
MySQL = MySQL + " FROM (([Inventory Master] INNER JOIN [Vendor Master] ON [Inventory Master].[Vendor Key]=[Vendor Master].[Vendor Key]) INNER JOIN ([Vendor Sales Items] INNER JOIN [Vendor Sales Orders] ON [Vendor Sales Items].[PO Number]=[Vendor Sales Orders].[PO Number]) ON ([Vendor Master].[Vendor Key]=[Vendor Sales Items].[Vendor Key]) AND ([Inventory Master].[Item Key]=[Vendor Sales Items].[Item Key]) AND ([Vendor Master].[Vendor Key]=[Vendor Sales Orders].[Vendor Key])) LEFT JOIN [Back Orders] ON [Inventory Master].[Item Key]=[Back Orders].[Item Key]"
MySQL = MySQL + " WHERE ((([Inventory Master].[Special Order Item]) = True) And (([Inventory Master].[Vendor Delivery Date]) = #8/30/2019#) And (([Inventory Master].Par) = 0) And (([Vendor Sales Orders].Status) = 'Placed') And (([Vendor Sales Orders].[Vendor Delivery Date]) = #8/30/2019#)) "
MySQL = MySQL + " ORDER BY [Vendor Master].[Vendor Name], [Vendor Sales Items].[PO Number]"
MyRecordSet.Open MySQL, , adOpenDynamic, adLockOptimistic
Dim targetRs As ADODB.Recordset
Do While Not MyRecordSet.EOF
targetRs.Fields(0) = MyRecordSet.Fields(0)
targetRs.Fields(1) = MyRecordSet.Fields(1)
targetRs.AddNew
targetRs.Update
Loop
Report_LabelsTempTable.RecordSource = MySQL
DoCmd.Close acReport, "LabelsTempTable", acSaveYes
DoCmd.OpenReport "LabelsTempTable", acViewReport, , , acWindowNormal
End Sub