AccessForm Type Mismatch

[TedW]

The following code runs as compiled code outside of MS Access. I'm trying to filter a recordset on the Access form. I can get a handle to the form object as an AccessObject, but when I try to set the object to an Access Form object I get an Error 13, Type Mismatch. I can retrieve a filtered recordset, but I can't apply this to the current recordset of the form. Any help would be greatly appreciated. The whole VB project can be emailed for debugging if that would help.
Thanks!

Public Sub FindRecord()

Dim access_app As Access.Application
Dim db As CurrentProject
Dim cnn1 As New ADODB.Connection
Dim comm As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim rs_filtered As New ADODB.Recordset
Dim rs_temp As New ADODB.Recordset
Dim db_name As String
Dim obj As AccessObject
Dim frm_obj As AccessObject
Dim frm As Access.Form
Dim frm_coll As Collection
Dim frm_name As String
Dim ndx As Long
Dim cnt As Long
Dim frm_title As String
Dim str As String
Dim SPN_txt As String
Dim prop As Property

SPN_txt = "235128301001"
frm_name = "f_Parcels"

Set access_app = Application
Set db = access_app.CurrentProject

db_name = db.Name
MsgBox "The open database is " & db_name

For Each obj In db.AllForms
'MsgBox "The object name is " & obj.Name
If obj.Name = frm_name Then
MsgBox "The desired object name is " & obj.Name
Set frm_obj = obj
Exit For
End If
Next

'-------------------------------------------------
'This is where the type mismatch happens
Set frm = frm_obj
frm_title = frm.Name
MsgBox "The title of the form is " & frm_title
'--------------------------------------------------

Set cnn1 = DataEnvironment1.Connection_parcel
Set comm = DataEnvironment1.Commands("parcel_att")
'MsgBox "cnn1 state: " & GetState(cnn1.State)

Set rs = comm.Execute
Set rs_temp = rs
rs_temp.Filter = "SPN = '" & SPN_txt & "'"

Set rs_filtered = rs_temp

cnt = rs_filtered.RecordCount
MsgBox "The filtered record count is " & cnt

End Sub

Public Function GetState(intState As Integer) As String

Select Case intState
Case adStateClosed
GetState = "adStateClosed"
Case adStateOpen
GetState = "adStateOpen"
End Select

End Function

[mstraf]

I never worked with Access Forms, but here is my two pennies.
I think the flow passes this line:

MsgBox "The desired object name is " & obj.Name

Change this with:

MsgBox "The desired object name is " & obj.Name & " " & typeof obj is AccessForm

and see if it prints True or False. It seems that obj is an AccessObject, not an AccessForm

[JBourgeois]

Access form recordsets are DAO Recordsets (Data Access Objects), not ADO Recordsets (ActiveX Data Objects). They are quite similar in use, but since they are not the same, you get the type mismatch.

Although Microsoft has been telling everybody for many years to use ADO instead of DAO, DAO is native to Access and is generally more interesting to use with an Access database, as demonstrated by the fact that Microsoft still uses it internally in Access.

If you want to manipulate an Access form, you have no choice but to use DAO.

For code that works directly on the data, the choice is yours.

DAO is more powerful and permits things in Access are not readily available in ADO, such as saving queries in the database from your code.

However, it is not portable to other databases as ADO is. If you ever decide to convert from Access to SQL Server, your code is doomed if you used DAO. ADO code would still work (mostly) just by changing the connectiong string.

[Ron Weller]

Actually your problem is that Access returns an AccessObject data type
from the AllForms, or in fact any of the All????? collections.
The more specific object type like Form, or Report, etc.. Can only access
open objects. You must first open the Form, then you can get the Form
object through the Forms Collection, which holds only Open Form objects.
The mode in which you open the form, like Design mode, or Normal mode,
etc. Determines which properties and methods you have access to; but
mostly the form must be open.
It's visiable flag can be set to True or False if you want to hide or unhide it.
There is also a WindowMode parameter in the OpenForm command, which if
set to Hidden, starts the form out with it's visiable flag set to false.
DoCmd.OpenForm "f_Parcels", , , , , acHidden 'Open The Form
Set frm = access_app.Forms("f_Parcels") 'Get the Form Object
frm.Visiable = True 'Show The Form

[TedW]

Thank you all so much for your thoughtful comments. Your explanation was quite clear Ron, and that piece of code was the missing link.

Thanks again!