Access VBA Run-time error 3078, or Type Mismatch on DCount function

Question

Objective: I'm building VBA code to filter through an address table SunstarAccountsInWebir_SarahTest. I want to loop through first and see if the address is "valid".

• If it is not valid – export to different table.
• If it is valid – it enters another nested if/then within "valid" address rows:
  • If their ID, external_nmad_id matches the ID of the second table 1042s_FinalOutput_7, I want to update one of the columns in the second table box13c_Address.
  • If it doesn't match an ID of the second table – it will be exported to a different table.

My problem is when I run my code it is returning

Run-Time error 3078: cannot find table or query

(it's breaking at the line where I compare the value of the cell (as string) against the DCount of table 2). If I remove the quotes around it I get a different error:

Type mismatch against the DCount

I feel like I'm missing something simple but can't tell what. How can I get my code to match a string value called in !external_nmad_id against the rest of the table called in my string? DCount("[ID]", StrSQL1)

Public Sub EditFinalOutput2()

'set variables
Dim i As Long
Dim qs As DAO.Recordset
Dim ss As DAO.Recordset
Dim StrSQL1 As DAO.Recordset
Dim IRSfileFormatKey As String
Dim external_nmad_id As String
Dim nmad_address_1 As String
Dim nmad_address_2 As String
Dim nmad_address_3 As String
Dim mytestwrite As String

'open reference set
Set db = CurrentDb
Set qs = db.OpenRecordset("SunstarAccountsInWebir_SarahTest")
'Set ss = db.OpenRecordset("1042s_FinalOutput_7")
'Set StrSQL1 = db.OpenRecordset("SELECT RIGHT(IRSfileFormatKey, 10) As ID 
'FROM 1042s_FinalOutput_7;")

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

    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")
        Set StrSQL1 = db.OpenRecordset("SELECT RIGHT(IRSfileFormatKey, 10) As ID FROM 1042s_FinalOutput_7;")
        If !external_nmad_id = DCount("[ID]", StrSQL1) Then
                ss.Edit
                ss.Fields("box13c_Address") = qs.Fields("nmad_address_1") & qs.Fields("nmad_address_2") & qs.Fields("nmad_address_3")
                ss.Update

        Else:   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

qs.MoveNext
Next i

End With

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

End Sub

Answer 1

The issue is that the DCount function cannot operate directly against a Recordset.

You are declaring StrSQL1 as a RecordSet object and setting it to a RecordSet based on your Select statement.

Set StrSQL1 = db.OpenRecordset("SELECT RIGHT(IRSfileFormatKey, 10) As ID FROM 1042s_FinalOutput_7;")

You are then trying to pass this RecordSet to the DCount function which cannot accept a RecordSet object as the Domain parameter. As you can see in MSDN the DCount function requires a String parameter in the second position to define the "query" that you wish to "Count". Hence the 3078 error. When you remove the quotes around [ID] in your DCount line, you get Type Mismatch as a compile error because [ID] is not a String or String variable.

After you resolve that, you might want to reconsider your If statement. You haven't provided a sample of what kind of value !external_nmad_id will contain, other than the fact that it is a String value. The DCount function is going to return the number of rows found in the Domain (query) that you told it to count, so it appears you will be comparing a string (which may possibly contain alpha characters) to a number. Access will implicitly convert the DCount numeric result to a String for the sake of the comparison, but if your !external_nmad_id String is truly 10 characters or contains alpha characters, they will never match.