Get Error message when passing arguments to a vba function: "Argument not optional (Error 449)"

Question

Hello,

I am trying to retrieve an ADODB recordset from a function in MS-Access 2007 but get a very annoying error message thrown in my face saying: "Argument not optional (Error 449)".

I really can't figure out what I am doing wrong, please help!

Regards,

Stefan

FUNCTION:

Function Rs(sourceSQL As String) As ADODB.Recordset

' Create New Disconnected Recordset

Dim rsConnection As ADODB.Connection
Dim rsRecordset As ADODB.Recordset

Set rsConnection = New ADODB.Connection
rsConnection.Open CurrentProject.Connection

Set rsRecordset = New ADODB.Recordset

rsRecordset.CursorLocation = adUseClient
rsRecordset.Open sourceSQL, rsConnection

Set Rs = rsRecordset

Set rsRecordset.ActiveConnection = Nothing

End Function

FUNCTION CALL:

Private Sub Form_Load()

Call Rs("tblDocumentCode")

Debug.Print Rs.txtDocumentCode(0).Value

End Sub

Answer 1

You are using rs twice, once as a function, once as the name of a recordset:

Private Sub Form_Load()

Set Myrs= Rs("tblDocumentCode")

Debug.Print MyRs(0).Value

End Sub

Answer 2

Assuming that "txtDocumentCode" is a field in the recordset, this:

  Private Sub Form_Load()
    Call Rs("tblDocumentCode")
    Debug.Print Rs.txtDocumentCode(0).Value
  End Sub

...should be changed to this:

  Private Sub Form_Load()
    Debug.Print Rs("tblDocumentCode").Fields("txtDocumentCode").Value
  End Sub

So far as I can tell, that should work without needing to assign the recordset returned by the function to a variable.

But let me step back a bit and suggest that fixing this syntactical error begs the question: what's being done her is pretty inadvisable. Every time this function is called, you're opening a new connection, but Access works better with a single connection that is used over and over again. That's true of both Jet/ACE back ends and server back ends. The overhead required to initialize the connection is going to make your forms load really slowly.

But even worse, this is not Access programming -- this is "refugee from a programming environment that lacks bound forms/controls" programming. You should be using ODBC with linked tables and then you can assign recordsources to your forms without having to muck about with ADO recordsets. If you insist on doing what you're doing, you might as well not be using Access at all, because you're discarding 75% or more of the advantages of Access, and you're getting no performance or concurrency benefits from doing so (and buying yourself a world of problems).

Of course, now that I look at it again, you're using the CurrentProject.Connection, and I'm not sure how this interacts with ODBC linked tables. Indeed, I guess it's possible this is an ADP and not an MDB/ACCDB, but that makes even less sense, since you don't need to do anything extra at all in an ADP to populate your forms with ADO recordsets -- it's the default.

So, in general, something is wrong beyond the syntax error -- what you are doing simply doesn't make a lot of sense.