Help: Forms using linked tables

[Vern Shellman]

We've got a form in Access 97 SR-2 that works fine with local tables.
The pertinent VB code populating a combo box looks like this:


Private Function ShowMOFInfo()
Dim db As Database
Dim rec As Recordset
Dim ccode As Integer
Dim strAD As String


On Error GoTo smiErrorHandler
Set db = DBEngine.Workspaces(0).Databases(0)
Set rec = db.OpenRecordset("tblMOF")
rec.Index = "PrimaryKey"
rec.Seek "=", Me!cboStudyNumber
If rec.NoMatch Then ....

When the same table is linked to an external database the form fails.
I'm not much of a VB progammer but I've learned that the problem is with
the .Seek function on linked tables. So I've tried using FindFirst in
the following manner:


On Error GoTo smiErrorHandler
Set db = DBEngine.Workspaces(0).Databases(0)
Set rec = db.OpenRecordset("tblMOF")
rec.FindFirst "StudyNumber = '" & Me![StudyNumber]& "'"
If rec.NoMatch Then ....

Looking in the Immediate Window, Rec.Fields.Count finds the correct
number of fields but I'm seeing a lot of errors declaring:

<Operation is not supported for this type of object>.

Also, error trapping gives:

"run-time error '13'; type mismatch".

However, creating a new form that pulls data from the same remote table
works, so I don't think it's a datatype issue.

What am I doing wrong?

Thanks in advance for any suggestions on how we might solve this
problem.

 

[Allen Browne]

Hi Vern.

You can solve the problem by specifying you want to open a dbDynaset type
recordset, and use FindFirst (not Seek).

If you don't specify what kind of recordset you want to open, Access gives
you a dynaset type if you open an attached table, query or SQL statement,
but a Table type if you open a recordset based on a local table. This
inconsistency is likely to break your code if the tables are moved, etc.,
just as you found.

Try:
Set rec = db.OpenRecordset("tblMOF", dbOpenDynaset)
rec.FindFirst "StudyNumber = '" & Me![StudyNumber] & "'"

BTW, if StudyNumber is actually a Number field (not a Text field, drop the
extra quotes, i.e.:
rec.FindFirst "StudyNumber = '" & Me![StudyNumber]& "'"
In the case where StudyNumber is blank on the form (e.g. at a new record),
the criteria will be mal-formed, so you may want to use:
rec.FindFirst "StudyNumber = " & Nz(Me![StudyNumber], 0)