Access VBA export one row of table to excel spreadsheet, not entire table

Question

I'm attempting to write a loop in VBA for Access 2010, where the loop looks through a table (table: "SunstarAccountsInWebir_SarahTest") and evaluates a number of conditions, and depending on the condition - may then loop through a different table ("1042s_FinalOutput_7") to see if it has an ID that matches. If it does match, it inserts "Test" into a field, if not - it should export that row of values (from the first loop - out of "SunstarAccountsInWebir_SarahTest") into an excel file.

My issue is that my code is exporting the entirety of the table "SunstarAccountsInWebir_SarahTest", I only want it to export the row corresponding to the value of i in the loop. How can I amend my code to do this?

Public Sub EditFinalOutput2()

'set loop variables
Dim i As Long
Dim qs As DAO.Recordset
Dim ss As DAO.Recordset
Dim strSQL As String
Dim external_nmad_id As String
Dim IRSfileFormatKey As String
'Function GetID(external_nmad_id As String, IRSfileFormatKey As String)

'open reference set
Set db = CurrentDb
Set qs = db.OpenRecordset("SunstarAccountsInWebir_SarahTest")
Set ss = db.OpenRecordset("1042s_FinalOutput_7")

'set loop for whole recordset(this is the original location, will try putting it within the If, ElseIf loop)
'For i = 0 To qs.RecordCount - 1

    With qs.Fields
    For i = 0 To qs.RecordCount - 1

        If (IsNull(!nmad_address_1) Or (!nmad_address_1 = !nmad_city) Or (!nmad_address_1 = !Webir_Country) And IsNull(!nmad_address_2) Or (!nmad_address_2 = !nmad_city) Or (!nmad_address_2 = !Webir_Country) And IsNull(!nmad_address_3) Or (!nmad_address_3 = !nmad_city) Or (!nmad_address_3 = !Webir_Country)) Then
        MsgBox "This was an invalid address"

        Else:
        With ss.Fields
                For j = 0 To ss.RecordCount - 1

                If (qs.Fields("external_nmad_id") = Right(ss.Fields("IRSfileFormatKey"), 10)) Then
                        ss.Edit
                        ss.Fields("box13_Address") = "Test"
                        ss.Update

                Else: DoCmd.TransferSpreadsheet acExport, 10, "SunstarAccountsInWebir_SarahTest", "\\DTCHYB-MNMH001\C_WBGCTS_Users\U658984\My Documents\pre processor\PreProcessor7\ToBeReviewed\AddressesNotActiveThisYear.xlsx", False

                End If

                ss.MoveNext
                Next j

                End With

        End If

    qs.MoveNext
    Next i

End With

'close reference set
qs.Close
Set qs = Nothing
ss.Close
Set ss = Nothing

End Sub

Answer 1

This ended up being the closest. I needed to switch to a "Do While" loop rather than a second integer loop. The code for so is below:Public Sub EditFinalOutput2()

'set variables
Dim i As Long
Dim qs As DAO.Recordset
Dim ss As DAO.Recordset
Dim strSQL As String
Dim external_nmad_id As String
Dim IRSfileFormatKey As String
Dim mytestwrite As String
mytestwrite = "No"

'open reference set
Set db = CurrentDb
Set qs = db.OpenRecordset("SunstarAccountsInWebir_SarahTest")
Set ss = db.OpenRecordset("1042s_FinalOutput_7")

    With qs.Fields
    For i = 0 To qs.RecordCount - 1

        If (IsNull(!nmad_address_1) Or (!nmad_address_1 = !nmad_city) Or 
(!nmad_address_1 = !Webir_Country) And IsNull(!nmad_address_2) Or (!nmad_address_2 = 
!nmad_city) Or (!nmad_address_2 = !Webir_Country) And IsNull(!nmad_address_3) Or 
(!nmad_address_3 = !nmad_city) Or (!nmad_address_3 = !Webir_Country)) Then
        DoCmd.RunSQL "INSERT INTO Addresses_ToBeReviewed SELECT 
SunstarAccountsInWebir_SarahTest.* FROM SunstarAccountsInWebir_SarahTest WHERE 
(((SunstarAccountsInWebir_SarahTest.external_nmad_id)='" & qs!external_nmad_id & 
"'));"

        Else:

        Set ss = db.OpenRecordset("1042s_FinalOutput_7")
        With ss.Fields

            'if not invalid address, loop through second (final output) table to find 
matching ID's
                If ss.EOF = False Then

                ss.MoveFirst
                Do
                Dim mykey As String
                mykey = Right(ss!IRSfileFormatKey, 10)
                Debug.Print mykey
                If qs.Fields("external_nmad_id") = mykey Then
                    ss.Edit
                    ss.Fields("box13c_Address") = qs.Fields("nmad_address_1") & 
qs.Fields("nmad_address_2") & qs.Fields("nmad_address_3")
                    ss.Update
                    mytestwrite = "Yes"

                End If

                ss.MoveNext

            'if the valid address doesn't match to final output table, add to list of 
addresses not matched
                Loop Until ss.EOF
                If mytestwrite = "No" Then
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL "INSERT INTO Addresses_NotUsed SELECT 
SunstarAccountsInWebir_SarahTest.* FROM SunstarAccountsInWebir_SarahTest WHERE 
(((SunstarAccountsInWebir_SarahTest.external_nmad_id)='" & qs!external_nmad_id & 
"'));"
                    DoCmd.SetWarnings True

                End If
            End If
        End With

        End If

    qs.MoveNext
    Next i

End With

'close reference set
qs.Close
Set qs = Nothing
ss.Close
Set ss = Nothing

End Sub

Answer 2

Ok, based on your stated goal, there are a few errors in your approach.

Here is how I understand your goal based on your opening paragraph:

Loop through each record in table TableA. If the record meets certain complex criteria, search a second table TableB to see if any records in TableB contain a matching ID value from this record in TableA. If a match exists, update a field in TableB, otherwise, export the record from TableA to Excel.

I will describe how the code you have presented is processing your data, and then I will explain how I would approach this problem.

First, as @ScottHoltzman alluded, the DoCmd.TransferSpreadsheet statement that you have in your code will, of course, transfer the entire table to Excel because that is what you told it to do. The 3rd parameter specifies the data to be exported, and you gave it the full table name, so the full table will be exported.

Second, I think you are misunderstanding how looping through the two RecordSets in your code is actually functioning. Your code is doing the following:

1. Evaluate a record in qs. If it doesn't meet the criteria, move to the next qs record and repeat step 1.
2. If the record in qs does meet the criteria, evaluate a record in ss against this record in qs.
3. If they match, update ss and move to the next ss record, go to step 2, remembering that qs is still pointing at the same record and has not moved.
4. If they do not match, transfer the entire table to Excel, now move to the next ss record, go to step 2, again remembering that qs is still pointing at the same record and has not moved.
5. Once all records in ss have been processed through steps 2, 3 & 4, move to the next qs record and go to step 1

I would expect your code to export the table to Excel over and over again many times.

I would also expect your code to get an error as soon as you begin to process the 2nd qs record that moves on to step 2 because after having processed steps 2, 3 & 4 for the first qs record that met your criteria, the ss RecordSet will be pointing at EOF, and you don't have any code to move the pointer back to the first record in ss.

Anyway, since you have a complex criteria for determining if a record is exported or not, I would recommend adding a single True/False field to TableA called ToExport. Now, at the beginning of your code, you would set ToExport = False for all records in TableA. Then, your code would work to evaluate each record in TableA to determine if the record should be exported. If it should, you update ToExport to be True. Once you have looped through the entire table, only the records needing exported will be marked as ToExport = True. Now, you export just the True records to Excel, thereby achieving your desired result.

Here is some code that should achieve this goal in an efficient manner. This code tries to use the tables and criteria from your original source. It also replaces your With blocks and For loops with more useful Do loops, taking advantage of built-in RecordSet looping and EOF checking.

Public Sub EditFinalOutput2()
Dim db As DAO.Database
Dim qs As DAO.Recordset
Dim ss As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()
strSQL = "UPDATE [SunstarAccountsInWebir_SarahTest] SET ToExport = False;"
db.Execute strSQL
Set qs = db.OpenRecordset("SunstarAccountsInWebir_SarahTest", dbOpenDynaset)

Do While Not qs.EOF
    If (IsNull(qs("nmad_address_1")) Or (qs("nmad_address_1") = qs("nmad_city")) Or (qs("nmad_address_1") = qs("Webir_Country")) And IsNull(qs("nmad_address_2")) Or (qs("nmad_address_2") = qs("nmad_city")) Or (qs("nmad_address_2") = qs("Webir_Country")) And IsNull(qs("nmad_address_3")) Or (qs("nmad_address_3") = qs("nmad_city")) Or (qs("nmad_address_3") = qs("Webir_Country"))) Then
        MsgBox "This was an invalid address"
    Else
        strSQL = "SELECT * FROM [1042s_FinalOutput_7] WHERE Right([IRSfileFormatKey], 10) = """ & qs("external_nmad_id") & """;"
        Set ss = db.OpenRecordset(strSQL, dbOpenDynaset)
        If ss.BOF Then
            qs.Edit
            qs("ToExport") = True
            qs.Update
        Else
            Do While Not ss.EOF
                ss.Edit
                ss("box13_Address") = "Test"
                ss.Update
                ss.MoveNext
            Loop
        End If
        ss.Close
    End If
    qs.MoveNext
Loop
qs.Close
strSQL = "SELECT * FROM [SunstarAccountsInWebir_SarahTest] WHERE ToExport = True;"
DoCmd.TransferSpreadsheet acExport, 10, strSQL, "\\DTCHYB-MNMH001\C_WBGCTS_Users\U658984\My Documents\pre processor\PreProcessor7\ToBeReviewed\AddressesNotActiveThisYear.xlsx", False

Set qs = Nothing
Set ss = Nothing
db.Close
Set db = Nothing
End Sub

I hope this helps you better achieve your goal.

Answer 3

Create a query like this, and execute it, and return dim rst as Recordset NOTE: I have changed the AND-s to OR-s as that is what I think you want...

Select qs.*

From
    (Select *
     From SunstarAccountsInWebir_SarahTest
     Where Not
     (
        (IsNull(nmad_address_1) 
     Or (nmad_address_1 = nmad_city) 
     Or (nmad_address_1 = Webir_Country)

     OR IsNull(nmad_address_2) 
     Or (nmad_address_2 = nmad_city) 
     Or (nmad_address_2 = Webir_Country) 

     OR IsNull(nmad_address_3) 
     Or (nmad_address_3 = nmad_city) 
     Or (nmad_address_3 = Webir_Country)
     )
    ) as qs

Left Join
    (Select *
        ,Right(ss.Fields("IRSfileFormatKey"), 10) as ssKey
     From 1042s_FinalOutput_7
    ) as ss

On qs.external_nmad_id = ss.ssKey
Where ssKey is NULL

Then output the rst --(taken from https://support.microsoft.com/en-us/help/246335/how-to-transfer-data-from-an-ado-recordset-to-excel-with-automation )

' Copy field names to the first row of the worksheet
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
    xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next


' Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rst
'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets