Access/VBA - Display linked fields in Updateable continuous Form

Posted on 2011-10-18
Last Modified: 2012-05-12
Hi Experts

Here is little background to my issue at hand.

I have a continuous Form where data source is a simple query from one table with no aggregate functions. This allow users to search, filter, sort and update single/multiple records, kinda similar to spreadsheet style. I have new requirement where another similar table needs to be joined (left join) to display few fields.

I tried following:

Create Left Join in underlying query of Form (sample: Select .... FROM TblFirst LEFT JOIN QrySecondTable ON TblFirst.SerialNum = QrySecondTable.SerialNum;)

Now Form is not Updateable, based on some previous recommendations i tried to change RecordsetType = Dynaset (Inconsistent Updates)... However i get into all kinds of trouble with existing filter, combos list throwing errors. So gave up on this idea..

Next I tried Dlookup to unbound fields but performance is way too slow for values to appear on screen. Its also affecting existing load and refresh times...

So based on above situation, i would like to know what else can be tried, that i can have updateable form with join fields displayed (read only) without suffering  from performance issues.

Question by:drivers
    LVL 39

    Expert Comment

    You can use subform for secondary table
    LVL 77

    Accepted Solution

    The join type should have no effect on updateability.
    It sounds like your second query uses something that makes it not updateable such as group by or other summary.

    If you cannot get Inconsistent Updates to work in this case then I don't see you have much alternative to creating a temporary table from your query and using that as the recordsource for your form.  OIf you are allowing updates as opposed to just filtering etc then there is more work to do in writing the records back to the source table as you will need to include an 'updated' field in the temporary table (and obviously set in the form_beforeupdate procedure)
    That will identify which records need to be written back.
     SInce we are talking about record volumes suitable for use in aform then I would be inclined to write all fields back rather than get into complexities of what has changed.
    LVL 1

    Author Comment

    sorry guys for coming back late, got away with other projects.

    als315,  subform cannot be used with continous form

    peter57r, i revaluate your first suggestion that join type should have no effect on updateability and found it is true if I use PK on both sides. However my current situation its bit tricky as the field (serialnumber) is the only common field on both sides but can have duplicate on both sides, i beleive that is preventating from udpates.

    Creating Temp table idea require overhead in terms of development and testing, so i am not inclined for this unless this is the only option available.

    Any further thoughts?

    LVL 77

    Expert Comment

    You cannot create an updateable query using joins of this nature (non_PK, duplicated fields) except by using InconsistentUpdates.

    The join type does not have any bearing on it.

    LVL 1

    Author Closing Comment

    Thanks everyone, looks like I have some thinking to do.. Points goes to Peter57r

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    758 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

    10 Experts available now in Live!

    Get 1:1 Help Now