Newbie question: How to optimize search over network in this case...

  • Thread starter Thread starter Hulo
  • Start date Start date
H

Hulo

1. I have a table named "Data Table" and a form named "Data Entry
Form", through which data is entered into the said table.

2. The "Data Table" has around 31 fields and presently around 80000
records with around 1500 records being added each month.

3. Two of the fields in the "Data Table" are named "LC_NO" and
"PARTY_NM".

4. In some of the records, value of "LC_NO" and corresponding
"PARTY_NM" are the same while the remaining fields may vary.

5. Presently "LC_NO" and "PARTY_NM" are being entered manually,
without checking whether they have been previously entered.


My objective is that after an "LC_NO" is entered, all of the
previously entered data will be searched (presently 80000 and
increasing) for the presence of that particular "LC_NO" in the table.
If it exists, the corresponding "PARTY_NM" from the table will be
automatically inserted into the corresponding text box in the form. If
it does not exist, the cursor will move to the text box and wait for
input.

What is the best and most optimized process to implement this so that
a minimum amount of network traffic is generated? The backend mdb
containing table "Data Table" is in one particular computer of the
network while the connected frontend forms are in 4 workstations with
connection speeds of 100 MBps among them.
 
The very most important thing you can do to speed this process is to make
sure there's an index on the LC_NO field in your table. An index on the
PARTY_NM field may help, too. Both indexes should specify "Duplicates OK".

Once that's in place, you can put code like this in the After_Update event
of the LC_NO textbox on your form:
Dim P_NM as variant
P_NM=DLookup("PARTY_NM","[Data Table]", "LC_NO=" & Me!LC_NO)
If IsNull(P_NM)=True Then
PARTY_NM.SetFocus
Else
PARTY_NM=P_NM
NextControl.SetFocus
End If

Coupla caveats:
Instead of NextControl, use the name of the control where you want focus
to go if PARTY_NM gets filled in automatically.
If LC_NO is a text field, rather than a numeric field, you'll need this
syntax:
P_NM=DLookup("PARTY_NM","[Data Table]", "LC_NO=" & chr(34) &
Me!LC_NO & chr(34))
This syntax will return the first occurrence of LC_NO in your table. If
you need to consider other occurrences, we'll have to change this.
You did ask for the best and most optimized way to do this. There are
more complex ways of achieving this, which could be more efficient in actual
time used. However, as long as your table is correctly indexed, I doubt
very much that your data entry person would notice the difference.

HTH
- Turtle
 
Back
Top