Is there a better way to refresh a datasheet in a subform using comboboxes?

Michael Paravicini
Michael Paravicini used Ask the Experts™
on
I am using a datasheet in a subform with a few columns which use combobox. Each time the user changes the combobox value I need to refresh the entire datasheet (usually not more than 30 rows) for this new value and for any matching row. Unfortunately, these updates make the screen very restless. Is there a better way to update these rows and then refresh the screen at the end? Thank you so much for any help. Regards Michael
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark EdwardsChief Technology Officer

Commented:
You don't say where your combobox is.  Is it on the parent form and you're using it to filter the subform, or is it a combobox on the datasheet itself.

Where is the update being made?  A little more design detail, please.  The right answer depends on it.

Author

Commented:
Sorry Mark you are absolutely right. The combobox is on the subform and part of the datasheet. It is not being used as a filter.... Hope this helps. Cheers Michael
Mark EdwardsChief Technology Officer

Commented:
o.k., so it's on the datasheet and it's not used as a filter.  That must mean it's being used to set the value of the underlying (assumed to be bound) field from the combobox's droplist.

Since you say we're only dealing with Access here, and you haven't stated that there is any VBA code involved, then you must be trying to save the new value to the bound recordsource field.  A simple Save Record command:

Private Sub Combobox_AfterUpdate()
    DoCmd.RunCommand (acCmdSaveRecord)
End Sub
'OR
Private Sub Form_AfterUpdate()
    DoCmd.RunCommand (acCmdSaveRecord)
End Sub

Open in new window


on the combobox's  or subform's AfterUpdate event, would flush the new value to the underlying bound table.  The difference between the two is that the combobox's afterupdate would save the record to the table IMMEDIATELY after updating the control.  The form's afterupdate event would update the table AFTER you move OFF THE RECORD.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark EdwardsChief Technology Officer

Commented:
The above solution seems so simple, I can't help but feel there is more to what's going on that you've let on so far.

Just what is happening (other than a simple save to the underlying, bound Access table field) to cause such "restlessness"?
Distinguished Expert 2017
Commented:
Private Sub Form_AfterUpdate()
    DoCmd.RunCommand (acCmdSaveRecord)
End Sub

This code would create an endless loop.  Whenever the code or Access decides that it is time to save a record, the form's BeforeUpdate event runs.  AFTER the record is saved, the AfterUpdate event runs.  Forcing the record to save again in the AfterUpdate event means that the BeforeUpdate event will run and then the AfterUpdate and you are forcing the update to run again - get the picture?

mpim,
I don't understand why you need to refresh/requery the form when a combo is changed.  Access automatically saves a record when you leave it.  You don't need to do anything to make this happen.  Sometimes, you want the user to be able to force a record to save immediately.   That is usually done via a button.  The code then runs the save command:

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveREcord
End If

Using Me.Refresh or Me.Requery do other things.  A SIDE EFFECT is that they force the current record to save.  When you use either of these commands to save a dirty record, you are causing unintended side effects and that may be what you are experiencing.

A trick that is sometimes recommended is:
If Me.Dirty Then
    Me.Dirty = False
End If

I have never run into the issue that the trick is supposed to overcome so I don't use it but I offer it as an option because you will see it recommended.

If you find articles that recommend using Me.Refresh or Me.Requery to save a record, make a note of the author and avoid all his recommendations going forward.

Me.Requery tells Access to rerun the form's RecordSource query.  This will bring in new records, drop deleted records, and show the changes for updated records.  It is way too much overhead for saving a record, plus it repositions the form back to the first record in the recordset and so is quite annoying.

Me.Refresh tells Access to update any existing records.  It will not show records that were added by others since you originally opened the form and it will flag deleted records as #deleted# rather than removing them.
Mark EdwardsChief Technology Officer

Commented:
@mpim:  Now the next question.... when you said:
Each time the user changes the combobox value I need to refresh the entire datasheet (usually not more than 30 rows) for this new value and for any matching row.
that sounds like you want to save the new data right then and there (use the control's Afterupdate() event), right?  Also, what did you mean by "and for any matching row"?

As Pat said earlier, Access will automatically save a record (write any changes to the underlying table) when you change to another record or close the form.  If you want to save the data before any of that, then you'll need to have a "Save" button for the user to click if you want to wait for your user to click it.  If you want it done automatically every time the data in a single field is changed, then you'll need to use the control's Afterupdate() event to run your save command.  

Also be aware of the effects of any code in any Beforeupdate() event which can be used to enforce any special data conditions before allowing the new entry to be saved (that's why the "Before" events have a "Cancel" variable that you can set to "True" to prevent the entry from being saved and forcing the user to make it a correct entry in order to get it saved).

There's a lot of , "but if...", "however...", etc. that could be overthought into this, but if you give us a little more description as to what you're actually trying to do, we could cut right to the solution.

Author

Commented:
I am sorry for not being clear. I am purely referring to the best way to refresh the screen without too much flickering... If the user changes a combobox I will display the same value in a few other rows. To achieve this is loop through every recordset of this datasheet and set the new value of the combobox which causes the screen to flicker a lot... I was wondering if there was a better way to achieve this... Thanks a lot! cheers Michael
Mark EdwardsChief Technology Officer

Commented:
The best way to refresh the screen depends on where you are making your changes.
Let me see if I have this straight:
It sounds like you are making a selection from a combobox located on a datasheet.  I'm going to assume that this combobox is bound to a field on the datasheet recordsource so that when you select an item in the combobox droplist, it changes the value of that bound field on the datasheet form.  However, the value in the underlying table is NOT changed until you do something to cause the change to be saved (written) to the recordsource's underlying table that the field is connected to.
Then you use the same value to update other "rows" (records?)  How you do that makes a difference:

-If you are running a query to update the underlying table instead of the datasheet controls, then you'll need to get the new values on the table to "bubble-up" to the form display - that's a requery.  Since you say you are currently requerying, I suspect that this is what you are doing.  You don't need to requery for each row change, just after the last change when you're ready to refresh the display.

-If you are changing the other "rows" by using VBA to change the value in a datasheet control, then you need to be aware that the saving of data changed by VBA works differently that changed by manually clicking on a different record.  Here you going to need to test the "Dirty" property of the form and run a form-level save command (NOT a requery) to flush all the control data changes to the underlying table.  That should do it,

Sorry to have to ask for details of what you are doing, but you see how finding a "better way" depends on it.  Without the details, we  have to try and cover all possibilities.
Mark EdwardsChief Technology Officer

Commented:
We can try to "fix" your issue by doing the bare minimum to get rid of the "flickering", or we can try to get you to redesign your whole datasheet updating processing to a way we know works flawlessly and avoids pitfalls, if you really want to get technical.
Distinguished Expert 2017

Commented:
If the user changes a combobox I will display the same value in a few other rows
This is as clear as mud.  Why does updating one record cause other records to change?  If what you are saying is that when a user adds a new value to the combo's RowSource, you want it ti be available in all rows immediately, then in the GotFocus event of the combo, use:

Me.myCombo.Requery

That way, the RowSource will be updated before the user chooses a value.

PS - personally I think allowing users to add values to lists on the fly just makes it easier for them to add typos.  I recognize that you might have a situation where the list is too dynamic to force a more controlled procedure where only an "admin" gets to add new values, but at least consider it.

Author

Commented:
Thank you for your help! As always it is very much appreciated...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial