ACC2000: Error Setting Recordset Property of a Form

• Microsoft Access 2000

SYMPTOMS

When you set the Recordset property of a form programmatically, if the source of the recordset's data is not a local object, you receive one of the following error messages when you open or close the form:

The Microsoft Jet database engine cannot find the input table or query '<table/query>'. Make sure it exists and that its name is spelled correctly.

-or-

The record source <RecordSource> specified on this form or report does not exist.

RESOLUTION

Avoid changing any form or control properties in the properties dialog boxes while in Form view of the form. Instead, modify and save all property changes in Design view.

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:

   
   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=sa;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.

Additional query words: pra alert

Keywords : kberrmsg kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug