none
In MS Access 2010 Web Database, requery action in the After Update event of Combo box is not always working

    Question

  • Note: I have the following issue only in my MS Access 2010 Web database. IT works great in client database.

    I have an unbound combo box (with two values) on the header of a form. The record source of the form is a query which references the unbound combo box in its criteria. In the After Update event of the combo box I have an embedded macro with a simple Requery action.

    When I select a value in a combo box, first time the form displays the values from the record source based on the value selected from the combo box. But when I close the form and open it again and select a value from the combo box, the error message shown here appears. If I go to the record source of the form and open the query, click on the save button in the query design pane, close it then it starts working again. But you close and reopen the form , the same error appears again.

    It seems as if the record source of the form is not recognized after the form is closed and reopened unless you open the record source and click on the save button in the design query pane of the record source.

    Please help. Thanks.


    • Edited by namwam Wednesday, May 23, 2012 5:28 PM Clarified that its a Web database
    Thursday, May 17, 2012 3:14 AM

Answers

  • Hi Nam,

     

    Thanks for the detailed repro steps.  I was able to reproduce your issue and I was able to find a workaround for this issue.   I have modified your steps to detail how I was able to get this to work without showing errors. 

     

    Scenario:

     

    Customers table: CustomerID (text field), LastName, FirstName, Phone, Email

     

    Number of records: 2

     

    Step 1: In the navigation pane, select table 'Customers', on the Ribbon, in the group 'Forms' of the tab 'Create', click on the 'Form' icon (the first one in the group). A simple editable entry form gets created with the table column names displaying on the left as levels and the column fields displaying on the right as text boxes. Name the form as frmCustomers.

     

    Step 2: On the top of the form, create a layout cell and drop a combobox there - name it cbo1. In the Data tab of the property sheet of the combo box do the following:

     1.Row Source Type: select 'Value List'

    2.Row Source property: Type CustomerA, CustomerB (in separate line. These are CustomerID's)

     

     

    In the Event tab of the Combo box, click the ellipses (...) on the After Update event.  In the macro editor, select the action 'SetFilter'.

    Set the Where condition to: =[CustomerID]=[Forms]![frmCustomers]![cbo1]

    Leave the control name blank.

    Add a new action after the Set Filter, select the action 'Requery'.

     

    Step 3: Save the form and change its view from Layout to 'Form View'. Toggle between CustomerA and CustomerB values from the combo box. You will see the form below will correctly show the other corresponding values (LastName, FirstName, Phone, Email) of the customer.

     

    (If you are changing your existing database, then change the record source of the form back to the customers table.)

     

    I was able to publish this out to a SharePoint site and it works correctly.  

     

    Basically we are setting a filter on the form based on the combo box.  The method you were using will work fine in a client database, but since we are somewhat limited the actions we can take in a web database, the above method will be preferred.

     

     

    If you are still having issues with getting this database to work correctly, then please open a support incident with Microsoft Support and we can work on the issue further. 

    Thursday, May 24, 2012 4:52 PM

All replies

  • Hi,

    I try to get other people help on this question, please wait some time.

    best regards,


    Forrest Guo | MSDN Community Support | Feedback to manager

    Friday, May 18, 2012 5:33 AM
    Moderator
  • Hello NamWam,

    Try these steps with a copy of the db

    1) Open the Form in Design View

    2) Open the SQL for the Form in SQL View

    3) Copy the SQL to notepad

    4) Close the SQL for the Form without saving

    5) Delete the Form's Control Source from the properties Sheet

    6) Save the Form & Close the Form

    7) Open the Form

    8) Goto the ControlSource for the Form

    9) Paste your SQL from Notepad

    10) Save/Close the SQL

    11) Save/Close the Form

    12) Test to see if it works if works stop here or

    13) Manually run Compact & Rapair f works stop or

    14) Decompile, Compact & Repair, Compile.


    Chris Ward

    Sunday, May 20, 2012 4:26 AM
  • Instead of using a macro to requery the form place the requery in an event procedure, when the procedure runs you can examine the record source of the form to ensure it is doing what you expected.

    For example, if the name of the combobox is cboSource then place the following code in it's AfterUpdate event;

    Private Sub cboSource_AfterUpdate()
        Me.Requery
    End Sub

    Place a breakpoint on the "Me.requery" line then in the Immediate window enter "? Me.RecordSource" to examine the current record source of the form.

    I would also recommend that you use Event Procedures for your code instead of macros as they are far easier to debug.

    Monday, May 21, 2012 11:46 AM
  • Hi Namwam,

     

    Thanks for posting.  It appears that the requery action in the after update event of a combo box is not working 100% of the time. 

     

    I have attempted to recreate your issue, however, I am not able to.  KCDW and Ray have suggested some very good steps towards resolving the issue. 

     

    I am curious if the issue occurs if we create a brand new blank form and attempt to recreate the issue.  The behavior suggest that there may be other events and/or code interfering with the requery action.  Please list any other events you have on the form and/or combo box control.

     

    I hope that gets you going in the right direction!


    Best Regards,

    Dan F. - Microsoft Online Community Support

    -----------------------------------------------------------

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.


    Monday, May 21, 2012 3:27 PM
  • Is the form's recordsource assigned or modified dynamically at run time, or is it simply set in the .RecordSource property in design view and left alone after that?

    Does the form's recordsource query select from a table that doesn't always exists, but instead is created on the fly, maybe in the form's Open event, or in an event from which the form is opened?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, May 21, 2012 6:12 PM
  • Dirk,

    1. First please note that this is a WEB form in MS Access 2010 Web database.
    2. RecordSource is an embedded SQL query that references the unbound combo box in its criteria and it is simply set in the .RecordSource property in design view and left alone after that.
    3. The table used in the recordsource query always exists and is NOT created dynamically in any event.
    4. There is no event used in the form except for After Update event of the Combo box that has a simple Requery action
    5. Even after re-opening the form it works sometimes (about 10%) but does not work 90% of the time.

    Thank you for trying to help,

    Nam

    Monday, May 21, 2012 9:24 PM
  • First please note that this is a WEB form in MS Access 2010 Web database.

    Now *that's* a useful piece of information! Did you mention it before?  Looking back over the thread, I don't see it.   Knowing that would have spared Ray Brack the trouble of posting a VBA suggestion.

    So I take it you have a Sharepoint back-end?  I have to say I have no experience as yet with Access Web applications, so I can only speculate about possible causes.  Is there anything unusual about the form's RecordSource query?  Is it particularly complex?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, May 21, 2012 10:58 PM
  • Hi Nam,

     

    Is your web database being published to SharePoint and are you seeing the errors there?    If the file is not being published to SharePoint, is a web database necessary? 

     

    Does this particular issue happen in a client database?   I am curious to see if the issue is happening in a new, simplified database.  

     

    Could you elaborate a bit on the repro steps of this issue?   The more information you could provide, the better the other members and I can attempt to assist.   Otherwise, it may be a good idea to open a case with Microsoft Support to better assist if you would like to dig into the issue.


    Best Regards,

    Dan F. - Microsoft Online Community Support




    Tuesday, May 22, 2012 7:42 PM
  • Ray,

    My bad. I should have mentioned that I am working on an MS Access 2010 Web database. I've updated my original post. Although, I am not working in a client database, but for the sake of the readers of this post, I just tested the same scenario in the client database and it works great there.

    Thank you for trying to help,

    Nam


    • Edited by namwam Wednesday, May 23, 2012 6:38 PM
    Wednesday, May 23, 2012 5:35 PM
  • Dan,

    <<Is your web database being published to SharePoint and are you seeing the errors there? If the file is not being published to SharePoint, is a web database necessary? >>

    I've not yet published the database to the SharePoint. Yes, a web database is necessary as multiple users will be using it and they would not want to export the database to client (desktop) every time they need to read/update data.

    <<Does this particular issue happen in a client database?>>

    Although, I am not working in a client database, but for the sake of the readers of this post, I just tested the same scenario in the client database and it works great there.

    <<Could you elaborate a bit on the repro steps of this issue? The more information you could provide, the better the other members and I can attempt to assist. Otherwise, it may be a good idea to open a case with Microsoft Support to better assist if you would like to dig into the issue.>>

    Scenario:

    Customers table: CustomerID (text field), LastName, FirstName, Phone, Email

    Number of records: 2

    Step 1: In the navigation pane, select table 'Customers', on the Ribbon, in the group 'Forms' of the tab 'Create', click on the 'Form' icon (the first one in the gruop). A simple editable entry form gets created with the table column names displaying on the left as levels and the column fields displaying on the right as text boxes. Name the form as frmCustomers.

    Step 2: On the top of the form, create a layout cell and drop a combobox there - name it cbo1. In the Data tab of the property sheet of the combo box do the following:

    1. Row Source Type: select 'Value List'
    2. Row Source property: Type CustomerA, CustomerB (in separate line. These are CustomerID's)

    On the top of the property sheet, select Form and in the Data tab, set the following properties:

    1. Record Source: SELECT Customers.CustomerID, Customers.[LastName], Customers.[FirstName], Customers.[Phone], Customers.[Email] FROM Customers WHERE (((Customers.CustomerID)=[Forms]![frmCustomers]![cbo1]));

    In the Event tab of the Combo box, click the elliposes (...) on the After Update event. In the macro editor, select the action 'Requery'.

    Step 3: Save the form and change its view from Layout to 'Form View'. Toggle between CustomerA and CustomerB values from the combo box. You will see the form below will correctly show the other corresponding values (LastName, FirstName, Phone, Email) of the customer.

    Step 4: Close the form and open it again, almost 90% of the time, you will get the message shown here.

    Step 5: Leave the form open and in the layout view, open the record source of the form (it will open in a query design). Click on Save (you are saving the embedded query). Save the form (do not close it). Repeat step 3 above. Form will start working again. Close the form and reopen it, you will get the error (90% of the time) stated in step 4.

    Thanks,

    Nam


    • Edited by namwam Wednesday, May 23, 2012 6:35 PM
    Wednesday, May 23, 2012 6:34 PM
  • Hi Nam,

     

    Thanks for the detailed repro steps.  I was able to reproduce your issue and I was able to find a workaround for this issue.   I have modified your steps to detail how I was able to get this to work without showing errors. 

     

    Scenario:

     

    Customers table: CustomerID (text field), LastName, FirstName, Phone, Email

     

    Number of records: 2

     

    Step 1: In the navigation pane, select table 'Customers', on the Ribbon, in the group 'Forms' of the tab 'Create', click on the 'Form' icon (the first one in the group). A simple editable entry form gets created with the table column names displaying on the left as levels and the column fields displaying on the right as text boxes. Name the form as frmCustomers.

     

    Step 2: On the top of the form, create a layout cell and drop a combobox there - name it cbo1. In the Data tab of the property sheet of the combo box do the following:

     1.Row Source Type: select 'Value List'

    2.Row Source property: Type CustomerA, CustomerB (in separate line. These are CustomerID's)

     

     

    In the Event tab of the Combo box, click the ellipses (...) on the After Update event.  In the macro editor, select the action 'SetFilter'.

    Set the Where condition to: =[CustomerID]=[Forms]![frmCustomers]![cbo1]

    Leave the control name blank.

    Add a new action after the Set Filter, select the action 'Requery'.

     

    Step 3: Save the form and change its view from Layout to 'Form View'. Toggle between CustomerA and CustomerB values from the combo box. You will see the form below will correctly show the other corresponding values (LastName, FirstName, Phone, Email) of the customer.

     

    (If you are changing your existing database, then change the record source of the form back to the customers table.)

     

    I was able to publish this out to a SharePoint site and it works correctly.  

     

    Basically we are setting a filter on the form based on the combo box.  The method you were using will work fine in a client database, but since we are somewhat limited the actions we can take in a web database, the above method will be preferred.

     

     

    If you are still having issues with getting this database to work correctly, then please open a support incident with Microsoft Support and we can work on the issue further. 

    Thursday, May 24, 2012 4:52 PM
  • Looks like a bug, all right, or at least a "limitation".  Dan F's suggestion of using SetFilter instead of changing the recordsource seems to work.  I don't think you need to to do a Requery after the SetFilter action, though.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, May 24, 2012 8:28 PM
  • Dan,

    Thank you very much for providing a work around. This is a simple solution and it worked for me. You certainly deserve my vote.

    Nam

    • Edited by namwam Tuesday, May 29, 2012 11:37 PM
    Tuesday, May 29, 2012 11:36 PM
  • The above solution worked well for me, but now I am getting  an error when I am using the combo box in the  navigation form .Could someone please help me with the same.
    Thursday, August 30, 2012 7:46 PM
  • Hum, using a combo box in a navigation form sounds like a different question here?

    I don't see why a combo box would not work, but you going to have to provide more details here.

    And it not clear if you using the combo box in the navigation form, or in the form of which you want the filter to operate on (and why not place the combo box on the form you want it to filter with anyway?).

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Friday, August 31, 2012 2:30 AM
  • see link below

    http://www.access-programmers.co.uk/forums/showthread.php?p=1233221#post1233221

    Thursday, February 14, 2013 2:18 PM