Code for for Displaying Form is messed up

[viperbyte]

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

 

[viperbyte]

Oooops; I mis-wrote the title of this thread. It is a REPORT that I am having trouble displaying.

 

[viperbyte]

I have my F8 working. I see that the problem is that I wasn't adding .value to the end of targetRs.Fields(0). So it's MyRecordSet.Fields(0).value. I also added .value to targetRs.Fields. My problem now is that targetRs.Fields(1).value isn't good syntax. targetRs is empty. How do you guys code this so that this empty targetRs can now be used for records to be added the way I'm trying to do? Please oh please oh please.

 

[vbaInet]

I don't understand why you're needing to add records into a temp table only to display them on a report? Can this not be done in a query? Then base your report on that query.

 

[viperbyte]

This can't be done in a query. There will be additional info added conditionally that needs to be done by client side processing. There are three different possible directions to take depending on the comparison of two of the fields in the rs. Then after evaluating that, different data will be placed in a column that will go in the report. This data is not in the database and to determine what data goes in this column is complicated. This complexity isn't displayed in the shown code for brevity sake.

 

[vbaInet]

Debug.Print the sql statement and copy and paste it into a query to see whether it returns any results. If it doesn't then you know the problem lies.

 

[viperbyte]

My third message is more specific.