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

Changing recordsource prohibits editing?

P: 18
I have a weird problem (or I'm overseeing something plain). I have a form which I open in formview with a query as recordsource (set in the design view). On opening, depending on some condition, the recordsource may be changed to another query from code. This query contains all the same fields, except with different values.
Now the problem is that as soon as the recordsource changes, all the fields get locked, and it's impossible to add new records. It's like the AllowEdits, and AllowAdditions properties are set to false, but they aren't. I set a breakpoint just before the sub exits and checked the form and control properties. None of the controls are locked, and AllowEdits and AllowAdditions are set to true. Yet still I can't edit or add any records on the form. The exact moment this happens is when the recordsource is changed.

Can anyone explain what's going on?
Aug 5 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
It would appear that the other recordsource query is not updatable. You can check this by opening the query from the query window and attempting to add a new record directly from the query; if this cannot be done the query is definitely not updatable. If it can, there is some other problem, but I am certain that it has nothing at all to do with the AllowAdditions/Edits/Deletions properties if these have not been changed in code.

There are a whole host of reasons why queries become non-updatable; one of the most obvious is grouping and totalling within the query. A fuller list of potential problems is available in this linked MS Knowledge Base article.


-Stewart
Aug 5 '08 #2

P: 18
It would appear that the other recordsource query is not updatable. You can check this by opening the query from the query window and attempting to add a new record directly from the query; if this cannot be done the query is definitely not updatable. If it can, there is some other problem, but I am certain that it has nothing at all to do with the AllowAdditions/Edits/Deletions properties if these have not been changed in code.

There are a whole host of reasons why queries become non-updatable; one of the most obvious is grouping and totalling within the query. A fuller list of potential problems is available in this linked MS Knowledge Base article.


-Stewart
There is one more thing. I have 2 options for opening the form: in datasheet mode, or formview. The problem arises when it's in formview. When I do the same thing in datasheet mode, change the recordsource to that same query I can edit it without problem. So it seems it has something to do with the formview.
I openend the queries from the query window, and both queries can not be edited when openend that way. Yet, I can edit the data when I open the form with the queries as recordsource in datasheet mode and one of them can be edited in formview.
Also, the query properties of both queries are the same.
Aug 5 '08 #3

Expert Mod 2.5K+
P: 2,545
Hi. If you could post the two recordsource queries here (space permitting) this would be very helpful for verification of how they are structured. They should both be editable when run from the query window; I cannot see how they would be editable otherwise unless there is a reference to a form field (in the where clauses) which is missing outside of running them from the form environment.

Datasheet or form view should make no difference - you can try this yourself by changing the form to datasheet view from form view and vice versa when you have the form concerned open for editing.

If you could also post the code used to switch from form view to datasheet view that would be helpful.

-Stewart
Aug 5 '08 #4

P: 18
Hi. If you could post the two recordsource queries here (space permitting) this would be very helpful for verification of how they are structured. They should both be editable when run from the query window; I cannot see how they would be editable otherwise unless there is a reference to a form field (in the where clauses) which is missing outside of running them from the form environment.

Datasheet or form view should make no difference - you can try this yourself by changing the form to datasheet view from form view and vice versa when you have the form concerned open for editing.

If you could also post the code used to switch from form view to datasheet view that would be helpful.

-Stewart
Hi Stewart,

In this thread I got an answer to what was going on.

Thank you for your time and trouble though
Aug 6 '08 #5

Expert Mod 2.5K+
P: 2,545
HI again. I am glad you got a resolution to your problem, even if through another forum in which you posted a copy of your DB. To summarise the fairly lengthy discussions in that other forum, your second recordsource query was in fact not updatable, but as a result of other bugs/quirks of your DB this was not being handled consistently by the DB and led to the query being treated as an Inconsistent Dynaset which allowed updatability to occur. The other forum also pointed out design issues relating to the ways the query was built.

I do understand your need to resolve problems in whatever way you see fit. It would help us to class your original question as completed or withdrawn if you would simply let us know that you are asking another forum for assistance - we won't be offended by this!

Stewart
Aug 6 '08 #6

P: 18
HI again. I am glad you got a resolution to your problem, even if through another forum in which you posted a copy of your DB. To summarise the fairly lengthy discussions in that other forum, your second recordsource query was in fact not updatable, but as a result of other bugs/quirks of your DB this was not being handled consistently by the DB and led to the query being treated as an Inconsistent Dynaset which allowed updatability to occur. The other forum also pointed out design issues relating to the ways the query was built.

I do understand your need to resolve problems in whatever way you see fit. It would help us to class your original question as completed or withdrawn if you would simply let us know that you are asking another forum for assistance - we won't be offended by this!

Stewart
Hi Stewart,

I understand, my apologies, I should have updated this post sooner. I guess I got a bit carried away by the discussion on the other thread and kinda forgot about this one. I'll remember this for the future :)
Aug 7 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.