How to add a text field (seemingly) to a complex select query.

Posted on 2014-08-19
Last Modified: 2014-08-21
My user wants to add a read-write row level field to a form backed by a complex select query where all existing tables are read-only. I can add a new table to the database with the necessary key fields to link it to the rows of select query, but I would like some guidance with the best approach including the methods to add the record and update fields. My VBA experience is mostly with Excel, but have written several simple Access applications.

I would like the field to appear in the form as if the field is always present even though it is not. I understand I will have to requery the form data and the user will experience some screen flashing.
Question by:WilbertWaterbury
    LVL 84

    Accepted Solution

    Complex select queries are rarely updateable, so if the current query is NOT updatedable, then adding another table won't help matters - and you cannot identify specific Tables/Fields in your Query that should be updateable, while leaving others as non-updateable.

    If you can make the query updateable, then you could simply set the Form Controls (i.e. the textboxes, dropdowns, etc) to Enabled = True and Locked = False. This would stop user from modifying those fields, and you could leave your new field as a read/write value.

    As I mentioned earlier, however, this all depends on if you can make that complex query updateable ...

    If you like, you could use SQL/VBA to handle this, perhaps in the AfterUpdate event of your textbox. Something like this:

    Sub MyControl_AfterUpdate()
      Currentdb.Execute "UPDATE SomeTable SET SomeField='" & Me.MyControl & "' WHERE IDField=" & YourIDValue
    End Sub

    Obviously, you'd have to change the names of Tables and Fields to match those in your database.
    LVL 9

    Assisted Solution

    by:Armen Stein - Microsoft Access MVP since 2006
    Or, you could put the one updatable field in a tiny subform that's related to the main form using Master/Child properties.

    Armen Stein

    Author Comment

    Thank you both @ScottMcDaniel and @ArmenStein. I am considering both of your comments and will respond shortly.

    Author Closing Comment

    @Scott's UPDATE command was the ticket (plus an add record if one did not already exist) and was a huge help thanks to you both for your ideas!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now