OpenRecordSet Invalid Argument

Can anyone help me with an Invalid Argument 3001 on the OpenRecordSet.   I have DAO 3.6 selected in references.

This is my code, just stepping through the code at this point.

Dim strsql As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

strsql = "Select * from SupplierContacts WHERE SuppliercntEmalSelected='Y'"
Set db = CurrentDb
Set rs = db.OpenRecordset(strsql, dbSeeChanges, dbFailOnError)
If Not rs.RecordCount = 0 Then
rs.MoveFirst
Do Until rs.EOF

' email code here

rs.MoveNext
Loop
Else
' do nothing
End If
rs.Close
Set rs = Nothing
Set db = Nothing
LVL 2
CompTech810Asked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Should be:

Set rs = db.OpenRecordset(strsql,dbOpenDynaset, dbSeeChanges)

and is SuppliercntEmalSelected a yes/no field or text?  If the latter, it's OK as is but if yes/no, your SQL should be:

strsql = "Select * from SupplierContacts WHERE SuppliercntEmalSelected = True"

Jim.
0
 
Rey Obrero (Capricorn1)Commented:
change this

Set rs = db.OpenRecordset(strsql, dbSeeChanges, dbFailOnError)

with


Set rs = db.OpenRecordset(strsql)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can leave the dbSeeChanges, but dbFailOnError is not a valid argument.

Jim.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
CompTech810Author Commented:
I removed dbFailOnError and im still getting the error 3001.

Set rs = db.OpenRecordset(strsql, dbSeeChanges)
0
 
Rey Obrero (Capricorn1)Commented:
i just post

Set rs = db.OpenRecordset(strsql)

Note: *You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.
The server-generated IDENTITY values cannot be reflected at the client side unless you use the DAO property dbSeeChanges.
0
 
Rey Obrero (Capricorn1)Commented:
in your code, looks like you just want to get the email address from the recordset, so you can simply use

Set rs = db.OpenRecordset(strsql)  ' without the optional arguments.
0
 
CompTech810Author Commented:
JDettman:  That worked!!!!!  Yeah!!

Capricorn1:  I have to use the dbSeeChanges because I am connected to a SQL database through ODBC.

Now I need the strsql to contain a query not just a simple query.  The query that I wrote references alot of fields that change depending on what contact it is referencing.

How do I add a query in the strsql statement, as you can tell I'm new to VBA....
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<How do I add a query in the strsql statement, as you can tell I'm new to VBA....>>

  For the openrecordset, you just name the query.  The argument can be an SQL string, a table, or the name of a query.

Jim.
0
 
CompTech810Author Commented:
Ok, I added the query  qryContactsToEmail.

Set rs = db.OpenRecordset(qryContactsToEmail, dbOpenDynaset, dbSeeChanges)

Now I'm getting an error that it can not find the query or it is misspelled.  I know it is spelled correctly because I copy and pasted the name from the query.....   Now what am I missing.....   I really need to take a VBA class.
0
 
Rey Obrero (Capricorn1)Commented:
you have to enclosed the query or table name in ""

Set rs = db.OpenRecordset("qryContactsToEmail", dbOpenDynaset, dbSeeChanges)
0
 
Dale FyeCommented:
Jim,

You said: "You can leave the dbSeeChanges, but dbFailOnError is not a valid argument"

Why is dbFailOnError not a valid argument?  Access help indicates that the Options argument is "A combination of RecordsetOptionEnum constants that specify characteristics of the new Recordset." and dbFailOnError is one of the RecordsetOptionEnum values.

Access help is not very useful in this sense, and does not give an example.  I would think "a combination" would be identified as the sum of those Enum values, not as a parameter array.  Maybe one of you MVPs can send a note to MS regarding this discrepancy
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Your looking at the options for the execute method, which in Access circles is an "action query" (a DML query - something other then a SELECT).

  For the openrecordset, the valid options are:

dbAppendOnly, dbSeeChanges, dbDenyWrite, dbDenyRead, dbForwardOnly, dbReadOnly, dbRunAsync, dbExecDirect, dbInconsistent ,and dbConsistent.


<<Access help is not very useful in this sense, and does not give an example.  I would think "a combination" would be identified as the sum of those Enum values, not as a parameter array.  Maybe one of you MVPs can send a note to MS regarding this discrepancy>>

 We've been complaining about the help since A97<g>.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.