473,389 Members | 1,089 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,389 software developers and data experts.

Forms using linked tables fail


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
2 2511
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: polytimi8 | last post by:
Hello, I would like to know if it is possible to create a form in Access2000, which would function like a calendar for 8 operating rooms in hospital, showing which hours are those closed for a...
3
by: Jonathan Bishop | last post by:
Hi. We are using MSDE2000 on a Point of Sale application. We need to keep a copy of a few key tables as up to date as possible for backup purposes. We are looking at using triggers over the...
1
by: Hank Reed | last post by:
Microsoft Access help says the maximum number of Modules, Forms, and Reports is 1000. It appears that linked tables do not count. What about local tables? Help also says that the maximum...
2
by: DataB | last post by:
Hi everyone! I have a forms problem. Bakground: I have created a number of tables. Of these, I have a main parent table (Personal Details) and a number of other child tables (Tax file No.,...
2
by: Mark | last post by:
I have been developing a new application (FE on local drive) which is attached to tables in a BE on a server. For testing purposes I had a copy of the BE in the same folder as the live BE and the...
4
by: danthrom | last post by:
Hi, I have a database with four tables. tbl_Client client_alias (PK, text) tbl_Matters matterID (PK, autonumber) matter (text) client_alias (foreign key, tbl_Client)
4
by: robertmeyer1 | last post by:
Hey, I have about 5 tables, and each table has a corresponding form. The 1st table (A) is a client data table (client information) with a ClientID (PK). This clientId is a (FK) in all 4 other...
1
by: kkrizl | last post by:
I've tried to research this problem, and I haven't been able to find any references to it. Probably because I shouldn't be doing it, but it was working, and now it's not. I'm trying to develop a...
2
by: Luting | last post by:
Hi, Is it possible to update oracle via Access forms? I am thinking maybe I could make a link table connnected with oracle database. And the form could be based on the link table. Does this...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.