Whats more efficient

  • Thread starter Thread starter Chris Strug
  • Start date Start date
C

Chris Strug

Hi,

I have a table of library addresses, PK is a seven digit alpha. User selects
address code from a combo and the full address is filled in the textbox
below the combo.

In the primary table, all the address is stored (Address_1, Address_2, etc)
not the library address code as the user often changes the addresses or
enters a new one altogether - the combo is purely a time saver and not part
of any relationships.

I'm curious as to the best approach to retrieve the address information from
the table and populate the form with it.

I see two approaches:
1. The DataSource for the combo is a statement which selects all the
relevant rows from the library address table with the code in column zero
with the rest of the address info held in the following hidden columns. In
the AfterUpdate event of the combo, each line of the address is populated in
the text box below it through code. I.e.

Me.txtAddress = me.cboAddCode.column(1) & vbcrlf & me.cboAddCode.Column(2) &
.... etc

OR....
2. This time the combo only holds the short codes from the library address
table. In the AfterUpdate Event of the combo each row of the address is
found through the use of the DLookup function.

Which of these is the best - in that its most efficient and places less
strain on the client and doesn't overtly hammer the server....

My design is an unbound ADP form working with SQLS 2000...

Any and all advice is gratefully received.

Kind thanks

Chris.
 
I'm not sure there's a single "best answer", as it depends on how many rows
are being returned to the combo box.

If it's a reasonable number (a few hundred, say), then bringing the
addresses along is probably better, as it saves additional trips back to the
server.

Too many rows, though, and the volume of data you're bringing across becomes
too much.
 
Douglas J. Steele said:
I'm not sure there's a single "best answer", as it depends on how many rows
are being returned to the combo box.

If it's a reasonable number (a few hundred, say), then bringing the
addresses along is probably better, as it saves additional trips back to the
server.

Too many rows, though, and the volume of data you're bringing across becomes
too much.
Click to expand...

Doug,

Many thanks for the reply. I kind of imagined that that would be the case.
I'm just curious as to when the trade off occurs. In my example, I'm looking
at perhaps a 1000 records (tops) in the source for the combo...

I guess I'll stick with bringing the data accross in the recordsource for
the combo and see how it performs, plus it makes my code a lot tidier....

Thanks for the reply

Chris
 
"Douglas J. Steele" wrote
Chris said:
Many thanks for the reply. I kind of imagined that that would be the case.
I'm just curious as to when the trade off occurs. In my example, I'm looking
at perhaps a 1000 records (tops) in the source for the combo...

I guess I'll stick with bringing the data accross in the recordsource for
the combo and see how it performs, plus it makes my code a lot tidier....
Click to expand...


I agree with Doug.

However, in your case where you only want a single string
for the final result, using the txtAdderss text box
expression;

=DLookup("address1 & (Chr(13) + Chr(10) + address2) & ...",
"addresstable", "PK = '" & combo & "'")

might(?) actually be "cleaner".
 
Back
Top