Results 1 to 8 of 8
  1. #1
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118

    Update Query on Non Updatable Query

    I am trying to build an update query that populates a “Reference” field with a number from another query. The query that contains the number is grouped by ‘First’ Reference because there are a number of reference numbers available. I first tried to make this query my UPDATE QUERY but noticed as soon as I made it an update query it removed the groping so was no longer the required results. I therefore saved this query and pulled it into a new query along with the table I am tying to update. This looked like it would work when viewing the results however when I run the query I get “you must use an updatable query”. I then thought maybe I could just pull in the table I want to update and in the ‘UpDate To:’ line use a DLookUp function to pull the results from the other query but can’t seam to get that to work either.
    This is what I tried

    DLookUp("FirstOfTriCare_TA_Auth","Qry_Update_Ref_A T","Holding_ID"="Holding_ID")


    FirstOfTriCare_TA_Auth being the ref number I am trying to update
    Qry_Update_Ref_AT being the query that it is in
    Holding_ID Being the Unique ID in both the query and table I am trying to update

    Any suggestions on how to achieve this appreciated

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Concatenate variable input:

    DLookUp("FirstOfTriCare_TA_Auth", "Qry_Update_Ref_A T", "Holding_ID=" & Holding_ID)

    Is FirstOfTriCare_TA_Auth a field generated with First() aggregate function?

    First() and Last() are unreliable because tables do not have inherent order - perhaps you should use Min() or Max().
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    When you run an update query involving a table and query (or two tables), the error message 'you must run an updateable query' will occur if its not clear to Access which records to update.
    To fix that, you need to specify unique records by changing the query SQL to UPDATE DISTINCTROW ....

    However if any part of a query is read only (e.g it includes an aggregate or crosstab query), the final query will still be read only
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The query that contains the number is grouped by ‘First’ Reference
    Be wary of that if the records are not sorted by that field and are not unique. First and Last are often used without sorting, and the result is that one day it can be one record but another day, something else. You should be able to update a field in a table via code as long as you don't have a lock on the record(s). Either post your code attempts or copy db, compact/repair, zip and post here with instructions to replicate issue for more focused help.
    Last edited by Micron; 09-02-2021 at 01:18 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118

    still struggling

    Thanks all for feedback, I have tried several suggestions but unfortunately to no Avail. I have therefore taken @Micron ‘s advice and zipped an example of the problem.
    Summary of the problem: I am trying to build 2 x update queries
    1/ to update Reference field with the next available “TriCare_TA_Auth” number from “Qry_Next_AT”

    2/ to update the “DateUsed” field with Current date “Now()” in “tbl_TriCare_TA_Auths” next to each of the records used in the first query.

    Tbl_TEMP_Invoice_Holding is a temporary table with a record representing an invoice line for an appointment for a service user. The table includes a “Reference” field which requires in essence a purchase order number (Called an AT Number) for each appointment, providing one is available (not all service users use AT Numbers. E.g. Bo in the example).

    Tbl_TriCare_TA_Auths holds all the AT Numbers allocated to Service Users (Patients) along with a DateUsed Field. Note: a batch (up to 30) of the same AT Number will be issued to each Patient and one will be used on each appointment until all gone.

    Qry_Next_AT looks at the “tbl_TriCare_TA_Auths” but filters to only show valid numbers e.g. UseBy date not expired and Date Used is Null.

    The issue I have had is that Query “Example1” shows the results I want in my update query but when I make it into an update query it loses the grouping so is no longer correct.
    If I use the results of “Example1” in another query (Example 2) it becomes “not Updatable” so will not work as an update.
    I don’t know if I am going about this correctly, but any solution would be appreciated. I am not a programmer or SQL/VBA expert so please give clear instructions. Thanks
    Exsample.zip

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Advise not to use punctuation/special characters in naming convention. Why an asterisk at beginning of field name?

    Field "Referance" is a misspelling of "Reference".

    Why does Bo not get a number assigned?

    Why are there multiple records for each Auth number?

    I think this is going to require VBA if you want to populate all Contact records in one process. Something like:
    Code:
    Sub SetRef()
    Dim db As DAO.Database, rs As DAO.Recordset, strAT As String
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT DISTINCT ServiceUser_ID FROM tbl_Temp_Invoice_Holding WHERE Referance IS NULL AND ServiceUser_ID<>52935426")
    Do While Not rs.EOF
        strAT = DMin("TriCare_TA_Auth", "tbl_TriCare_TA_Auths", "DateUsed IS NULL")
        db.Execute "UPDATE tbl_TEMP_Invoice_Holding SET Referance='" & strAT & "' WHERE ServiceUser_ID=" & rs!ServiceUser_ID
        db.Execute "UPDATE tbl_TriCare_TA_Auths SET DateUsed = Date() WHERE TriCare_TA_Auth = '" & strAT & "'"
        rs.MoveNext
    Loop
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Ant_Snell is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jul 2021
    Location
    UK
    Posts
    118
    Thanks June7, I will give this a go!

    In answer to your questions:
    The table containing *ContactName is exported to CSV for upload into an accounting system, the asterisk represents a mandatory field but more importantly it is the field name the accounting system recognises
    Referance, My error, I will correct this
    Bo is private service user who does not provide AT, Numbers (in the full record set only abut 50% will have AT numbers)
    The Auth Numbers are issued in batches (of all the same number)per service user. e.g. you could have up to 30 of the same number and then mark one off for each appointment until all gone.

    PS Not tried this yet but just noticed that "Bo" is referenced in the code

    tbl_Temp_Invoice_Holding WHERE Referance IS NULL AND [*ContactName]<>'Bo'")


    Bo is just a test name in my example but as mentioned above, probably 50% of the records would not have an available AT Auth or would not need one. Would that have a baring on this code?
    Thanks

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You must have read early version of my post. I changed SQL filter to not reference "Bo" but the ServiceUser_ID instead.

    Need some criteria for excluding that 50% of users. Right now I 'know' only Bo does not get AT because you said so. How do you 'know' a user does not utilize AT number? If you don't have any way to identify those users, AT will have to be assigned even if user never uses. Sure, it 'wastes' AT nums but there is no alternative - there must be filter criteria.

    What do you mean by "user does not provide AT" - it seems you are assigning AT to users.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. query not updatable
    By diegomarino in forum Access
    Replies: 33
    Last Post: 10-27-2020, 03:51 PM
  2. Updatable query
    By Syla in forum Queries
    Replies: 3
    Last Post: 03-25-2020, 10:22 AM
  3. Replies: 4
    Last Post: 02-08-2019, 07:50 PM
  4. Replies: 3
    Last Post: 05-08-2017, 04:53 AM
  5. Updatable query
    By Starscream in forum Queries
    Replies: 3
    Last Post: 04-26-2013, 07:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums