best way to retreive data from table to form

[scrapcode]

Hi guys,

I have a form that is checking staff names against staff numbers and I'm looking for the best way to return this data.

I have up to 20 text boxes where staff numbers are entered, and 20 text boxes next to those where I would like the staff names filled it.

I originally had some great advise on a Dlookup, which worked fine for one search at a time but looking for multiple staff numbers against a list of 1300 takes a very long time. I have a feeling from what I've read that using a query would work faster. I've never tried to integrate queries and forms so I'm feeling out of my depth.

My ideal solution would be to have the seperate text boxes, the user fills in as many as required and then a "search" Cmd button checks the staff number against the table and brings back the person's name.

Thanks very much in advance for any help you can provide.

If I can provide any more information I will.

[dsatino]

For starters, 1300 records is nothing for a database engine so dlookup should be fine, even for 20 fields. If it seems slow, then you probably lack an index on the table that has the employee numbers.

[scrapcode]

Thanks for your reply.

What do I need to do to get round this then?

Thanks again.

[Mihail]

I suspect a poor design of your database.
Why 20 text boxes with the same type of data ?
Have you 20 tables (or 20 fields) on the same table (one for each staff number) ?
The same question for the staff names.

[NeoPa]

Your understanding seems about right to me ScrapCode.

I would consider updating the values in the staff name TextBoxes in the AfterUpdate event procedure of the various staff number TextBoxes. The code to do that should be as common as possible of course. have a separate procedure which takes the staff number as a parameter and returns the staff name which is called from within all the different AfterUpdate event procedures.

[scrapcode]

Thanks NeoPa. That was the way I first approached it. However the user will be manually entering a up to 20 staff numbers. The idea behind bringing the name back is to give the user a visual check that they have not made a mistake with the number. If I use the after update then won't the last box not be checked until after the user has hit submit?

Even using the Dlookup on one search at a time does take around 10 seconds. The table it's looking through doesn't have a primary key, I assume that this is the indexing problem I was advised of. I'll go and see if changing that will speed up the search.

[NeoPa]

ScrapCode:
If I use the after update then won't the last box not be checked until after the user has hit submit?

I think you may be confusing the control's _AfterUpdate() procedure with the form's Form_AfterUpdate() one. No. That shouldn't be a problem.

ScrapCode:
The table it's looking through doesn't have a primary key, I assume that this is the indexing problem I was advised of.

Absolutely. A small (generally integral) PK is required as well as, if they are on different fields, an index for the field you are using to select by. The size of the PK data effects the speed of all other index lookups too.