Performance Question

[Guest]

I have a complex form with close to 100 controls on it. Most are populated
based on a combo box.

Performance wise, what is the best method to populate the data? DLookup
statements, hidden subform, ???

Thanks, in advance.

Sharkbyte

 

[Douglas J Steele]

What do you mean by "populated based on a combo box"?

Are you saying that when you select something in combo box A, it may cause
data to be populated in several other (unbound)controls? The usual way would
be to include those extra bits of data in the row source of the combo box,
and put logic in the AfterUpdate event to populate the other controls. The
"extra" data doesn't have to be visible in the combo box for this approach
to work.

 

[Guest]

Okay. I'll admit to not knowing how to do this...

While I understand about adding the needed fields to the row source, will
this cause a performance issue with the form opening the drop-down?

And could you give an example of the code to populate the Row Source data,
of the ComboBox, into another control?

Thanks.

Sharkbyte

 

[John Vinson]

I have a complex form with close to 100 controls on it. Most are populated
based on a combo box.

Performance wise, what is the best method to populate the data? DLookup
statements, hidden subform, ???

At a guess, the most efficient way would be to use a form bound to a
query referencing the (unbound) combo box, and requery the Form.

Dlookup would be a dead-last choice.

What are you actually doing with the data in the form? Surely not
updating a 100-field table!?

John W. Vinson[MVP]

 

[Douglas J Steele]

Unless you're talking thousands of rows in the combo box, with dozens of
fields, you should have any performance issues to worry about.

If you wanted to take the contents of the third column of the selected row
and put it in text box Text1, and put the contents of the fourth column of
the selected row into text box Text2, you'd use code like:

Private Sub MyComboBox_AfterUpdate()

Me.Text1 = Me.MyComboBox.Column(2)
Me.Text2 = Me.MyComboBox.Column(3)

End Sub

Note that the Column collection starts counting at 0, not 1.

 

[Guest]

What are you actually doing with the data in the form? Surely not

updating a 100-field table!?

LoL. Absolutely not!

It's a conglomeration of 9 tables, plus some 'code' tables, making up the
main work order screen for a customized CMMS package.

Thanks for your suggestion.

Sharkbyte