Record Duplication

Could anyone give me a hand as to how to write the proper VBScript for a database check
before the data is inserted?

It's for my MS Access movie database. The relevant info including tables are below.


Actors Table
Table Name: tblActors
Table Fields: fldActorID, fldAFname, fldALname


Movies Table
Table Name: tblMovies
Table Fields: fldMovieID, fldMovieName


Actor/Movie Join Table
Table Name: tblActorMovie
Table Fields: fldActorMovieID, fldActorID, fldMovieID


After I fill in the info for the new movie being inserted and click the Submit button I would like

to check;

If the Actor's first AND last name already exists (Eg. AFname = Harrison / ALname = Ford) Then

Use the Actor's ID number (Eg. ActorID = 2) and insert it into the Actor/Movie Join Table
and join it with the new MovieID

Else

Use the original method for inserting movies and actors (which works like a charm)

EndIf

I need to eliminate duplicated names in the Actors Table.
(Eg. ActorID=2 / AFname = Harrison / ALname = Ford AND ActorID=59 / AFname = Harrison / ALname = Ford)

Can anyone help me with this???

Here is what I would do.

I would enter you movie, like you do, then I would select the actor from a combo box, IF the actor doesn't exisit in your combo box (which you populated from your actor list), then have an add actor button which the user will click on to enter a new actor. when the user is done entering the new actor, you will be brough back to your pervious form, with the actor info, now seleted in the combo box.

The reason I wouldn't go searching for an exisiting record (based on what you already have in the tables) is because of spelling mistakes. Lets say you type Harison Ford instead of Harrison Ford, and you don't realize you typed the name wrong. You have now just added another Harrison Ford to your actor table (which isn't a duplicate based on spelling). Having the user select an exisitng name (record) remove that problem.

S-

I could do that, OR while I'm inserting the record, it can check for duplicate records with the %LIKE% attribute, that way if there IS a duplicate and I made a spelling mistake, a page can ask me: Did you mean - Harrison Ford and I can either click yes add to Harrison Ford's Movie connection via ActorID OR I can say no, add the new actor named Harison Fordd.

It's funny, I know what I want to do, but I just don't know HOW to do it.

Can anyone else give a helpful hand like sbaxter here? Thanks again sbaxter!

Problem with


%LIKE%

IS

I have Harrison in my system

I type
Harrisson

%Like% will not find Harrison


What you need to use in this case is Soundex You need to look for the name based on the sound and not spelling (won't work in all cases, but will get you close.

Look it up in help and internet for code samples.

S-