The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
Is there a better way to refresh a datasheet in a subform using comboboxes?
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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
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:
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.
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
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.
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"?
Just what is happening (other than a simple save to the underlying, bound Access table field) to cause such "restlessness"?
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.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
@mpim: Now the next question.... when you said:
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.
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.
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
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.
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.
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.
If the user changes a combobox I will display the same value in a few other rowsThis 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
Where is the update being made? A little more design detail, please. The right answer depends on it.