473,326 Members | 2,124 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,326 software developers and data experts.

Changing recordsource prohibits editing?

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
6 2187
Stewart Ross
2,545 Expert Mod 2GB
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
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
Stewart Ross
2,545 Expert Mod 2GB
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
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
Stewart Ross
2,545 Expert Mod 2GB
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
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

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

Similar topics

2
by: ColinWard | last post by:
Hi. I have a form which has as its recordsource an SQL string. The SQL String is as follows: SELECT * from CONTACTS where false. this ensures that there is no data loaded in the form when the...
3
by: Michael | last post by:
Hi everyone, I am trying to change the field names for a table that is being exported via Excel. Its a spreadsheet that our National Office sends us but even after promise after promise they...
5
by: Atley | last post by:
I want to edit the source of a Dataset I have set up using the Data Forms wizard. Is there a way to either filter the data to get the subset I want or to change the recordsource of the Dataset...
32
by: deko | last post by:
I have a popup form with a textbox that is bound to a memo field. I've been warned about memo fields so I'm wondering if I should use this code. Is there any risk with changing the form's...
9
by: robert d via AccessMonster.com | last post by:
I'm not sure why the following isn't working. The subform loads correctly, but no data is displayed. I'm certain that there is data and I have checked that the SQL statement does in fact return...
3
by: john | last post by:
I have a form with Members and in that form a subform with Transactions. Those are linked (on banknr) 1xM which I've set in the database relations. Now I would like to copy the form and modify it....
2
by: G Gerard | last post by:
Hello I have noticed that whenever I set a form's RecordSource property in code it launches the form's OnActivate event. In one of my application I set a form's RecordSource to zero lenght...
2
by: cek172 | last post by:
Is there any way to change the recordsource of a subform from the mainform? I have a subform that I would like to have return data from a table based on selections from multiple checkboxes on the...
8
Megalog
by: Megalog | last post by:
Hey guys.. my turn to ask a question: I'm having a weird issue with a form I've reworked. This form has a combo box, which when used is changing the recordsource of a subform. This subform has...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.