List/combo Box Refreshing

[limperger]

Hi everyone!

What about this situation? In a shared database, where people works with form, having these list boxes, imagine that 2 users enter data for the, say, same costumer, who was not in a table until then and, therefore, was not displayed in the list box when opened. You could eliminate the duplicate entry with the find duplicates wizard, but imagine that there is a type-error and the 2 entries are slightly different. I presume that this 2 entries will be treated as being different. Am I right?
How can this situation be avoided? Is it possible a real-time list box refreshing?

Best regards from Barcelona

[Stewart Ross]

Hi limperger. You are right that you could end up with duplicate customer records if the list box data is not refreshed. You can force a refresh of the list or combo box concerned using the Requery method of the list or combo. If your list is very large there could be a slight delay as the data is refreshed.

One way to have automatic refresh of the list data is to use the After Insert event of the form, which is triggered when a new record is added.

To try it, open the form in design view. Select the Event tab and right click the After Insert event, select Build, Code. In the VB skeleton add

Me![list box control name].Requery

replacing the name above with the actual name of the control you want to requery.

If the After Insert event is not frequent enough in your circumstances you could use the list box's On Got Focus event (but this would requery every time a user selected the combo), or the form's On Current event (which would trigger a requery every time a user moved from one record to another). The choice is yours, really.

-Stewart

[limperger]

Stewart:

Just one question: this refreshing event applies to everyone who has the form opened or just only you?

Thnak you very much!!

Best regards

[Stewart Ross]

Hi limperger. The event code updates the local view of the list data seen by the current user. A refresh is necessary for combos and list boxes because the combo contents are loaded only once, at the time the form is opened. The list contents do not change in response to new records added to the combo's record source until the current form is closed and re-opened again, or a requery is called as discussed.

I can see why you are asking, but I must admit to being a little puzzled by your question, as Access is a PC application running on one PC at a time. Even if the underlying tables are being shared on a network drive each user is opening an individual copy of the database front end. Access is not a back-end database running on a server (unlike SQL Server or Oracle for instance).

As long as you have the users using the same front-end form with the same refresh built in each will see an up-to-date view of the data.

-Stewart

[limperger]

Hi Stewart:

I will proceed as suggested. Thank you very much for your help!!
Secondly, I had no intention of troubling you with my questions...It's only I have little idea of Access, beyond what can be found in ordinary manuals... Sorry for this!! : ( !!!

Best regards!

[limperger]

Hi!

The list box I have displays the surname, name and a personal ID number (not an autonumeric field but a number you type). I have written the following code:

Private Sub Form_AfterInsert()
Me![Lista242].Requery
End Sub

This code is in response to:

Me![list box control name].Requery

I understand that it should work right now once you have typed the surnname, name and personal ID of a new person into the database. That is, once you have this information typed, the list box should refresh so as to display the new information. But no visible change seems to appear. I may have done something wrong. But what?

Best regards!

[Stewart Ross]

Hi limperger. There is nothing wrong with your code. It would appear that the frequency of update using the After Insert event of your current form is not suitable for what you need.

I think the safest option for you at present is to place the Requery code line in the On Current event of your present form instead of the After Insert event. I did say that the choice of which event to use was up to you, as I cannot second guess how your form is being used.

Using After Insert will requery your listbox whenever your user adds a new record to this form (not in response to unknown events in another form or another user's copy of the database). It would be suitable for what you need if your users are frequently adding new records through that form, but if this is not the case the On Current event is almost certainly more suitable.

If you really want the listbox to be up to date at all times use the On Got Focus event of the listbox control itself, but as I mentioned in my previous response it will then refresh every time a user pulls down the listbox, and I think this is likely to be too often. Anyway, it is not something I can guess at - it is for you to choose which event is most suitable for your circumstances.

-Stewart

[limperger]

Dear Stewart:

Just a little more question. Let me explain: I definetely choose the gotfocus litsbox event,as I think is the one that better fits what the form is supposed to do in my database. I have typed the following code:

Private Sub Lista242_GotFocus()
Me![Lista242].Requery
End Sub

It works fine. But my question is about setting all things straight. When you commented that the On Got Focus event refreshed everytime an user pulled down the list, I understood that this refreshing event would take place everytime an user scrolled down the list box using the scroll down bar on the right. Nevertheless, what happens here is that the listbox, although it does refresh itself, the refreshing event takes place when you select a record from the listbox and not when you scroll down with the bar provided to that end. I don't know for sure if this is the case or I have made something wrong and really there is a way to refresh the listbox with the scroll bar and without selecting a record in the listbox.

Thank you very much for your great great help!!

Best regards!

[Stewart Ross]

Hi again limperger. On Got Focus occurs when a user 'lands on' the listbox control by clicking on it or by tabbing to it from a previous control. The event occurs as soon as the control is in scope, which is before a selection has been made from it.

With small recordsources it will refresh relatively quickly, so it will offer an up-to-date view before any selection is made. With large data sources the refresh may prevent the control from displaying a full view of the data until completion, but the event still takes place when the control is first landed on, not when it is scrolled or a selection made.

To convince yourself that the event occurs before any selection is made simply place the following test messagebox call after your listbox requery line:

msgbox ("control refresh called")

and you will see on screen that the messagebox appears upon tab to or click onto the control, not when you scroll it (though, of course, you can't scroll it without firing the on-got-focus event first, so it might look as if you have to scroll the list to refresh it - but you don't.)

-Stewart