Invalid Argument with VBA code "Set rstNewRecord = CurrentDb.OpenRecordset("CustomerTable", dbOpenDynaset)"

Posted on 2003-03-30
Medium Priority
Last Modified: 2012-06-21

I'm using Access 2002.  I want to do this simple task of entering a new record into the "CustomerTable" table which has one field called "CustomerName".  I want to do this via a simple form with one text box and one add customer button.  

Table: CustomerTable
Field: CustomerName
Form txt box: txtCustomerName
Form button: AddCustomer

This is my code:
Private Sub cmdAddCustomer_Click()
    Dim rstNewRecord As Recordset    
    Set rstNewRecord = CurrentDb.OpenRecordset("CustomerTable", dbOpenDynaset)
    With rstNewRecord
        !CustomerName = txtCustomerName
    End With
End Sub

Problem: When data is filled in the txtCustomerName box and the AddCustomer button is clicked i get a "Invalid Argument" error on line "Set rstNewRecord = CurrentDb.OpenRecordset("CustomerTable", dbOpenDynaset)".  It doesn't get any further than that.  

What is the problem here?
Question by:bennyt
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8235484
Is DAO objects referenced, or ADO? You might try to dim the recordset as dao.recordset

Author Comment

ID: 8235986
Sorry I don't know what DAO or ADO are yet as I am new to Access.  However, I tried to dim recordset as dao.recordset like you said and I got the "User-defined type not found" compile error.
LVL 28

Accepted Solution

TextReport earned 80 total points
ID: 8236666
In your VBA Window go to the Tools Menu, References and ensure Microsoft DAO is selected, if there are more than one option for Microsoft DAO select the latest version. Then change your dim statement to Dim rstNewRecord As DAO.Recordset

Cheers, Andrew

Expert Comment

ID: 8765944
Try this:

Private Sub cmdAddCustomer_Click()
     Dim rstNewRecord As Object 'untyped object

     Set rstNewRecord = CurrentDb.OpenRecordset("CustomerTable") 'no dbOpenDynaset option

     With rstNewRecord
          !CustomerName = txtCustomerName
     End With
End Sub

The data type is 'dynamic'. It does not have the same signification in every environment. A recordset, in Access, is a child for a Form (with a DataSource). Because your Sub is not in a form, you cannot user the Access Recordset object type. You could seek for the right object type (also called Recordset, as mentionned by vbaRookie) but it is easier to use a "all purposes object" and cope with it. That way, you'll not have assistance on properties and methods but it will work. That's what you're waiting for.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question