requery in access

this will requery all comboboxes on a form

For Each itm In Me.Controls

If itm.ControlType = acComboBox Then
itm.Requery
End If
Next

yes, but the combo boxes are not on the same form as where the changes are being made...

Presuambly they are all open? In that case we will need to go through the forms collection!

try this:

For Each frm In Forms

    For Each itm In frm.Controls

        If itm.ControlType = acComboBox Then
        itm.Requery
        End If
    Next
Next

where do i enter this code?  how will it know to do this after updates/additions?

isn't there some way to just add onto what i already have? =[Forms]![ISelector]![CboISO].[Requery]

This code works but I just want to add some other combo boxes onto it.  I am not good with the syntax though.  Can't I just add a comma or something in order to add on another combo box.

thanks,
john

you would put this wherever your [Forms]![ISelector]![CboISO].[Requery] is (no equals required). In the on insert and on update events

you can do this combobox by combo box  line by line but this will do them all. And if you add to them it doesn't matter.

dan vella, i don't see the code you mention?  thanks...

Hi jz1english



to requery a control on a sub-form the syntax is:

   Me.FormName.Form.ControlName.Requery

or from another form other than the parent form
  Form_ParentFormName!ChildFormName.Form.ControlName.Requery

or to requery a control on the parent form
  Me.Parent.ControlName.Requery

Often with single controls, you can use ReCalc instead of requery.

Another good way to requery a combo or listbox is to set the rowsource
  Me.MyCombo.RowSource = Me.MyCombo.RowSource


Alan

jz1english here is the code as posted @ 10:05 on 18/11/2004:
-------------------------------------------CODE BELOW------------------------------
For Each frm In Forms

    For Each frm In Forms

    For Each itm In frm.Controls

        If itm.ControlType = acComboBox Then
        itm.Requery
        End If
    Next
Next
-------------------------------------------CODE ABOVE------------------------------

You need to paste this into the onInsert and afterUpdate event (or any event you deem appropriate) procedures. It will requery all comboboxes on all open forms. I have tested it and it works. I fail to see what else you require!

Dan

dan vella, i tried your script and it returns the following error:

"Compile Error: Variable not defined"

frm in the first line (For Each itm In frm.Controls) is highlighted...

Are you sure that you have used the correct code as the line you quote as the first line is actually the second line.

Ommitting the first part would also explain the error.... so here it is again:


For Each frm In Forms 'Goes through all open forms

    For Each itm In frm.Controls 'Goes through all controls on each form

        If itm.ControlType = acComboBox Then  'If the control is a combobox
        itm.Requery  'it refreshes the combobox
        End If
    Next  'Moves to next control in the collection
Next  'Moves to next form in the collection

sorry, it is the first line.  i changed the code to the following and got that same error:

For Each frm In Forms 'Goes through all open forms

    For Each itm In frm.Controls 'Goes through all controls on each form

        If itm.ControlType = acComboBox Then  'If the control is a combobox
        itm.Requery  'it refreshes the combobox
        End If
    Next  'Moves to next control in the collection
Next  'Moves to next form in the collection

tried this too, and same thing....

Private Sub Form_AfterUpdate()
For Each frm In Forms

    For Each itm In frm.Controls

        If itm.ControlType = acComboBox Then
        itm.Requery
        End If
    Next
Next


End Sub

it is worth a try, but it works as it is on my machine. If they are not declared they should just be of variant type anyway.

dan

> it is worth a try, but it works as it is on my machine.

That I can not explain except if frm and itm are declared globally.

> If they are not declared they should just be of variant type anyway.

No, they should not, but they could, though I can see no reason why; always be as specific as possible.

/gustav

Yes they are mate. And that code works without the global/local declarations on a multitude of machines I have worked with. If you want to get into a talent contest fine, I just want to help this person sort their problem out.  

> I just want to help this person sort their problem out.  

We all do, Dan, but it doesn't help John to claim that his code should work when it doesn't.
It will not compile without the declaration of the two variables, be it local or global. This is tested in both A97 and AXP. Why it works on your magic machines, I have no idea - John's machine seems to react the normal way.

As for the variable type, it is just considered good programming practice to use Variant only when needed (to hold, say, Null) or when you don't know or care about the type of value. Neither is the case here. We are all free to write sloppy code as if VBA was a scripting language with no type declaration but it is not, and I see no reason to encourage people seeking advice to do so.

/gustav

Perhaps Dan uses Option Explicit instead of Option Compare Database, that may explain some things.

Alan, that's about it: Dan _doesn't_ use Option Explicit.

Dan, insert in the top of your module declaration section:

Option Explicit

and you'll see the message when you try to compile.
Of course, remove it again if you don't like it. However, Option Explicit is highly recommended as it will catche any typing error of a variable you may do. If you don't like specific data types and prefer Variant all over, just declare:

Dim itm
Dim frm

I don't think many recommend this route but it is doable.

Thanks Alan. Mystery solved.

/gustav

Guys, thanks for your help.  I split up the points since everyone helped out on this.

You can see the answer that I accepted.  

Alan, thanks for your response, I wish I could have used it but for some reason it did not work and I am anxious to get this one closed.  Ultimetly though, that would have been the best way to go.

Thanks again everyone...

John