Recordset - too few parameeters, expected 2 (1 Viewer)

[YYYY]

I'm using a recordset in access vba to update specific records with an update command.
I'm receiving this error mesage
too few parameeters, expected 2
i looked it up and found that the DBGuy suggests
to add this link to a module and then call on it

Code Snippets

as understood from there, I changed my code to
set recordset = fDAOGenericRst(Query)
[instead of
set recordset = CurrentDB.OpenRecordset(Query)]

the problem is I get the message "variable not defined" on that code
p.s. I tried both the ADO and the DAO.
both gave me the same error messsage.
as you can see in the code attached I tried these 3 methods.

Dim Query
    Query = "SELECT TDID, ContactID, TransactionDate, AdditionAmount, FundId, SpecialReceipt, RunningFund_ContactTotalDates, RunningContactTotalDates, ReceiptNumber From ReceiptsToCreateTotals_Dates " _

    ' Get a recordset using this command.
    Dim Recordset
    
    'Set Recordset = CurrentDb.OpenRecordset(Query)
    'Set Recordset = fADOGenericRst(Query)
     Set Recordset = fDAOGenericRst(Query)

 

[arnelgp]

Recordset is a Reserved word.
try renaming your Variables.

 

[June7]

Advise not to use reserved words as names for anything. Query and Recordset are reserved words.

Why do you need to open a recordset? Records can be updated without a recordset.

Why use line continuation character when there is no continuation to next line?

 

[Galaxiom]

Advise not to use reserved words as names for anything. Query and Recordset are reserved words.

"Anything" is too strong a word. There are exceptions. There is nothing wrong with using words like Recordset or Controls as Property names inside a custom class because the context is clear.

 

[YYYY]

Thanks for the responses,
but the error wasn't received on this line , rather on the code of the link:
CODE SNIPETS - http://www.accessmvp.com/thedbguy/codes.php?title=generic
mentioned above.
to clear out the problem I"m adding the part that errors here -
this part is highlighted when the error occurs.
adLockOptimistic errors in fADOGenericRst
and dbOpenDynaset errors in fDAOGenericRst

Function fADOGenericRst(ByVal strSource As String, _
                            Optional cnn As ADODB.Connection, _
                            Optional pCursorLocation As ADODB.CursorLocationEnum = adUseServer, _
                            Optional pCursorType As ADODB.CursorTypeEnum = adOpenKeyset, _
                            Optional pLockType As ADODB.LockTypeEnum = adLockOptimistic, _
                            Optional pOption As ADODB.ExecuteOptionEnum = -1) As ADODB.Recordset

Function fDAOGenericRst(strSQL As String, _
                            Optional intType As DAO.RecordsetTypeEnum = dbOpenDynaset, _
                            Optional intOptions As DAO.RecordsetOptionEnum, _
                            Optional intLock As DAO.LockTypeEnum, _
                            Optional pdb As DAO.Database) As DAO.Recordset

 

[theDBguy]

Thanks for the responses,
but the error wasn't received on this line , rather on the code of the link:
CODE SNIPETS - http://www.accessmvp.com/thedbguy/codes.php?title=generic
mentioned above.
to clear out the problem I"m adding the part that errors here -
this part is highlighted when the error occurs.
adLockOptimistic errors in fADOGenericRst
and dbOpenDynaset errors in fDAOGenericRst

Function fADOGenericRst(ByVal strSource As String, _
                            Optional cnn As ADODB.Connection, _
                            Optional pCursorLocation As ADODB.CursorLocationEnum = adUseServer, _
                            Optional pCursorType As ADODB.CursorTypeEnum = adOpenKeyset, _
                            Optional pLockType As ADODB.LockTypeEnum = adLockOptimistic, _
                            Optional pOption As ADODB.ExecuteOptionEnum = -1) As ADODB.Recordset

Function fDAOGenericRst(strSQL As String, _
                            Optional intType As DAO.RecordsetTypeEnum = dbOpenDynaset, _
                            Optional intOptions As DAO.RecordsetOptionEnum, _
                            Optional intLock As DAO.LockTypeEnum, _
                            Optional pdb As DAO.Database) As DAO.Recordset

Hi. I couldn't tell from your posts, which line, exactly, was causing the error? Have you tried compiling (Debug > Compile) your project?

Edit: Oh, I see. You're saying dbOpenDynaset is getting highlighted. Right, so compiling your project should give you some clues too. What are your References?

 

[YYYY]

Hi. I couldn't tell from your posts, which line, exactly, was causing the error? Have you tried compiling (Debug > Compile) your project?

yes,
when I used fADOGenericRst the line ending with adLockOptimistic
when I used fDAOGenericRst the line ending with dbOpenDynaset

 

[theDBguy]

yes,
when I used fADOGenericRst the line ending with adLockOptimistic
when I used fDAOGenericRst the line ending with dbOpenDynaset

Hi. Whether you use ADO or DAO depends on your references and the data type of your variable. Using something simple like:

Dim Recordset

could be ambiguous. Depending on your references, you might get a DAO or an ADO object. The fact you're getting an error for using VBA constants could mean you're missing the necessary reference. Could you please list them out for us (the ones you have)?

 

[YYYY]

Hi. Whether you use ADO or DAO depends on your references and the data type of your variable. Using something simple like:

Dim Recordset

could be ambiguous. Depending on your references, you might get a DAO or an ADO object. The fact you're getting an error for using VBA constants could mean you're missing the necessary reference. Could you please list them out for us (the ones you have)?

visual basic for application
microrsoft access 16.01 object library
OLE Automation
Microsoft CDO for Windows 2000 library

 

[theDBguy]

visual basic for application
microrsoft access 16.01 object library
OLE Automation
Microsoft CDO for Windows 2000 library

Looks like you're missing the one for DAO. Try adding a reference to: Microsoft Office 16.0 Access Database Engine Object Library

 

[YYYY]

Looks like you're missing the one for DAO. Try adding a reference to: Microsoft Office 16.0 Access Database Engine Object Library

Added, but now it errored on (Query) with the message in the picture attached
Added,

 

[theDBguy]

Added, but now it errored on (Query) with the message in the picture attached
Added, View attachment 81793

Hi. This could be where you're running into reserve words problems now. Try using a different variable name for Query, just as a test.

 

[YYYY]

Hi. This could be where you're running into reserve words problems now. Try using a different variable name for Query, just as a test.

Changed Query to Query1 and Recordset to Recordset1
but I"m getting the same error.

 

[YYYY]

Changed Query to Query1 and Recordset to Recordset1
but I"m getting the same error.

here is the full string that Query1 represents, maybe something is wrong with the string.
SELECT TDID, ContactID, TransactionDate, AdditionAmount, FundId, SpecialReceipt, RunningFund_ContactTotalDates, RunningContactTotalDates, ReceiptNumber From ReceiptsToCreateTotals_Dates WHERE ContactID =200 And RunningFund_ContactTotalDates >= 100 And TransactionDate >= #3/30/2020# And TransactionDate <= #4/20/2020#

 

[theDBguy]

here is the full string that Query1 represents, maybe something is wrong with the string.
SELECT TDID, ContactID, TransactionDate, AdditionAmount, FundId, SpecialReceipt, RunningFund_ContactTotalDates, RunningContactTotalDates, ReceiptNumber From ReceiptsToCreateTotals_Dates WHERE ContactID =200 And RunningFund_ContactTotalDates >= 100 And TransactionDate >= #3/30/2020# And TransactionDate <= #4/20/2020#

Hi. An easy way to test if the query string is okay is to copy and paste it into the query designer.

 

[YYYY]

Hi. An easy way to test if the query string is okay is to copy and paste it into the query designer.

tried and works well!

 

[theDBguy]

tried and works well!

So, it's not the query string. What is Me.QueryString? That's were the error was, correct?

 

[YYYY]

So, it's not the query string. What is Me.QueryString? That's were the error was, correct?

no. the error is in the code
Set Recordset = fDAOGenericRst(Query1)
I guess the problem is in the fDAOGenericRst function
which you are a lot more familiar with then me.
thanks a lot!

On the form that sets of this code i added a textbox called QueryString, to be able to view the full string and paste it to the query designer as you said.

 

[theDBguy]

no. the error is in the code
Set Recordset = fDAOGenericRst(Query)
I guess the problem is in the fDAOGenericRst function
which you are a lot more familiar with then me.
thanks a lot!

On the form that sets of this code i added a textbox called QueryString, to be able to view the full string and paste it to the query designer as you said.

Well, I thought you said you changed that name? Try it this way too:

Set rs = fDAOGenericRst(Me.QueryString)

 

[YYYY]

Well, I thought you said you changed that name? Try it this way too:

Set rs = fDAOGenericRst(Me.QueryString)

It worked!! Thank You!!!
p.s. do I understand correctly that you prefer DAO over ADO?