How to check if recordset field *exists* (not whether null or not)

[RSIboy]

Hi - I have a recordest that is based on one of 3 queries, depending on user selection in a combo box. The queries are different - eg one includes CompanyName, and CompanyFax, whilst another includes CompanyName and CompanyAddress.

I need to be able to test if recordset("CompanyFax") exists, not if recordset("CompanyFax") Is Not Null

At the moment I am trying to see if it is null or not, but if it isn't even in the recordset, then Access returns an error like 'object does not exist in this collection'

Any help much appreciated!

Dale
Manchester, UK

 

[meboz]

Try something like this:

private function exists
dim myfield as field
For each myfield in rst.fields
    if myfield.name = "fieldname" then
        exists = 1
    end if
next myfield
end function

 

[modest]

You're trying to find out whether a query exists. This is what I do, just plug the following into a module:

'Function to check if a query exists in the database
'Query name is passed in as a parameter
'A return variable (true/false) is returned
Public Function QueryExists(queryName As String) As Boolean
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb
    QueryExists = False
    
    For Each qdf In db.QueryDefs
        If qdf.Name = queryName Then
            QueryExists = True
            Exit Function
        End If
    Next qdf
End Function

And run the following from your code when you want to set the recordset:

  'if query object doesn't exist already create a new one, or use old one if it does
    If Not QueryExists(queryName) Then
        Set qdf = db.CreateQueryDef(queryName)
    Else
        Set qdf = db.QueryDefs(queryName)
    End If

Then to run it do:
Set rs = db.OpenRecordset(queryName) 'run the query in the background

courtesy of me i hope this is what you're looking for

Remember, you need DAO to run this and where you run the code you need to define your object variables:
Dim db As DAO.Database 'declare variable to hold db/workspace name
Dim qdf As DAO.QueryDef 'declare variable to hold query info
Dim rs As DAO.Recordset 'declare variable to reference query result table