Search for exixting records

[rikklaney1]

I need some advice. I have a database that tracks visitors to our company. I started with a search box that will look up any info entered from the records of previous visitors and show any record that has that info. You can also just enter a NEW visitor. The problem is the search field is not being used. People are just entering a new visitor every time leading to many duplicates in the table of people. My question is what is the best way to do something like this. I would like to keep using the search but make it change as they are inputting info into the individual fields, last_name, first_name, Contact_Number, etc. Has anyone done something like this before and have any advice?

 

[theDBguy]

Hi. Yes. This is similar to a process of preventing duplicate records in a table. The first thing we need to know is how do you determine if a person is a previous visitor? Do you enter a unique information about them?

 

[rikklaney1]

I would like to use whatever info is entered. The requirements ar first and last name, gender, nationality, vehicle ID and a company name. It is possible to have the same name from different companies but unlikely to have all the same info. I want the person entering the data to at least have to confirm that this is a new person and not just a duplicate. From a personal standpoint I would have liked to require an id like a drivers license number but HR says we can't ask for that. We require it to get into the plant but are not allowed to record it.

 

[rikklaney1]

I have it working somewhat. Unfortunately I could still get duplicates with different spellings but at least I think this will limit the number of duplicates I get.

 

[theDBguy]

Hi. Glad to hear you have a working solution. Good luck with your project.

 

[rikklaney1]

I really liked my dynamic lookup. Wish I could keep it but... Everytime I try to prevent someone from doing something wrong they find a better way. lol

 

[theDBguy]

Hi. Not sure if this is what you're doing already; but basically, you can use your data entry form as a replacement for your dynamic lookup screen. In each AfterUpdate event of the key information textbox you want to check, you can execute your code to look up the table if the current visitor is a previous visitor as well. You can then either let the user know or let them continue.

 

[rikklaney1]

That's pretty much what I'm doing. The difference is when I was using a single search field I could change the listbox rowsource so that it, neatly IMHO, updated the list as they typed. if they typed Rikk it showed any records that had Rikk in any field and it updated as they typed the letters, after the R I saw anything with an R, then anything with an RI, then anythingwith an Rik etc. The list got narrowed down as they typed. I though it was slick. Using seperate fields I am having to use the after update to look for a match. Not as slick but it works. I'm sure it's possible to still do it from multiple fields but right now I can't get my head around that query. lol

 

[theDBguy]

Hi. I hear what you're saying. However, you can have the "best" application in the world; but if nobody uses it, then you might as well have the "worst" one as long as it does the job. In any case, if you want to insist on a "quasi lookup" feature, perhaps you could monitor the user's entry and pop up a list of potential duplicates where they could click a match, and it will bring up the previous record. Essentially, you could try to do everything you were doing before without requiring the user to perform an extra step - intentionally opening a search form first; instead, the data entry form doubles as a search form as well (without their consent).

 

[rikklaney1]

Thats the direction I'm heading. Thanks for the help all.

 

[theDBguy]

Thats the direction I'm heading. Thanks for the help all.

Sounds good. Best of luck!