Solved Refresh Combobox on Subform from other form (1 Viewer)

mafhobb

Registered User.
Local time
Yesterday, 22:29
Joined
Feb 28, 2006
Messages
1,170
So I have this form "frmProjectHistory" with a subform "frmMaterialsUseSubform". There is a combobox on the subform called "cmbMaterialsUsed" with a row source: SELECT [tblMaterials].[ID], tblMaterials.[Material] FROM tblMaterials;

On this same subform there is a button that opens a continuous form (frmMaterials) that adds data to the table "tblMaterials". I use this button to add a material to that table with the aim to be able to select that material in the combobox "cmbMaterialsUsed".

Unfortunately, after I add a record in the table "tblmaterials" though the continuous form "frmMaterials" and I close "frmMaterials", the new material does not appear in "cmbMaterialsUsed" until I close the database and restart it. This means that I need to add some Refresh? Requery? code somewhere after adding the material and closing "frmMaterials", but for the life of me I have not figured it out yet.

I have tried Forms!frmPropertyHistory![frmMaterialsUseSubform].Form.Requery when closing frmMaterials
I have tried Me.requery when clicking on cmbMaterialsUsed
I have tried Me.requery when unlocking frmMaterialsUseSubform

Any suggestions?

mafhobb
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:29
Joined
Oct 29, 2018
Messages
16,216
In the Focus event of the Combobox, you could try something like:
Code:
Me.cmbMaterialsUsed.Requery
Hope that helps...
 

mafhobb

Registered User.
Local time
Yesterday, 22:29
Joined
Feb 28, 2006
Messages
1,170
That worked. Many thanks!

I did try use Me.Requery in a form event but that did not seem to work. Could it be that my try was requering the form but not the controls on the form (ie, the combobox) or was it some other reason? I mean, is it the specific control that I need to requery?

mafhobb
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:29
Joined
Oct 29, 2018
Messages
16,216
That worked. Many thanks!

I did try use Me.Requery in a form event but that did not seem to work. Could it be that my try was requering the form but not the controls on the form (ie, the combobox) or was it some other reason? I mean, is it the specific control that I need to requery?

mafhobb
Yes, you needed to requery the combobox, since the form's data is separate from the table you updated for your combobox.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:29
Joined
Feb 19, 2002
Messages
32,908
Open the continuous form modal. That stops code from running in the controlling form. Then on the line of code that follows the OpenForm method, requery the combo.

Me.mycombo.Requery

That line of code won't run until the popup form has been closed.
 

mafhobb

Registered User.
Local time
Yesterday, 22:29
Joined
Feb 28, 2006
Messages
1,170
Yes, you needed to requery the combobox, since the form's data is separate from the table you updated for your combobox.
I see. So when requering the form it is only updating the source data for the form, not for the controls if they have a difference control source.
Understood.
Many thanks
mafhobb
 

mafhobb

Registered User.
Local time
Yesterday, 22:29
Joined
Feb 28, 2006
Messages
1,170
Open the continuous form modal. That stops code from running in the controlling form. Then on the line of code that follows the OpenForm method, requery the combo.

Me.mycombo.Requery

That line of code won't run until the popup form has been closed.
I see. Another way to do the same thing.
Thanks!
mafhobb
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:29
Joined
Feb 19, 2002
Messages
32,908
Just FYI. Me.Requery not only requeries the form's recordsource but all the RowSources for listboxes and combos also.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:29
Joined
Oct 29, 2018
Messages
16,216
Just FYI. Me.Requery not only requeries the form's recordsource but all the RowSources for listboxes and combos also.
@Pat Hartman that sounds about right, but I just gave it a try, and it didn't work in this case for the same situation as this thread's topic.
...there is a button that opens a continuous form (frmMaterials) that adds data to the table "tblMaterials".
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:29
Joined
Feb 19, 2002
Messages
32,908
Was the other form still open? Had the new record not actually been saved? I don't let users update combos on the fly so I don't ever run into this. I also don't requery the form when what I want is to requery a combo.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:29
Joined
Oct 29, 2018
Messages
16,216
Was the other form still open? Had the new record not actually been saved? I don't let users update combos on the fly so I don't ever run into this. I also don't requery the form when what I want is to requery a combo.
Actually, here's what I did.

1. Created a form based on Table1 and added a listbox based on Table2
2. I also added to buttons: one uses Me.Requery and the other uses Me.List0.Requery
3. I opened the form and Table2 at the same time
4. When I edit a record in Table2, the listbox on the form immediately reflects the new data
5. However, when I add a new record in Table2, the new data never showed up in the listbox even if I navigate through the records on the form
6. When I click on the Me.Requery button, the record pointer goes back to the first record, but the listbox still doesn't show the new row fron Table2
7. When I click on the Me.List0.Requery button, the new row in the listbox showed up, without affecting the form's navigation

Hope that makes sense...

Also, I am not saying this is what I expect a user interface should be. It was just a quick test to see if there's a difference between the two Requery methods.

Cheers!
 

moke123

AWF VIP
Local time
Yesterday, 23:29
Joined
Jan 11, 2013
Messages
2,531
I use a lot of listboxes and often update data they're based on. I use the code below in the onClose event of the data entry form. Cant see why it wouldn't work on combos too. Just change acListBox to acComboBox. Might be worth a try.

Code:
Public Sub RequeryAllLists()

    Dim ctl As Control
    Dim frm As Variant

    For Each frm In CurrentProject.AllForms
        If frm.IsLoaded Then
            For Each ctl In Forms(frm.Name).Controls
                If ctl.ControlType = acListBox Then
                    ctl.Requery
                End If
            Next
        End If
    Next

End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:29
Joined
Feb 19, 2002
Messages
32,908
When you open a form, the recordsource is loaded into memory and so are the RowSources for the combos and listboxes. When updates happen, you will see them because the access form is set to automatically "refresh" the recordsources. However, refresh and requery are not the same thing. Only a requery will re execute the query and thereby bring in NEW rows. Refresh only shows the status of existing rows. You will see field changes and you will see #deleted# if the other form deleted a row. But you will NOT see new rows unless you requery the recordsource.

Your buttons also show you the downside of requering the form when that isn't what you actually want to do.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:29
Joined
Oct 29, 2018
Messages
16,216
When you open a form, the recordsource is loaded into memory and so are the RowSources for the combos and listboxes. When updates happen, you will see them because the access form is set to automatically "refresh" the recordsources. However, refresh and requery are not the same thing. Only a requery will re execute the query and thereby bring in NEW rows. Refresh only shows the status of existing rows. You will see field changes and you will see #deleted# if the other form deleted a row. But you will NOT see new rows unless you requery the recordsource.

Your buttons also show you the downside of requering the form when that isn't what you actually want to do.
Hi @Pat Hartman If you were addressing me, I am not sure what you are saying. I was merely trying to verify your previous statement.
Just FYI. Me.Requery not only requeries the form's recordsource but all the RowSources for listboxes and combos also.
You said all the OP needed to do was use Me.Requery. All I was saying is when I tried to apply it to the OP's case, it didn't work as the OP had intended. However, using Me.ListboxName.Requery did work.

All that other stuff about the difference between Refresh and Requery was not the issue. Also, it is clear to me that they are not the same. I also mentioned in the steps I provided in my previous post that "updates" to existing records/data was not the problem. It's the "adding" new ones that the OP was trying to fix.

Can you please elaborate on this statement?
Your buttons also show you the downside of requering the form when that isn't what you actually want to do.
What did the button show, and what did you think the actual intent was? Thank you!
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:29
Joined
Feb 19, 2002
Messages
32,908
My suggestion was to use Me.controlname.Requery. I would NOT requery the form unless that was what I actually wanted to do.
5. However, when I add a new record in Table2, the new data never showed up in the listbox even if I navigate through the records on the form
This is what prompted my reply. The point being that Access' automatic refresh doesn't bring in new records. Only a requery can do that.
What did the button show, and what did you think the actual intent was? Thank you!
Many people use Requery (and to a lesser extent, Refresh) incorrectly and not for their intended purposes. For example, many people use Requery to save a record and then complain that their form has been repositioned to the original first record.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:29
Joined
Oct 29, 2018
Messages
16,216
My suggestion was to use Me.controlname.Requery. I would NOT requery the form unless that was what I actually wanted to do.

This is what prompted my reply. The point being that Access' automatic refresh doesn't bring in new records. Only a requery can do that.

Many people use Requery (and to a lesser extent, Refresh) incorrectly and not for their intended purposes. For example, many people use Requery to save a record and then complain that their form has been repositioned to the original first record.
Hi @Pat Hartman. I agree with everything you just said, and I am well aware of what you are saying. But we seem to be getting away from the original reason why I did that little experiment. I created the test form, because I was curious to verify your statement from Post #8, which I quote:
Just FYI. Me.Requery not only requeries the form's recordsource but all the RowSources for listboxes and combos also.
I interpreted that to mean Me.Requery will also requery the row source of a listbox. Is that what you were saying, or did I misunderstand your meaning? Thanks for clearing up any confusion on my part. Cheers!
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:29
Joined
Feb 19, 2002
Messages
32,908
I just created a sample and it seems that Access no longer requeries the combos when you requery the form. I would log this as a bug.

In any event, if you do what I suggested and open the form as a dialog and requery the combo control. It works as desired.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:29
Joined
Oct 29, 2018
Messages
16,216
I just created a sample and it seems that Access no longer requeries the combos when you requery the form. I would log this as a bug.

In any event, if you do what I suggested and open the form as a dialog and requery the combo control. It works as desired.
Hi @Pat Hartman. Thanks for the clarification. That settles it. Please consider me no longer confused. Have a nice day! :)
 

Users who are viewing this thread

Top Bottom