By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,141 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

Forms using linked tables fail

P: n/a

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.

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

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Seek only works on local tables opened as table-type recordset. Linked
tables can't be opened as table-type recordsets; to be writeable, must be
opened as dynasets, for which Seek is not supported. Try "FindFirst",
instead.

Larry Linson
Microsoft Access MVP

"Vern Shellman" <ve***********@uchsc.edu> wrote in message
news:40**********************@news.newsgroups.ws.. .

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.

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

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #2

P: n/a
Vern Shellman <ve***********@uchsc.edu> wrote in message news:<40**********************@news.newsgroups.ws> ...
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:
...
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 ....


See if
rec.FindFirst "StudyNumber = " & Me![StudyNumber]
instead of
rec.FindFirst "StudyNumber = '" & Me![StudyNumber]& "'"
works.
Looking up an Integer or Long using a string would cause a type mismatch.

James A. Fortune
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.