requery all controls on a form? 1

[KellyK]

I am attempting to requery all the controls on my form, without having to explicitly name all 193 of them. I have tried the following code:

Private Sub cboRegion_AfterUpdate()

For a = 0 To (Forms!frmMain.Controls.Count - 1)
   Forms!frmMain.Controls(a).Requery
Next

End Sub

but get the error "Object doesn't support this property or method". The text boxes on my form are sourced by DLookups based on the value in the cboRegion. Any ideas? Thanks in advance.

Kelly

 

[Pampers]

Maybe Me.requery?

Pampers
Keeping it simple can be complicated

 

[Remou]

Not all controls can be requeried, labels for example. Also consider:
Me.Requery
Me.Recalc
Me.Refresh

Usually if you change a value that is refered to in DlookUp, the look up will automatically update.

 

[MajP]

Agree with all above, but if there is a reason to query the controls and not the form (can not think of one) then

Dim myControl As Access.Control
For Each myControl In Me.Controls
Select Case myControl.ControlType
Case acTextBox, acListBox, acComboBox
myControl.Requery
End Select
Next myControl

These are the standard controls that get requeried.

 

[Remou]

Why would you requery a textbox?

 

[KellyK]

Maybe I didn't explain myself very well. I want to requery ALL the controls on a form. I'm not sure how Me.Requery would achieve that. They are text boxes with various different queries as control sources that use the value of cboRegion as a filter. I do not have a single query/record source bound to the entire form.

Kelly

 

[Remou]

Please post an example of a textbox control source.

 

[KellyK]

The combo box I'm updating (cboRegion) is the input to several of the queries that populate the text boxes, through DLookups. I would need to rerun the queries with the new value of cboRegion to get the set of values from which to look up. I do not want to run these queries without the filter of cboRegion because I am going up against gigantic data warehouse tables with millions of records, so I would rather pull only those in the region I am interested in each time the user changes the combo box.

Is this poor design? I see no other way to achieve this than to requery the text boxes... forgive me, I am learning as I go.
P.S. MajP, you're great. Worked like a charm.

Kelly

 

[KellyK]

Remou,

=DMin("Loads","qselCapacity")

where the query "qselCapacity" uses the region in cboRegion as criteria ([Forms]![frmMain]![cboRegion]) for one of the fields in the query.

Kelly

 

[Remou]

As I mentioned, dlookup normally updates automatically. I am surprised it is not updating for you, so I enquired about the control source of a textbox that is not updating. It would not be usual to requery a textbox, recalc perhaps, but requery seems a little odd.

 

[Pampers]

Just a question, KellyK. You mentioned: "They are text boxes with various different queries as control sources". Does this mean, that these placed are various subforms...?

Pampers
Keeping it simple can be complicated

 

[KellyK]

Pampers, subforms probably would have made more sense, but no. I just have one mondo form with tons of text boxes on it. There are something like 4 or 5 queries which feed these text boxes. In hindsight, maybe I should have grouped the text boxes by query and made subforms out of them?

Kelly

 

[Pampers]

Hi KellyK,
Yes, that might have been a good idea (but you could still do that, could you not? (the query's are already there). And 193 controls on form, wow, that is a lot. Maybe to get rid of a couple you could do somehting like double click a control, which will then show some details.... Maybe use tab-controls, and group textboxes by subject.

Pampers
Keeping it simple can be complicated

 

[KellyK]

Yes, I definitely could still do the subforms and am considering it now, thanks to your suggestion. As far as the double-clicking controls to bring up more details, that's the ultimate goal but for now the business wants to see ALLLLLLLLL the details in one fell swoop. I think I'm over 200 controls now! I will look into tab-controls, I'm not familiar with them. Thanks for your help!

Kelly