Refresh All? (1 Viewer)

[ward0749]

I have a typical Access 2007 database with a main form that has combo boxes that are populated by other tables. To edit or add to the combo boxes I have separate forms. When I make the changes I want the combo boxes to be refreshed. I would imagine I can do it with a FormName.Refresh command. However, I am curious if there is a command to refresh all forms and reports in case I have more than one form open with combo boxes that link to the changed data.

Thanks

 

[TJPoorman]

Check this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=35918

 

[missinglinq]

I believe that you'll find that Refreshing a Form does not 'Refresh' the Comboboxes thereon. In point of fact, I believe the Comboboxes will need to be Requeried.

If FormA has a Combobo1 on it, and you go to another Form to add data for this Combobox, to bring the Combobox up to date, when returning to FormA, in its Code Module, you can use

Private Sub Form_Activate()
 Me.Combobox1.Requery 
End Sub

Alternatively, you could open your secondary Form in Dialog Mode. Doing this causes the code in the primary Form to stop executing until the secondary Form is closed, so you could use

DoCmd.OpenForm "SecondaryForm", , , , , acDialog
Me.Combobox1.Requery

Linq ;0)>

 

[TJPoorman]

I believe that you'll find that Refreshing a Form does not 'Refresh' the Comboboxes thereon. In point of fact, I believe the Comboboxes will need to be Requeried.

If FormA has a Combobo1 on it, and you go to another Form to add data for this Combobox, to bring the Combobox up to date, when returning to FormA, in its Code Module, you can use

Private Sub Form_Activate()

[B]Me.Combobox1.Requery [/B]
[B]End Sub[/B]

Linq ;0)>

For the sake of not having to code everything... if you were to go this way I would do some sort of loop to go through every combobox and requery them.

 

[missinglinq]

Good idea, TJ!

Linq ;0)>

 

[speakers_86]

Perhaps this is what your looking for.

 

[gemma-the-husky]

if you have a form open, and then change underlying data that would affect records displayed in combo boxes, these would not normally be updated until the next time the form is opened. (which is often inconvenient to have to do) if you manage a combo box from within the form, responding to the not in list event, then it isn't an issue, as the combobox gets requeried automatically

therei s no need to do anything with unopened forms.


so on a form, add a button with this sort of code.

for each ctrl in me.controls
if ctrl.controltype = accombobox or ctrl.controltype = aclistbox then
ctrl.requery
end if
next

alternatively add a doubleclick event to each combo box separately
comobboxname.requery
(maybe me.requery works as well)

 

[ions]

Another solution which may not be as efficient but is easy to code and guarantees that you will always get a refreshed combo box is to put the

Me.myComboBox.requery on the combo boxes's Got Focus Event.

 

[RainLover]

I have a typical Access 2007 database with a main form that has combo boxes that are populated by other tables. To edit or add to the combo boxes I have separate forms. When I make the changes I want the combo boxes to be refreshed. I would imagine I can do it with a FormName.Refresh command. However, I am curious if there is a command to refresh all forms and reports in case I have more than one form open with combo boxes that link to the changed data.

Thanks

Just to clarify my thinking. You appear to be on the Form that contains the Combo. You realise that the Combo does not contain the data you want, so you open another Form and enter the new information there. Then you go back to the original Form but the Combo still does not contain data you just added.

Is this correct or did I catch the wrong bus.

If I am correct there is much better/ faster/ easier way of doing this.

 

[ward0749]

Just to clarify my thinking. You appear to be on the Form that contains the Combo. You realise that the Combo does not contain the data you want, so you open another Form and enter the new information there. Then you go back to the original Form but the Combo still does not contain data you just added.

Is this correct or did I catch the wrong bus.

If I am correct there is much better/ faster/ easier way of doing this.

That's exactly the way I have it set up. I also have other forms that have the same combo set up that may or may not be open; depending on whether the user has that form open already or not.

 

[ward0749]

I believe that you'll find that Refreshing a Form does not 'Refresh' the Comboboxes thereon. In point of fact, I believe the Comboboxes will need to be Requeried.

If FormA has a Combobo1 on it, and you go to another Form to add data for this Combobox, to bring the Combobox up to date, when returning to FormA, in its Code Module, you can use

Private Sub Form_Activate()
 Me.Combobox1.Requery 
End Sub

Alternatively, you could open your secondary Form in Dialog Mode. Doing this causes the code in the primary Form to stop executing until the secondary Form is closed, so you could use

DoCmd.OpenForm "SecondaryForm", , , , , acDialog
Me.Combobox1.Requery

Linq ;0)>

The Form Activate does the trick. The other works great too, but I have my control buttons in the ribbon. So simple. Thanks.

 

[RainLover]

Happy to see you with a solution.

I was going to suggest the Not On List Event with some appropriate code.

Just in case someone is interested I will post it anyway.

Private Sub cmbZipcode_NotInList(NewData As String, Response As Integer)
    intAnswer = MsgBox("The postal code " & Chr(34) & NewData & _
    Chr(34) & " is currently not found in this database." & vbCrLf & _
    "Would you like to add it to the database?" _
    , vbQuestion + vbYesNo, "Address Type Not Found")
    
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblZipcodes([zipCode], [cityID]) " & _
                 "VALUES ('" & NewData & "', '" & Me.cmbCity & "');"
       
        DoCmd.RunSQL strSQL
     
        MsgBox "The new postal code has been added to the database." _
            , vbInformation, "New postal code added"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose a job title from the list." _
            , vbInformation, "Zip code not added, please reselect the zip code from the list"
        Response = acDataErrContinue
    End If
End Sub

This code is a simple copy paste. Names would have to be changed to suit.