none
Re-feeding a view with Me.Recordsource, then can't modify the entry

    Question

  • Hi!

    I have a continuous form feeded by a view, and it is showing records depending on a control in the header.

    When the form loads, I can alter the records shown, but as soon as I send a new SQL query to re-feed Me.RecordSource and try to change something, I get a small message down-left in Access (not a pop-up) saying that it is impossible to modify the record as this time.

    Me.RecordSource = "SELECT TOP 100 PERCENT * FROM table " & _ "WHERE var1_id_fk = " & Me.var1_id_fk & " AND var2_id_fk = " & Me.var2_id_fk & _ "ORDER BY criteria1 ASC, criteria2 ASC"

    Me.RecordsetType = 1 'Dynaset

    Am I forgetting something?

    Thank you for your help/suggestions!
    Vincent

    Wednesday, December 18, 2013 3:41 PM

Answers

  • Maybe you can take out the top 100 percent and apply the orderBy seperately.

    Me.RecordSource = "SELECT * FROM table " & _
                      "WHERE var1_id_fk = " & Me.var1_id_fk _
    				  & " AND var2_id_fk = " & Me.var2_id_fk 
    Me.RecordsetType = 1 'Dynaset
    Me.OrderBy = "criteria1 ASC, criteria2 ASC"
    Me.OderByOn = True


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Vincent DM Thursday, December 19, 2013 1:46 PM
    Wednesday, December 18, 2013 5:44 PM
  • Another possible option would be not to amend the form's RecordSource property, but instead use a query which references two unbound controls in the form as parameters.  The controls can be hidden.  You'd then assign the values of the two columns to these controls and requery the form.  To be absolutely sure of no interference by the filter you can also set the form's FilterOn property to False, so the query would be along these lines:

    SELECT *
      FROM table
    WHERE (var1_id_fk = Forms!YourForm!txt1_id_fk
          OR Forms!YourForm!txt1_id_fk IS NULL)
      AND (var2_id_fk = Forms!YourForm!txt2_id_fk
          OR Forms!YourForm!txt2_id_fk IS NULL)
    ORDER BY criteria1 ASC, criteria2 ASC;

    NB: do not declare the parameters as you need to examine them for NULL.

    The code would be along these lines:

    Me.FilterOn = False
    Me.txt1_id_fk = Me.var1_id_fk
    Me.txt2_id_fk = Me.var2_id_fk
    Me.Requery

    To reset the form to show all rows:

    Me.txt1_id_fk = NULL
    Me.txt2_id_fk = NULL
    Me.Requery

    You can reinstate the original filter with Me.FilterOn = True is you wish.

    Ken Sheridan, Stafford, England

    • Marked as answer by Vincent DM Thursday, December 19, 2013 1:48 PM
    Wednesday, December 18, 2013 6:24 PM
  • >>Am I forgetting something?<<

    2 things AFAICS:

    1.  The problem is probably only on your posting and not in your code but there must be a separator character (Space) before the keyword ORDER in the SQL String.  Personally, when I do SQL String concatenation using continued lines, I always include the space character at the end of the preceding line and the beginning of the next line like:

    Me.RecordSource = "SELECT * FROM table " & _
      " WHERE var1_id_fk = " & Me.var1_id_fk & " AND var2_id_fk = " & Me.var2_id_fk & " " & _
      " ORDER BY criteria1 ASC, criteria2 ASC"

    (I take out the TOP 100 PERCENT since it does nothing in this case).  If miss one of the 2 in the set, the SQL String still works.  If I have them both, ACE/JET simply ignore the additional separator and the SQL still works.

    2. >>Me.RecordsetType = 1 'Dynaset<<

    1 is actually Dynaset (Inconsistent Updates).  If you want Dynaset RecordsetType, it should be 0 and not 1.  See Microsoft Office article: RecordsetType Property for more info.

    Try with RecordsetType = 0 just in case setting to 1 on an SQL based on a single Table confuses Access. 

    Are Me.var1_id_fk and Me.var2_id_fk referring to Control names on the Form or Field names from the (current) RecordSource?  If they are Control names, are the Control bound or unbound?

     

      


    Van Dinh


    • Edited by Van DinhMVP Wednesday, December 18, 2013 10:01 PM Addendum
    • Marked as answer by Vincent DM Thursday, December 19, 2013 1:48 PM
    Wednesday, December 18, 2013 9:59 PM
  • I have a continuous form feeded by a view, and it is showing records depending on a control in the header.

    When the form loads, I can alter the records shown, but as soon as I send a new SQL query to re-feed Me.RecordSource and try to change something, I get a small message down-left in Access (not a pop-up) saying that it is impossible to modify the record as this time.

    Me.RecordSource = "SELECT TOP 100 PERCENT * FROM table " & _ "WHERE var1_id_fk = " & Me.var1_id_fk & " AND var2_id_fk = " & Me.var2_id_fk & _ "ORDER BY criteria1 ASC, criteria2 ASC"

    Me.RecordsetType = 1 'Dynaset

    Am I forgetting something?

    Hi Vincent,

    Strange problem, because I use the same technique "millions of times" without any problem. In that respect I normally never use the Filter and OrderBy properties, but just assign a new value to the RecordSource. (Only in very special cases with a very special control is may use the Filter property). The form has the RecordsetType property set to Dynaset (Inconsistent Updates) and that property I never change.
    By the way, setting the RecordsetType in VBA makes an new Recordset, as does the assignment of the Recordsource, so it is a kind of inefficient to use both.

    Commenting on the use of the space in continuing lines. As Van Dinh I place the space at the beginning of the line, for better recognisability and readability. It is "a must", so in my way of working unnecessary to place a space also at the end of the line.

    Imb.

    • Marked as answer by Vincent DM Thursday, December 19, 2013 1:48 PM
    Wednesday, December 18, 2013 10:51 PM

All replies

  • Vincent

    Maybe you just have to requery the form after changing the RecordSource.

    Me.RecordSource = "SELECT TOP 100 PERCENT * FROM table " & _
                      "WHERE var1_id_fk = " & Me.var1_id_fk & " AND var2_id_fk = " & Me.var2_id_fk & _
                      "ORDER BY criteria1 ASC, criteria2 ASC"
    Me.RecordsetType = 1 'Dynaset
    Me.Requery


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Wednesday, December 18, 2013 4:13 PM
  • Hi Bill,

    I tried but it did not work.

    Initially when this form loads it filtered (with DoCmd.OpenForm.......) so maybe requery the form restarts this filter... but I don't think so because my new requested records stay there, I just can't modity them.

    Wednesday, December 18, 2013 4:47 PM
  • Maybe you can take out the top 100 percent and apply the orderBy seperately.

    Me.RecordSource = "SELECT * FROM table " & _
                      "WHERE var1_id_fk = " & Me.var1_id_fk _
    				  & " AND var2_id_fk = " & Me.var2_id_fk 
    Me.RecordsetType = 1 'Dynaset
    Me.OrderBy = "criteria1 ASC, criteria2 ASC"
    Me.OderByOn = True


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Marked as answer by Vincent DM Thursday, December 19, 2013 1:46 PM
    Wednesday, December 18, 2013 5:44 PM
  • Another possible option would be not to amend the form's RecordSource property, but instead use a query which references two unbound controls in the form as parameters.  The controls can be hidden.  You'd then assign the values of the two columns to these controls and requery the form.  To be absolutely sure of no interference by the filter you can also set the form's FilterOn property to False, so the query would be along these lines:

    SELECT *
      FROM table
    WHERE (var1_id_fk = Forms!YourForm!txt1_id_fk
          OR Forms!YourForm!txt1_id_fk IS NULL)
      AND (var2_id_fk = Forms!YourForm!txt2_id_fk
          OR Forms!YourForm!txt2_id_fk IS NULL)
    ORDER BY criteria1 ASC, criteria2 ASC;

    NB: do not declare the parameters as you need to examine them for NULL.

    The code would be along these lines:

    Me.FilterOn = False
    Me.txt1_id_fk = Me.var1_id_fk
    Me.txt2_id_fk = Me.var2_id_fk
    Me.Requery

    To reset the form to show all rows:

    Me.txt1_id_fk = NULL
    Me.txt2_id_fk = NULL
    Me.Requery

    You can reinstate the original filter with Me.FilterOn = True is you wish.

    Ken Sheridan, Stafford, England

    • Marked as answer by Vincent DM Thursday, December 19, 2013 1:48 PM
    Wednesday, December 18, 2013 6:24 PM
  • >>Am I forgetting something?<<

    2 things AFAICS:

    1.  The problem is probably only on your posting and not in your code but there must be a separator character (Space) before the keyword ORDER in the SQL String.  Personally, when I do SQL String concatenation using continued lines, I always include the space character at the end of the preceding line and the beginning of the next line like:

    Me.RecordSource = "SELECT * FROM table " & _
      " WHERE var1_id_fk = " & Me.var1_id_fk & " AND var2_id_fk = " & Me.var2_id_fk & " " & _
      " ORDER BY criteria1 ASC, criteria2 ASC"

    (I take out the TOP 100 PERCENT since it does nothing in this case).  If miss one of the 2 in the set, the SQL String still works.  If I have them both, ACE/JET simply ignore the additional separator and the SQL still works.

    2. >>Me.RecordsetType = 1 'Dynaset<<

    1 is actually Dynaset (Inconsistent Updates).  If you want Dynaset RecordsetType, it should be 0 and not 1.  See Microsoft Office article: RecordsetType Property for more info.

    Try with RecordsetType = 0 just in case setting to 1 on an SQL based on a single Table confuses Access. 

    Are Me.var1_id_fk and Me.var2_id_fk referring to Control names on the Form or Field names from the (current) RecordSource?  If they are Control names, are the Control bound or unbound?

     

      


    Van Dinh


    • Edited by Van DinhMVP Wednesday, December 18, 2013 10:01 PM Addendum
    • Marked as answer by Vincent DM Thursday, December 19, 2013 1:48 PM
    Wednesday, December 18, 2013 9:59 PM
  • I have a continuous form feeded by a view, and it is showing records depending on a control in the header.

    When the form loads, I can alter the records shown, but as soon as I send a new SQL query to re-feed Me.RecordSource and try to change something, I get a small message down-left in Access (not a pop-up) saying that it is impossible to modify the record as this time.

    Me.RecordSource = "SELECT TOP 100 PERCENT * FROM table " & _ "WHERE var1_id_fk = " & Me.var1_id_fk & " AND var2_id_fk = " & Me.var2_id_fk & _ "ORDER BY criteria1 ASC, criteria2 ASC"

    Me.RecordsetType = 1 'Dynaset

    Am I forgetting something?

    Hi Vincent,

    Strange problem, because I use the same technique "millions of times" without any problem. In that respect I normally never use the Filter and OrderBy properties, but just assign a new value to the RecordSource. (Only in very special cases with a very special control is may use the Filter property). The form has the RecordsetType property set to Dynaset (Inconsistent Updates) and that property I never change.
    By the way, setting the RecordsetType in VBA makes an new Recordset, as does the assignment of the Recordsource, so it is a kind of inefficient to use both.

    Commenting on the use of the space in continuing lines. As Van Dinh I place the space at the beginning of the line, for better recognisability and readability. It is "a must", so in my way of working unnecessary to place a space also at the end of the line.

    Imb.

    • Marked as answer by Vincent DM Thursday, December 19, 2013 1:48 PM
    Wednesday, December 18, 2013 10:51 PM
  • Bill, Ken, Van and Imb,

    Thank you very much for your help, all your suggestions and answers enlightened me!

    I learned and discovered some new tricks because of you, and in this world of bytes and queries, that's worth gold :)

    Thanks!

    Thursday, December 19, 2013 1:56 PM