Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2017
    Posts
    1,230

    Updatable query as form's record source

    Currently I have a form with a table as record source. I want users to be able to set filter condition to form depending on values in linked tables. It would be possible, when I replace the table with a query where fields needed for filtering (3 fields from 3 different tables) are added. I don't want to edit contents of those fields - they are there only so I can set filter property for form - but the recordset must contain them.

    In https://www.datanumen.com/blogs/15-r...-access-query/ are listed conditions for MS Access query to be updatable. According p.7 the query is updatable when it consist JOIN's only of same direction (LEFT or RIGHT, INNER ?). According this when I use several LEFT JOIN's it must be updatable?



    I tested this:

    SELECT t1.*, t2.FilterField1
    FROM Table1 t1 LEFT JOIN Table2 t2 ON t2.PKField2 = t1.FKField2
    --- is updatable (when I run query in query designer mode, I can edit querytable values).

    SELECT t1.*, t2.FilterField1
    FROM (Table1 t1 LEFT JOIN Table2 t2 ON t2.PKField = t1.FKField) LEFT JOIN Table3 t3 ON t3.PKField3 = t1.FKField3
    --- is not updatable (I can't edit querytable values)!

    Why? Is there a left-out condition that only one JOIN is allowed - then whole p.7 is moot in link above? Or Access sees <...FROM (Table1 t1 LEFT JOIN Table2 t2 ON t2.PKField = t1.FKField)...> as subquery (p.6 in link above - what makes p.7 moot again, as brackets are a must with several JOIN's in Access), Is there a way around this in Access? (I'm really annoyed with MS Access SQL Query syntax )


    When there is no workaround in Access, then as my database uses SQL Server DB as BE, there is another possible solution - to use a SQL Server view instead of table as form's recordsource. SQL Server has less constrictions, but essential is, that only values from one table may be updated. I'm content with this, but before I'm changing my design, can someone clear for me, how will SQL Server and Access handle the case, where those 3 fields are present in form's recordsource, but never really updated from form's point of view (there even will not be any controls linked to them). When the record is updated, do those fields count as updated for SQL Server or not (i.e. are other fields of record updated when I have those 3 fields present in recordsource)?
    Last edited by ArviLaanemets; 03-01-2021 at 08:54 AM.

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,927
    SELECT t1.*, t2.FilterField1
    FROM (Table1 t1 LEFT JOIN Table2 t2 ON t2.PKField = t1.FKField) LEFT JOIN Table3 t3 ON t3.PKField3 = t1.FKField3
    --- is not updatable (I can't edit querytable values)!
    in query design, open the query properties and try changing the dataset type from dynaset to dynaset inconsistent updates

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,263
    Given:
    FE = Access
    BE = SQL Server Express

    Tables are SQLSE linked with ODBC to Access


    ************************************************** *********
    Form1 record source saved query (qryTable1)
    SQL =
    Code:
    SELECT tblTable1.Table1ID_PK, tblTable1.Table_2_ID_FK, tblTable1.Table_3_ID_FK, tblTable1.Table_4_ID_FK, tblTable1.ThisField, tblTable1.ThatField, tblTable1.TheOtherField, tblTable1.SomeOtherField
    FROM tblTable1;

    Form1 has 3 unbound combo boxes to select criteria. VBA filters form record source.


    Attached dB does not have any data. Just a framework as an example.
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    Join Date
    Apr 2017
    Posts
    1,230
    To Ajax: I'll check this out (As I prefer to design exclusively in SQL view, I may have missed this option!).

    To Steve: It is not so simple!
    P.e.
    I have a table tArticles: ArtNo, ArtName, ArtType, ArtCode, CurrentSupplierNo, ...;
    ArtCode is a FK linked to field ArtCode in table tArtCodes: ArtCode, ArtCodeName, ArtCodeAlias, where ArtCodeAlias groups together any number of different article codes;
    CurrentSupplierNo is a FK linked to field SupplierNo in table tSuppliers: SupplierNo, SupplierName, SupplierAlias, ..., where SupplierAlias groups together any number of different suppliers, and is applied only for articles with ArtType = 1 (purchase articles);
    There is a 3rd parameter like those 2 too - but I think it is enough to get the idea. (And of-course there is a score of other parameters in tArticles which may be used for filtering the form directly.)

    Now what I need is to allow form filtering by all article codes which share specific article code alias, or/and by current suppliers which share specific supplier alias, or/and ... . So I need fields ArtCodeAlias and SupplierAlias (and 3rd one too) present in query which is form's recordsource, and at same time being able to edit the table tArticles from form.

    Having combos to select filters is no problem at all! I have designed them already, and they allow to select a specific filter condition, or the parameter being NULL, or No Filter selection. The problem is, that I can't apply Filter condition to field not present in form's recordsource!

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,682
    I have come across this and when using SQL server I simply created a view with all the additional table "lookups" that wouldn't be edited.
    Then left joining that view to the table I want to edit still works. As you say a single table.

    I think the restriction is caused by the way access "nests" the joins in brackets. Quite frustrating.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    Join Date
    Apr 2017
    Posts
    1,230
    Quote Originally Posted by Minty View Post
    I have come across this and when using SQL server I simply created a view with all the additional table "lookups" that wouldn't be edited.
    Then left joining that view to the table I want to edit still works.
    This looks promising! Thanks!
    Quote Originally Posted by Minty View Post
    I think the restriction is caused by the way access "nests" the joins in brackets. Quite frustrating
    Microsoft loves his brackets too much

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,682
    You can create editable view in SQL but it can cause more work that it saves.

    Every time I have looked at it I gave up due to the need to rework the design of all the other tables.
    I suspect if you work with that design in mind from the get go it is a lot easier to accommodate.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    Join Date
    Apr 2017
    Posts
    1,230
    Quote Originally Posted by Minty View Post
    I have come across this and when using SQL server I simply created a view with all the additional table "lookups" that wouldn't be edited.
    Then left joining that view to the table I want to edit still works.
    Worked like a charm! Lucky me having SQL Server as BE!

  9. #9
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    506
    Quote Originally Posted by Minty View Post
    You can create editable view in SQL but it can cause more work that it saves.
    That can be achieved in Access using a local table (in role of View), which hold the result of the complicated query and is joined with the main table. The final result is a rich recordset with usefull info and the ability to updates.

    Sometimes, we have to deal with a middle denormalized level between back-end and front-end.

    Now what I need is to allow form filtering by all article codes which share specific article code alias, or/and by current suppliers which share specific supplier alias, or/and ... . So I need fields ArtCodeAlias and SupplierAlias (and 3rd one too) present in query which is form's recordsource, and at same time being able to edit the table tArticles from form.
    I have undertake a difficult project that manage sets of electrical equipment and have to give to the user the ability of choose sets through all combinations of product series, installation ways, protection degrees, design line of cover plates and some other options. So, for each product series, I keep the results of some complicated queries in a local, "temporary" table joined with the table of chosen sets. Thus, I have all the necessary info for the filters available in an updatable query.

    The final query in design view:
    Click image for larger version. 

Name:	View.JPG 
Views:	10 
Size:	33.0 KB 
ID:	44459
    In this case, the table "tmpOrderSets" is the table on role of "View".

    I hope it helps,
    John

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

Similar Threads

  1. Replies: 6
    Last Post: 11-14-2017, 10:07 AM
  2. Replies: 14
    Last Post: 12-29-2016, 12:34 AM
  3. Replies: 5
    Last Post: 09-19-2016, 02:28 PM
  4. Replies: 23
    Last Post: 01-24-2012, 12:46 PM
  5. Replies: 1
    Last Post: 12-12-2009, 10:47 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 - Senior Forums