Hello everyone. I hope somone can help me. I'm new to Access VBA. I'm trying to get a labels report generated. For now I would be content to be able to display only two fields-description and brand in the report. If I can get this far then I'll go deeper in the processing and desision makeing as far as deciding what to put in the tempTable. For now I can't get the report to display anything. It comes back blank. The report is based off of the temp table consisting of the description and brand fields for now. Can someone please look this code over and tell me what part(s) are completly off and what's the correct way? btw F8 doesn't work for me to step through code. Please.
Option Compare Database
Private Sub Command0_Click()
Dim deleteAll As String
deleteAll = "DELETE * FROM tempTable"
DoCmd.SetWarnings False
DoCmd.RunSQL deleteAll
DoCmd.SetWarnings True
'now tempTable is empty
Dim MySQL As String
Dim openTempSql As String
Dim cnn1 As ADODB.Connection
Dim MyRecordSet As New ADODB.Recordset
Set cnn1 = CurrentProject.Connection
MyRecordSet.ActiveConnection = cnn1
openTempSql = "SELECT Description, Brand FROM tempTable"
Dim cnTempTbl As ADODB.Connection
Dim targetRs As New ADODB.Recordset
Set cnTempTbl = CurrentProject.Connection
targetRs.ActiveConnection = cnTempTbl
targetRs.Open openTempSql, , adOpenDynamic, adLockOptimistic
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
Do While Not MyRecordSet.EOF
targetRs.Fields(0) = MyRecordSet.Fields(0)
targetRs.Fields(1) = MyRecordSet.Fields(1)
targetRs.AddNew 'here I'm trying to put the first 2 fields of result set from MySQL
'into tempTable by putting then in targetRs
targetRs.Update
Loop
MyRecordSet.Close
cnn1.Close
targetRs.Close
cnTempTbl.Close
openTempSql = "SELECT Description, Brand FROM tempTable" 'here I'm loading up from
'tempTable to populate labels report
Set cnTempTbl = CurrentProject.Connection
targetRs.ActiveConnection = cnTempTbl
targetRs.Open openTempSql, , adOpenDynamic, adLockOptimistic
Report_LabelsTempTable.RecordSource = openTempSql
DoCmd.Close acReport, "LabelsTempTable", acSaveYes
DoCmd.OpenReport "LabelsTempTable", acViewReport, , , acWindowNormal
targetRs.Close
cnTempTbl.Close
'the report is blank
End Sub
Option Compare Database
Private Sub Command0_Click()
Dim deleteAll As String
deleteAll = "DELETE * FROM tempTable"
DoCmd.SetWarnings False
DoCmd.RunSQL deleteAll
DoCmd.SetWarnings True
'now tempTable is empty
Dim MySQL As String
Dim openTempSql As String
Dim cnn1 As ADODB.Connection
Dim MyRecordSet As New ADODB.Recordset
Set cnn1 = CurrentProject.Connection
MyRecordSet.ActiveConnection = cnn1
openTempSql = "SELECT Description, Brand FROM tempTable"
Dim cnTempTbl As ADODB.Connection
Dim targetRs As New ADODB.Recordset
Set cnTempTbl = CurrentProject.Connection
targetRs.ActiveConnection = cnTempTbl
targetRs.Open openTempSql, , adOpenDynamic, adLockOptimistic
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
Do While Not MyRecordSet.EOF
targetRs.Fields(0) = MyRecordSet.Fields(0)
targetRs.Fields(1) = MyRecordSet.Fields(1)
targetRs.AddNew 'here I'm trying to put the first 2 fields of result set from MySQL
'into tempTable by putting then in targetRs
targetRs.Update
Loop
MyRecordSet.Close
cnn1.Close
targetRs.Close
cnTempTbl.Close
openTempSql = "SELECT Description, Brand FROM tempTable" 'here I'm loading up from
'tempTable to populate labels report
Set cnTempTbl = CurrentProject.Connection
targetRs.ActiveConnection = cnTempTbl
targetRs.Open openTempSql, , adOpenDynamic, adLockOptimistic
Report_LabelsTempTable.RecordSource = openTempSql
DoCmd.Close acReport, "LabelsTempTable", acSaveYes
DoCmd.OpenReport "LabelsTempTable", acViewReport, , , acWindowNormal
targetRs.Close
cnTempTbl.Close
'the report is blank
End Sub
