ACC2000: Error Setting Recordset Property of a Form (223230)

MORE INFORMATION

When you programmatically set the Recordset property of a form, if you explicitly save any other modifications made to the form while in Form view, Microsoft Jet writes the SQL SELECT statement to the form's RecordSource property. When you try to open or close the form, Jet tries to validate the RecordSource property. If the RecordSource property contains the name of an external table, query, or SQL source, you receive the error message described in the "Symptoms" section.

Because Microsoft Access forms do not store the location of the record source, Jet expects to find the source within the current database. Because the table or query is not a local object, but rather an external source, you receive the error message.

The important issue to keep in mind is that this occurs if you make changes directly to the properties dialog boxes while in Form view. Changes made to properties in Design view or programmatically (at run time) do not cause this error.

Steps to Reproduce Behavior

1. Open a new database.
2. Create a new blank form.
3. Place a text box on the form.
4. On the View menu, click Code, and type or paste the following code:
   
   Note In the following sample code, you must change User ID=<username> and Password=<strong password> to the correct values. Make sure that the user ID has the appropriate permissions to perform this operation on the database.

   Private Sub Form_Load()
      Dim cn As New ADODB.Connection
      Dim rs As New ADODB.Recordset
   
      cn.ConnectionString = "Provider=MSDataShape;" & _
         "Data Provider=SQLOLEDB;Data Source=<SQLServerName>;" & _
         "Initial Catalog=<DatabaseName>;User ID=<username>;Password=<strong password>;"
      cn.Open
       
      rs.LockType = adLockOptimistic
      rs.CursorLocation = adUseClient
      rs.ActiveConnection = cn
      rs.Open "SELECT * FROM <TableName>;"
      
      Set Me.Recordset = rs
   End Sub
   						

   NOTE: In the code, replace <SQLServerName> with the name of your SQL Server computer, replace <DatabaseName> with the name of a database on the SQL Server computer, and replace <TableName> with the name of a table in the SQL Server database.
5. On the View menu, click Form View.
6. If the properties dialog box is not already open, click Properties on the View menu.
7. Change the ControlSource property of the text box to ContactName.
   
   NOTE: The text box should now be populated with data.
8. Close the form and click Yes to save the changes. Accept the default form name. Note that you receive the following error message:
   The Microsoft Jet database engine cannot find the input table or query 'Customers'. Make sure it exists and that its name is spelled correctly.