OPENRECORDSET woe's

Question

I am having HUGE problems with an "OpenRecordset" statement I'm trying to write in VB code in a small access db I'm working on.

I need help -  using :

Private Sub FINDCONTS()

Dim Db As DAO.Database

Dim rs As DAO.Recordset

Set Db = CurrentDb

Set rs = Db.OpenRecordset("CUSTCONTS", dbOpenDynaset, dbReadOnly, dbOptimistic)

End Sub

as the code block - and the CUSTCONTS is a simple query that seems to pick up the 2 records it should....but I'm getting the Run Time error 3001 - Invalid argument error.  ANY help is GREATLY appreciated.....I have been banging my head on the desk the entire day.

My goal is this - (I'm open to suggestions on how to 'better' do this please)

• I have 4 forms - I open the first 'master' form - then take a customerID number from that and when opening the second form - the form would populate with a 'recordset' that all have the same customer ID from the first form  that the user can then scroll back and forth through to edit or view.  SO - if the first form is a "Customer" master record and the second is a 'Contacts" page - and there are 3 contacts for that customer - then opening the "Contacts" page would give them 3 records to sift through - as brought back by the "CUSTCONTS" query...

I am using Access 2007.  Thank you in advance for ANY thelp you can provide.

John

Answer 1

First - above you mention " move the reference to the C outside of the quotes" - I don't understand what the "C" means - can you explain?

Second - this code is applied on the Contacts page - so the statement above is what I used - and you are correct!  - the CUSTID is a text field so I used the single quote version you sent for that.

I continue to get an invalid argument error - so perhaps it's not seeing the string as a value yet?  

My goal for keeping the forms/tables unbound was for these reasons - from another - so I can protect the cleanliness of the data before it's committed to the table - perhaps apply required constraints or checks for existing data before it's inserted to a record.   Also so that should there be a future enhancement I can apply a new set of code and link to the existing data tables rather than need to export/import the records into a new 'bound' application.  Also - in the event the code gets 'hosed' somehow I can just take the tables and set it up with a 'clean' set of executables.  Is that in your opinion 'legitimate' for using unbound forms?  I would like to understand your opinions on this area too!  Thank you very much.

First, regarding "C outside the quotes": I have no idea what happened to what I thought I wrote!  I meant to say, "move the reference to the form control outside the quotes".  In other words, your reference to Forms!CUSTOMER!CUSTID.

Second, you say you are getting an "invalid argument error".  Is that a different error from what you were getting before?  I just noticed this, which is a change from the code you had previously posted:

    Set rs = Db.OpenRecordset(strSQL, dbOpenDynaset, dbQUpdate)

Where did "dbQUpdate" come from?  That has no business in a call to OpenRecordset!  It's a QueryDefType constant. Why did you change the code to include that? Either you should use this:

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

... if you want the recordset to be read-only, or just this:

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

... or even this:

    Set rs = CurrentDb.OpenRecordset(strSQL)

... if you want the recordset to be editable.

Finally, regarding reasons for using unbound forms: I don't think your reasons are very good.  First, any essential requirements on the data can and should be built into constraints on the table, either through validation rules or (in Access 2010 or later) table macros.  Such constraints remain with the tables and are independent of the front-end.

Second, bound forms give you *amazing* ability to validate data up front, through control validation rules, control BeforeUpdate events, and form BeforeUpdate events.  The BeforeUpdate events give you the ability to check anything you want and display nice messages to the user, so why not use that?

I don't understand your concern about problems related to code being "hosed".  If your code is damaged, you just create a new front-end and attach it to the same tables.  For a multi-user application, it's standard practice to split the application into a back-end database containing only the tables and a front-end database containing everything else, with linked tables pointing into the back-end.  If you're worried about code being "hosed", you can do this even with a single-user application.  That way your front-end can be totally trashed, and the back-end tables are still unchanged, and you can experiment with different versions of the front-end.

Answer 2

Hi - thanks for the reply - but when I remove the final condition from the set rs......statement I receive the "too few parameters.  Expected 1" error  Per your second suggestion - my table is not bound as I move data from the forms into the tables with a button control for a number of reasons.  thanks though..

Answer 3

My goal is this - (I'm open to suggestions on how to 'better' do this please)

• I have 4 forms - I open the first 'master' form - then take a customerID number from that and when opening the second form - the form would populate with a 'recordset' that all have the same customer ID from the first form  that the user can then scroll back and forth through to edit or view.  SO - if the first form is a "Customer" master record and the second is a 'Contacts" page - and there are 3 contacts for that customer - then opening the "Contacts" page would give them 3 records to sift through - as brought back by the "CUSTCONTS" query...

There's no need to open a recordset and then assign that recordset to your Contacts form.  Just open the Contacts form, assuming it isn't already open, while filtering it to the specific customer ID you want.  For example:

    DoCmd.OpenForm "Contacts", WhereCondition:="CustomerID=" & Me.CustomerID

I'm assuming here that the Contacts form is bound to your Contracts table, and that table contains a CustomerID field that relates it to your table of Customers.

There are other ways to set this up; for example, you might have the Contacts form as a subform on the Customer form, with subform linkage set up to automatically filter the subform to show the current customer.  However, I don't know enough about the work flow of your application to know what other options might be suitable.

Answer 4

I am having HUGE problems with an "OpenRecordset" statement I'm trying to write in VB code in a small access db I'm working on.

I need help -  using :

Private Sub FINDCONTS()

Dim Db As DAO.Database

Dim rs As DAO.Recordset

Set Db = CurrentDb

Set rs = Db.OpenRecordset("CUSTCONTS", dbOpenDynaset, dbReadOnly, dbOptimistic)

End Sub

as the code block - and the CUSTCONTS is a simple query that seems to pick up the 2 records it should....but I'm getting the Run Time error 3001 - Invalid argument error.  ANY help is GREATLY appreciated.....I have been banging my head on the desk the entire day.

Specifying any lockedits argument (e.g, dbOptimistic) is incompatible with the dbReadOnly option.  This makes sense, because there's no need to lock the record for editing when you have a read-only recordset that can't be edited.  Just leave that argument off altogether:

    Set rs = Db.OpenRecordset("CUSTCONTS", dbOpenDynaset, dbReadOnly)

Answer 5

Try:

Set rs = Db.OpenRecordset("CUSTCONTS")

but you then need to do something with your rs.

    Dim Db              As DAO.Database

    Dim rs              As DAO.Recordset

    Set Db = CurrentDb

    Set rs = Db.OpenRecordset("CUSTCONTS")

    If rs.RecordCount <> 0 Then

        'Records exits so now what

    Else

        'no records where returned

    End If

    rs.Close

    Set rs = Nothing

    Set Db = Nothing