1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Query not allowing changes

Discussion in 'Business Applications' started by Erm, Aug 6, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    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:

  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  3. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    Oh yeah I forgot, sorry...what about this one?

    Just marking it as archived...2 tables, its a 1 to many
     

    Attached Files:

  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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:

  5. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    here we go...thanks
     

    Attached Files:

  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  7. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    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?
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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:

  10. Erm

    Erm Thread Starter

    Joined:
    Jul 30, 2003
    Messages:
    298
    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
     
  11. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1064038