Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1559
  • Last Modified:

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

Hi,

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
        .AddNew
        !CustomerName = txtCustomerName
        .Update
    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?
0
bennyt
Asked:
bennyt
1 Solution
 
vbaRookieCommented:
Is DAO objects referenced, or ADO? You might try to dim the recordset as dao.recordset
0
 
bennytAuthor Commented:
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.
0
 
TextReportCommented:
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
0
 
tidjjCommented:
Try this:

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

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

     With rstNewRecord
          .AddNew
          !CustomerName = txtCustomerName
          .Update
     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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now