Database Recordset not updateable

[Aaron Shumaker]

Hi, I have a database I am working with, and I made a
couple small changes that seem to have messed things up.
Any new code I added theoretically should not have had
any influence on the rest of the code, as I didn't refer
to anything that was defined later in the sub. Even
after taking out the new code I put in, several things
still happen. First, it tells me several objects are
undefined. Fine, I fix that no problem. Then it tells
me that the recordset is not updateable, and fails on the
following line:

AccountOpened.Value = -1

Where AccountOpened is a check box linked to a table.
Any help is greatly appreciated.

 

[John Vinson]

Hi, I have a database I am working with, and I made a
couple small changes that seem to have messed things up.
Any new code I added theoretically should not have had
any influence on the rest of the code, as I didn't refer
to anything that was defined later in the sub. Even
after taking out the new code I put in, several things
still happen. First, it tells me several objects are
undefined. Fine, I fix that no problem. Then it tells
me that the recordset is not updateable, and fails on the
following line:

AccountOpened.Value = -1

Where AccountOpened is a check box linked to a table.
Any help is greatly appreciated.

You may have some corruption in your VBA code or the database. I'd
suggest Decompiling and recompiling your database (see
http://www.granite.ab.ca/access/corruptmdbs.htm for possible causes
and cures).

 

[Steve Schapel]

Aaron,

I assume you are working on a form, and the Record Source of this form
is a query. Am I right? If so, the most likely cause of this is that
the query is not updateable. There are a number of factors that
determine whether a query is updateable. There is a good topic on this
in Access Help. If you can't track it down, post back with details of
the query, maybe show us the SQL view of the query.

 

[Aaron Shumaker]

Steve,

You are correct, this code is in a form running off a
query. I was unable to find the help file you suggested.
Here is the SQL view of the query:

SELECT dbo_OpenAccount.*, tbl_EquifaxExplain.*
FROM tbl_EquifaxExplain RIGHT JOIN dbo_OpenAccount ON
tbl_EquifaxExplain.[EID Score] =
dbo_OpenAccount.EIDOverallScore
ORDER BY dbo_OpenAccount.ID DESC;

The query just takes to tables and joins them with a one
to many relationship. dbo_OpenAccount is all new account
information submitted via a web site, tbl_EquifaxExplain
is a set of explainations that are displayed depending on
a value in dbo_OpenAccount.

The only info I did find in the help file related to
joined tables; these tables are only joined in the query,
they have no relationship. Thanks for the help, if you
can give me any more that would be great.

 

[John Vinson]

SELECT dbo_OpenAccount.*, tbl_EquifaxExplain.*
FROM tbl_EquifaxExplain RIGHT JOIN dbo_OpenAccount ON
tbl_EquifaxExplain.[EID Score] =
dbo_OpenAccount.EIDOverallScore
ORDER BY dbo_OpenAccount.ID DESC;

This query will only be updateable if there is a unique Index on
EIDOverallScore in dbo_OpenAccount - and I'd guess that is not going
to be possible.

 

[Steve Schapel]

Aaron,

On the face of it, I would expect this query to be updateable. If you
open the query datasheet, does it give you a blank line at the bottom
for the possibility of adding a new record, and are you able to edit any
of the data directly in the query datasheet?

I suggest you pursue the possibility of corruption, as suggested by John
Vinson.

 

[Steve Schapel]

John,

Won't it be updateable if tbl_EquifaxExplain.[EID Score] is unique...
which is what I had assumed? And I had also assumed that the
AccountOpened field he is trying to update is in the dbo_OpenAccount
table. Should be ok. No?

 

[John Vinson]

John,

Won't it be updateable if tbl_EquifaxExplain.[EID Score] is unique...
which is what I had assumed? And I had also assumed that the
AccountOpened field he is trying to update is in the dbo_OpenAccount
table. Should be ok. No?

I think you're right Steve - I had it backwards.