Checking for Optional Argument

[taporctv]

Public Sub runQueryFromDB(query As String, Optional ByRef myRS As DAO.Recordset)
' Add the reference to Microsoft Access 11.0 Lib.
' Add the DAO 3.6 reference
Dim myAccess As access.Application
Dim myDB As Database
Set myAccess = CreateObject("Access.Application")

' Relative path
Dim CurDir As String
CurDir = ThisWorkbook.Path & "\IA Testing.mdb"

' myAccess.Application.Visible = False 'if you don't run this the database will run in the background hidden
myAccess.OpenCurrentDatabase (CurDir)

' open access query normally
myAccess.DoCmd.OpenQuery (query)
Application.DisplayAlerts = False

If myRS Is Nothing Then
' create recordset
Set myDB = myAccess.CurrentDb()
Set myRS = myDB.OpenRecordset(query)
End If


' myAccess.DoCmd.Close
myAccess.Quit
Set myAccess = Nothing

End Sub

How can I check if myRS has been passed or not? I tried IsMissing but thats only for varaints. The reason im asking is because at times no recordset is needed when a query is excuted. So when I pass a query and no recordset to this function, im getting an error because the function thinks there is a recordset to populate.

[rory]

You can use:
If myRS Is Nothing Then

[rory]

Oops - guess I should have read further in your code! Is that not working for you?

[Bob Phillips]

Public Sub runQueryFromDB(query As String, Optional ByRef myRS)


and



If IsmIssing(myRS) Then

[JKwan]

You can also do this:

If IsMissing(OptionalString) Then

[JKwan]

You can also do this:

If IsMissing(OptionalString) Then
OptionalString = "abc"
End If

[taporctv]

Im trying to prevent this code from running if no recordset is passed to the function. IsMissing is doesnt seem to work for me.
' create recordset
Set myDB = myAccess.CurrentDb()
Set myRS = myDB.OpenRecordset(query)

[rory]

If you want to prevent it running, you can use:
If Not myRS Is Nothing Then
' create recordset
Set myDB = myAccess.CurrentDb()
Set myRS = myDB.OpenRecordset(query)
End If

[taporctv]

If you want to prevent it running, you can use:
If Not myRS Is Nothing Then
' create recordset
Set myDB = myAccess.CurrentDb()
Set myRS = myDB.OpenRecordset(query)
End If


The problem I have with this is that even though I pass a recordeset (myRS), this code will never execute because myRS is nothing from the start of the function no matter if a recordset was passed or not.

[Bob Phillips]

That doesn't make any sense.

[rory]

How are you creating the recordset initially? If you pass an actual recordset, then Is Nothing will be False. If you simply declare a variable as DAO.Recordset and pass that as the argument, then it will be Nothing because you haven't actually created a recordset. It may be simplest for you to declare it as Variant as XLD suggested and use IsMissing.