We help IT Professionals succeed at work.

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

Michael Paravicini
on
82 Views
Last Modified: 2018-11-18
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

Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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.
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

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"?
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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.
CERTIFIED EXPERT
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...