Other Multi-User Cannot See New Record on Linked SharePoint List RRS feed

  • Question

  • I'm developing a multi-user Access app using linked Sharepoint Online lists for a non-profit organization but am having problems. When a user creates a new record in a list called DonorName via input on a main form and then moves on to, say, edit another record; another user using the app at the same time can't see the newly created record. The record’s there as the other user sees an incremented record count -- it's just not accessible to them.  Odd thing is that when a new donation record is added to a another list called Gifts using a pop-up form launched via a button click on the main form the new donation record is visible to the other user. I can’t figure out why one new record’s visible/accessible but the other’s not. I'm sort of lost at this point so help would be appreciated.

    Tuesday, June 18, 2019 11:30 PM

All replies

  • What you describe is normal.

    If you open a form, and start to add new records, other users who also opened the form will not see the new records until such time that the form is closed, and then re-opened.

    The popup form works because it is opened to the ONE record, and then when done you close the form.

    As a general rule, you should also adopts this kind of work flow:

    Let user search for data

    Display search results

    Let user pick record to edit.

    Think of using google.

    Think of using an accounting package.

    Think of using ANY type of software system.

    In near 99% of the cases, you are provided with the means to search for the record you want.

    Then results of search are displayed.

    Then you launch the form to the ONE record to edit.

    You do your work, close the form and now are right back at the search form ready to do battle with the next customer.

    A bank machine (instant teller) does not download every single user and THEN ask you what record to work on.

    Same goes for Google. You don’t download the whole internet into the browser and THEN go ctrl-f to find that information.

    And of course if you did download google, then ANY NEW sites would not appear in your “list” of sites and searches until such time you AGAIN re-load that list (you would have to re-load the browser to see new results).

    So the “list” of records is loaded ONLY one time on form load and startup.

    If you want to re-load the form, then you have to close it, re-open it.

    You can also place a button on the form, and behind that button you can have this code:


    So, the above would re-load the form contents, and that includes new records. (It just saves you having to close and then re-open the form).

    However you as a general rule (and especially for multi-user) have to adopt the same approach as google, accounting packages, and EVERY SINGLE other type of software you have ever seen or used.

    That software FIRST ask the user what they want.

    Then search results are displayed.

    Then you choose what from the “list” to work on.

    Because you ONLY launched the form to ONE record to edit, and THEN you close the form to return back to the search screen (ready to do battle with the next customer), then there is no need to re-load the form to show new records, since each time you are searching for the record you need.

    With above, then you are only loading the one record to the form, and thus don’t have to have the form re-load 1000’s of records over and over.

    You don’t want to open a form that is bound to a large table without first asking the user the simple question of hat record they are working on. Having the form bound to the large table is fine but you need to open the form with a “where” clause. This will thus only load the records you ask into the form.

    The added bonus in the above is not only are you saving TONS of data loading (why load up a form with 1000 records when the user only wants to work on one record?).

    So, any new records added to the system are available WHEN you load the form, but once the form is loaded, then new records added by everyone else are not available until such time you re-load the form, or force a requery.

    Think of when you use a web browser. You search and the results are display. But now while you view the results, you do NOT see all the new sites while just setting there.

    You don’t see millions of new web sites and text being added and pooping into your browser while just sitting there looking at the results.

    You only see new sites and data when you re-load (re-fresh) the browser.

    So when something new is added to the internet, then you ONLY see that new content if you search again and re-load up your browser. (You have to re-fresh the browser).

    Access forms work the same way.

    So, your browser, or even an access form does not “know” about all the other users adding new data (too much communication and too complex of a problem to manage.

    So such systems can’t notify the other users – and if you had 50 or 75 users, then your form would be updating every second, or even multiple times per second. And you would “lose” your spot in the form, since the 4<sup>th</sup> record in a few seconds now might become the 15 record. And this case gets even worse if the order of the form is sorted. (Again, new records being added would constantly change your location in the form).

    So, forms do not get updated automatic by OTHER users. You might have 10, or even 50 users on your system. You form thus might have to be updated 10 times or more even in 5 seconds of time. Your counter would be bouncing around like crazy!

    The amount of updates in a short time as you have more multi-users would grow and grow until such time you likely could not keep up with how fast things are changing.

    So, the form does a onetime load on start-up of the data. But additional data is NOT loaded into the form until you re-load the form.

    So even the record counter would constantly be changing and updating with lots of users. I suppose with 1 or 2 users, no problem, but the setup also has to work with 50 users.

    You might go back one record, and then 10 new records are added by other users, and you go forward, and the next 5 records would NOT be the same ones you just viewed.

    So since other users can add new records, then the records you see and are editing would fast become out of order, and out of date.

    So, the form ONLY loads records when the form is launched, and new other records added by other users do not appear until you close the form and re-open it.

    However, since all software tends to ASK you what you want before loading the form to the ONE result, then the above issue is not a problem anymore.

    So, you search, and any new record just added will show up in the search.

    You can then click on the one search result, and launch the main form to edit that one main record.

    So, over time, you realize that your forms likely should not have nor need nor even allow navigation.

    So think of any kind of software you EVER used that allows forms to edit data. Or think of using a browser.

    So best is to provide the user some kind of search form, say like this access form:

    So, in the above, the users types in a bit of the last name (or whatever users need to search by in your case).

    Then search results are displayed. You can then click on any row (the glasses icon) to launch the main form to edit the ONE main record. (And if there are sub forms – that is just fine).

    The main form thus allows the user to edit the ONE record, and then the user closes that main form and is right back at the above search form to do battle with the next customer.

    And having the user close that main form after working is also very nice for a VERY VERY long list of reasons. But here are a few:

    The user “feels” they are done working – finished the task with one customer. This work flow “closure” is very nice, since then the user can move on to the next task, tick off the box on their desk or move to the next pile.

    Closing the form FORCES and ENSURES that the data is written to the table. The result is data entry and things they do are NOT saved until If you have a “design” in which users leave open the form, they might edit a bit, go for coffee, or leave that form open all  the time. If their computer freezes up, or crashed, then that record will not be saved.

    So a design in which they don’t close the form means they by habit LEAVE it open all the time. Now a BIG chance exits that they will enter a record, and then go for coffee, but the record not yet saved.

    So, with a workflow that encourages the user to close the form when done, then such users are far more likely to get in the habit of close the form when done, and thus the data is now saved and available by any other user on the system.

    The above is really a form of “social” engineering. You design your software to encourage good behaviour by your users.

    And the above ALSO encourages the user to search before they start adding new things or customers. Since the first form is a search form, then users will SEARCH for something to edit.

    If you just toss up a form with 1000’s of records? Users will just whack the add new, and start typing in the data. This OFTEN results in duplicate data because you not providing a nice and easy user interface that encourages users to search before they start adding things.

    So, make the searching really nice, really fast, and really easy.

    The result is then users will be happy to search for something before they just decide to type things in.

    A user can’t work or edit or do ANYTHING unless they can find the record and data the need to work on.

    As a result, you want to spend considerable amounts of time with your software development team to provide VERY nice searching and picking out of data to edit.

    The NUMBER ONE THING your users will be doing ALL day is searching for data borer they can update such data. You can’t edit something until such time you find it, right?

    So not only does the above concept mean that multi-user issues are now not an issue, but your application also runs MUCH faster since you are not loading up forms with huge amounts of data that you not really going to use anyway.

    And the approach is far more friendly and easier for your users.

    So, you can place a button and me.Requery to re-load the form, but really at the end of the day your users will oh so much thank you a lot more if you providing a simple search form, and thus make a rather enjoyable work flow for your users.


    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Thursday, June 20, 2019 9:28 PM
  • Hi Albert,

    Just looked and saw your response. Thanks so much for the reply and especially the depth that you provided. I'm going to go over it a few times to make sure I thoroughly understand what your saying. One thing I thought of since initially posting was that I set things up on my main form wrong to begin with. As it stands the form/controls are linked directly to the table that's being updated and, instead, I should have linked them via a query. That idea seems to follow what your saying and the need to reloaded the form to see new records.

    I feel stupid for not realizing that right off but, hey, I guess late is better then never.

    Again, thanks for helping me out.


    Sunday, June 23, 2019 8:31 PM
  • Hey Albert,

    Following up from yesterday. Read your comments a couple of times and now realize I probably set things up wrong and need to make a change to the "search then populate" schema you talk about. While tying the form to a query, as I mentioned above, would probably work fine in a small 2-3 user group like I have it's lacking and not best practice.



    Monday, June 24, 2019 11:45 AM
  • No worries. The behavior I describe above works the same if you base a form on a query, or base a form directly on a linked  table. In BOTH of these cases, if you load the form (based on the query or linked table), then as you work, Access does not show other records added. This is the case for regular Access. Access to SQL server, and Access to SharePoint.

    So, basing a form on a query will not change this behavior. In all cases:

    You either have to re-query the the form to see other records (or close, and re-load the  form).

    In my search example, we  don't have to re-query the main  edit form, because after working on the one record, we close the  form. So, each time you re-load a form, then new records (added by other users) will be available. However, as noted, it makes little sense to load up a form with potential 100's or even 1000's of records to THEN just search them all to find and work on one record. You can do this, but loading up a form with lots of records is not only a waste of computer resources, it also slow, and as noted, such forms will not show other new records until such time you tell/ask the form to re-load (or you execute a re query command)   

    So, JUST using and basing a form on a query will not  change how access works anymore then basing that form on a linked table. The key concept is to have the form re-load the records which then will show other records added by other users. 

    Albert D. Kallal (Access MVP 2003-2017)

    Edmonton, Alberta Canada

    Monday, July 8, 2019 3:00 AM