Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access Query not allowing changes


(!)

Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
06-Aug-2012, 10:06 AM #1
Access Query not allowing changes
I have an Access 2010 database as attached and a query in it called qryDueforArchive. The query is displaying the necessary results...all with the most recent treatment date greater than a year, but it won't allow me to tick the archived box...checked unique values and dynaset settings and all are as resources suggest they should be...please can anyone help?

PS. OBP...if you see this,,,check out tabbed forms...is this what you was thinking?
Attached Files
File Type: zip Example.zip (212.0 KB, 9 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
06-Aug-2012, 11:39 AM #2
Sorry, I can't open the 2010 database, it would have to be 2007 for me to open it.
Are you actually archiving the data or just marking as "archived"?
Is there more than one table in the query?
What joins have you used, you can't use left or right joins and still have the dataset updatable.
__________________
OBP
I do not give up easily
Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
06-Aug-2012, 03:52 PM #3
Oh yeah I forgot, sorry...what about this one?

Just marking it as archived...2 tables, its a 1 to many
Attached Files
File Type: zip ExampleOldVersion.zip (207.2 KB, 16 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
07-Aug-2012, 05:58 AM #4
No it is still the same, I have had this problem with converted databases. I will attach an Access 2000 database, can you import the tables and query in to it, that should ensure that I can open it.
Attached Files
File Type: zip Updates.zip (20.8 KB, 9 views)
Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
07-Aug-2012, 11:09 AM #5
here we go...thanks
Attached Files
File Type: zip Compatible.zip (242.9 KB, 7 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
07-Aug-2012, 12:00 PM #6
You are using qryMaxTreatmentDate which uses aggregate functions which make the query recordset "Not Updatable".
So you either need to use a VBA Recordset or use that query to make or append to a table.
Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
07-Aug-2012, 02:44 PM #7
oh...will a VBA recordset allow me to present the user with the list of records from that query for them to tick archive as required? is it difficult...no idea where to start...is that what you meant by tabbed mainforms too?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
08-Aug-2012, 07:01 AM #8
No it would not make it available to the user to tick them. I can't open your forms, it gives loads of errors due to being an Access 2010 database.
In fact even using an update query and appending the records to a table and then using the table to filter the query does not allow the recordset to be updatable.
You can however create an Updatable query as long as the Treatment Record is not included in the query.

Last edited by OBP; 08-Aug-2012 at 07:40 AM..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
08-Aug-2012, 08:06 AM #9
OK, take a look at the new Temp table and the reworking of your original query in to an Append query. It appends the records that have a max date over one year old. The query that brings it back together and is updatable is the new Temp Query.
So to make this work you will also need a Delete query to delete the old records from the temp table.
Run that query plus your append query and then ope a form based on the Temp Query to tick the Archive check box.
If you didn't need user intervention you could just use a query or vba to tick the archive boxes.
Attached Files
File Type: zip Compatible.zip (199.4 KB, 7 views)
Erm's Avatar
Erm Erm is offline
Member with 298 posts.
THREAD STARTER
 
Join Date: Jul 2003
Location: UK
02-Sep-2012, 06:18 AM #10
oooh sorry, never got a notification about this!! Will go through that now...at present had settled with it producing a list to use as a check list
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑