CODEWITHSUNDEEP
ms-accessvba
Jeff Brady
Jeff Brady

Reputation: 1498

How to test if item exists in recordset?

I have a crosstab query that is being loaded into a recordset. I'm then writing the query fields to an Excel spreadsheet. The problem is that a field may not exist based on the query results.

For example, I have the following line:

oSheet5.Range("F1").Value = rsB2("AK")

...which would write the value of the recordset item named "AK" to the spreadsheet. But if "AK" doesn't exist, I get an error Item not found in this collection.

How I can I test to see if there's an item named "AK"?

I tried...

If rsB2("AK") Then
    oSheet5.Range("F" & Count).Value = rsB2("AK")
End If

...but that didn't work.

I also tried...

If rsB2("AK") Is Nothing Then
    oSheet5.Range("F" & Count).Value = ""
Else
    oSheet5.Range("F" & Count).Value = rsB2("AK")
End If

...and still the same error.

There are 50+ items/fields to check .. all states in USA plus a few extras. Thanks!

Upvotes: 1

Views: 14085

Answers (2)

HansUp
HansUp

Reputation: 97131

You could add an error handler to catch the item not found error ... ignore it and/or do something else instead.

Or if the first recordset field always maps to the first sheet column regardless of the field's name, you can reference it by its ordinal position: rsB2(0)

Or you could examine the recordset's Fields collection to confirm the field name is present before attempting to retrieve its value.

After you open the recordset, load a dictionary with its field names. This code sample uses late binding. I included comment hints in case you want early binding. Early binding requires you to set a reference for Microsoft Scripting Runtime.

Dim objDict As Object 'Scripting.Dictionary
'Set objDict = New Scripting.Dictionary
Set objDict = CreateObject("Scripting.Dictionary")
Dim fld As DAO.Field

For Each fld In rsB2.Fields
    objDict.Add fld.Name, vbNullString
Next

Then later you can use the dictionary's Exists method to your advantage.

If objdict.Exists("AK") = True Then
    oSheet5.Range("F1").Value = rsB2("AK")
End If

Upvotes: 2

Khinsu
Khinsu

Reputation: 1487

You can use Recordset.FindFirst Method (DAO) take a look here or here

Small example:

Sub FindOrgName()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

'Get the database and Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblCustomers")

'Search for the first matching record   
rst.FindFirst "[OrgName] LIKE '*parts*'"

'Check the result
If rst.NoMatch Then
    MsgBox "Record not found."
    GotTo Cleanup
Else
    Do While Not rst.NoMatch
        MsgBox "Customer name: " & rst!CustName
        rst.FindNext "[OrgName] LIKE '*parts*'"
    Loop

    'Search for the next matching record
    rst.FindNext "[OrgName] LIKE '*parts*'"
End If

Cleanup:
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

End Sub

Upvotes: 4

Related Questions