?
Solved

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

Posted on 2003-03-30
4
Medium Priority
?
1,543 Views
Last Modified: 2012-06-21
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
Comment
Question by:bennyt
[X]
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
4 Comments
 
LVL 1

Expert Comment

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

Author Comment

by:bennyt
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.
0
 
LVL 28

Accepted Solution

by:
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
0
 

Expert Comment

by:tidjj
ID: 8765944
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

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