Coding many to many relationship table

osemollie · June 15th, 2006, 02:12 AM

Hi guys,

I have three tables in MsAccess 2000:-
- Movie table (movieID - autonumber, title- text)
- cast table (castId -autonumber, actor - text, description - memo)
- movie-cast table (movieId, castId)

I have a form with controls (textboxes, and a combbox) for capturing the above information. The main challenge is how to code an INSERT statement that can associate one movie to more than 2 actors.

Eg Movie title = Coming to America
Actors 1 - EddieMurphy
2 - Arsenio Hall

Remember the INSERT code is getting it's values from the controls on the form.

Any form of sample code will be much appreciated.

BrianWren · June 15th, 2006, 03:58 PM

You would need to have one INSERT statement for each movie/actor combination.
If Coming to America were movie ID 10:

Code:

INSERT INTO MOVIE_CAST ( MOVIE_ID, CAST_ID ) VALUES ( 10, 1);
INSERT INTO MOVIE_CAST ( MOVIE_ID, CAST_ID ) VALUES ( 10, 2);

osemollie · June 16th, 2006, 02:27 AM

Hi BrianWren,

I agree with you about using individual INSERT statements, however, the challenge that I have is how to establish the second record in the cast table. I am using the SELECT @@Identity to generate the last MovieId entered in the movie table but the problem now lies with establishing the two entries from the cast table so that they correspond with this one movie. Remember, I am using a combobox to select/insert actors into the cast table. Is there a way of determining the sequence of selected items in a combobox? I believe that is the only way I can be able to use individual INSERT statements to enter these entries in the bridge table. Or is there another way out? Any suggestions will be most welcome.

BrianWren · June 16th, 2006, 03:11 PM

I hate to admit it, but I've looked and I've looked, and I cannot find anything that explains the meaning of the â@@â . . . How is that used, and what does it do?

I don't exactly follow your quandary. The entries in the CAST table are completely independent of anything in any other tables; It is just a list of actors (with possible ancillary data like age, religion, nationality, etc.). The entries in the MOVIES table are just a list of movies (with ancillary data like copyright date, running time, etc.). The MOVIE_CAST table has [u]only</u> 2 columns: one that holds an ID from the MOVIES table, and one that holds an ID from the CAST table.

I am not clear on your interface, but if the idea is that the user enters a movie into one control, and a list of actors into a second control, then the operation would be something like this:

    1. Find whether the movie exists in the MOVIE table.
       a) If so, get the ID
       b) If not, add it to that table then get the ID

    2. For each entry in the Actors control:
       a) Find whether the actor exists in the CAST table.
          i. If so, get the ID
          i. not, add it to that table then get the ID
       b) Find whether this specific combo of CAST_ID and MOVIE_ID already
          exists in the MOVIE_CAST table.
          i. If so, nothing to do; Get the next actor
          i. If not, build the INSERT statement, and issue it,
             then get the next actor

Or am I missing something in the setup?

osemollie · June 19th, 2006, 01:59 AM

BrianWren,

Thanks for having taken your time to help me out with this 'tricky' situation. Maybe I forgot to mention that am relatively new to VB and am learning on my own. Am using the web resources and friends like you to help me learn. Am determined to learn and this is my first application that am trying out.

Back to my dilema, you have analysed my problem very well and your suggestions make so much sense but since am still new to VB, am NOT sure how I will implement it. Will you mind writing a sample code that I can use to find whether the movie or actor exists in the MOVIE/CAST table.
a) If so, get the ID
b) If not, add it to that table then get the ID?

I know how to search a database but NOT how to get the search results to be used in another query/code.

Just for information, @@identity is a stored procedure that gives u the last inserted record in a table.

Thanks for your patience.

BrianWren · June 20th, 2006, 01:29 PM

You would do that by trying to open a recordset with the details that would point to the record that you are after, with the ID field as one of the fileds in the SELECT statment. If no records result, then you know the record does not exist. If there [u]is</u> a record, it will be the ID you are after.
So let's say weâre looking for Jennifer Aniston (whom I would very much like to find...).

Code:

Function CastID(ByVal FirstName As String, ByVal LastName As String) As Long

    Dim r As ADODB.Recordset
    r.open "SELECT ID                                   " & _
           "FROM   CAST                                 " & _
           "WHERE  LCase(FNAME) = """ & FirstName & """ " & _
           "  AND  LCase(LNAME) = """ & LastName & """  "

    If r.EOF And r.BOF Then
        CastID = -1
    Else
        CastID = r!ID
    End If

End Function

Then you would call this function:

Code:

    lngCastID = CastID("Jennifer", "Aniston")
    If lngCastID = -1 then
        RunSql "INSERT INTO CAST (        FNAME,       LNAME  ) " & _
               "VALUES           ( ""Jennifer"", ""Aniston"" ")
        ' Now the records exists.  Get the ID.
        lngCastID = CastID("Jennifer", "Aniston")
    End If

And so on. If you are using a connection (named, say "c"), you can substitute in place of the RunSql the following c.Execute "INSERT . . . "

In your case you would use the values of the input objects of your form in plkace of the hard-coded strings I've used, but the ideas in the above should get you up and running.

osemollie · June 28th, 2006, 10:42 AM

I am new to programming and I have been trying with no success. How can I substitute your code with the controls on my form?

I need this code to work desperately.

Thanks

osemollie · July 28th, 2006, 03:52 AM

Hi Brian,

Am still pursuing this thread. I have tried to get a solution using your code sample but seems not to work for me. Can you elaborate how it works?