Junction in a Form

[CaLintZ]

I have 3 tables

Table 1: tblMovie
MovID (PK)
MovName

Table 2: tblGenre (manually inputted; limited to existing data)
GenreID (PK)
GenreName

Table 3: junction (MovID has lookup on MovName from tblMovie; GenreID has a lookup on GenreName from tblGenre)
MovID
GenreID


I would like to have a form which shows the movie title and its genre;
e.g
Title: Fictional Movie
Genre: Action, Horror, Suspense, Drama

I've tried using text box, combo box, and list box. But at most i was only able to show a Single Genre Name with mutiple record instance of the same title.

Any help on how to solve this problem is greatly appreciated

tnx.

[ADezii]

I have 3 tables

Table 1: tblMovie
MovID (PK)
MovName

Table 2: tblGenre (manually inputted; limited to existing data)
GenreID (PK)
GenreName

Table 3: junction (MovID has lookup on MovName from tblMovie; GenreID has a lookup on GenreName from tblGenre)
MovID
GenreID


I would like to have a form which shows the movie title and its genre;
e.g
Title: Fictional Movie
Genre: Action, Horror, Suspense, Drama

I've tried using text box, combo box, and list box. But at most i was only able to show a Single Genre Name with mutiple record instance of the same title.

Any help on how to solve this problem is greatly appreciated

tnx.

If you defined your Relationships as follows, you wouldn't need any type of Lookup:

[tblMovie].[MovID](1) ==> [Junction].[MovID](MANY)
[tblGenre].[GenreID](1) ==> [Junction].[GenreID](MABY)

You could then just simply create a Form/Sub-Form scenario with the Composite Key from the Junction Table as the Link.

[CaLintZ]

If you defined your Relationships as follows, you wouldn't need any type of Lookup:

[tblMovie].[MovID](1) ==> [Junction].[MovID](MANY)
[tblGenre].[GenreID](1) ==> [Junction].[GenreID](MABY)

You could then just simply create a Form/Sub-Form scenario with the Composite Key from the Junction Table as the Link.

Thanks for the Info. Actually the sole purpose of the lookup was so that Genre and Title wouldnt show up as only numbers.

Anyways tried it as you said. but still not working. To be more elaborate. my problem is that my records on form shows up like this;

Record 1:
Title: Fictional Movie 1
Genre: Action
Record 2:
Title: Fictional Movie 1
Genre: Comedy
Record 3:
Title: Fictional Movie 2
Genre: Romance
Total of 3 records

Instead of like this:
Record 1:
Title:Fictonal Movie 1
Genre: Action, Comedy
Record 2:
Title: Fictional Movie 2
Genre: Romace

Total of 2 records

I'd like to have the Genre in a single textbox if possible, but a listbox is acceptable too. Thinking of making a loop which looks up the junction table, but have no idea no how to do it. Can anyone give a hint on how to start.

Sorry for the long post.

[ADezii]

Thanks for the Info. Actually the sole purpose of the lookup was so that Genre and Title wouldnt show up as only numbers.

Anyways tried it as you said. but still not working. To be more elaborate. my problem is that my records on form shows up like this;

Record 1:
Title: Fictional Movie 1
Genre: Action
Record 2:
Title: Fictional Movie 1
Genre: Comedy
Record 3:
Title: Fictional Movie 2
Genre: Romance
Total of 3 records

Instead of like this:
Record 1:
Title:Fictonal Movie 1
Genre: Action, Comedy
Record 2:
Title: Fictional Movie 2
Genre: Romace

Total of 2 records

I'd like to have the Genre in a single textbox if possible, but a listbox is acceptable too. Thinking of making a loop which looks up the junction table, but have no idea no how to do it. Can anyone give a hint on how to start.

Sorry for the long post.

1. Create a Form based solely on tblMovie consisting of the [MovID] and [MovName] Fields.
2. Create a Query named qryMovieGenreJunction consisting of all 3 Tables joined as previously indicated.
3. Assuming that a Text Box (Unbound) on frmMovies is called txtGenre, the followinig code in the Current() Event of the Form will produce the desired results.
   

   Private Sub Form_Current()
   Dim MyDB As DAO.Database, MyRS As DAO.Recordset
   Dim MySQL As String, strGenre As String
    
   MySQL = "Select * From qryMovieGenreJunction Where [MovID]=" & Me![MovID]
    
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
    
   Do Until MyRS.EOF
     strGenre = strGenre & MyRS![GenreName] & ","
     MyRS.MoveNext
   Loop
    
   strGenre = Left$(strGenre, Len(strGenre) - 1)
    
   Me![txtGenre] = strGenre
    
   MyRS.Close
   End Sub

Sample OUTPUT for txtGenre Field:

Action,Comedy,Romance
Unknown,Weird

[CaLintZ]

1. Create a Form based solely on tblMovie consisting of the [MovID] and [MovName] Fields.
2. Create a Query named qryMovieGenreJunction consisting of all 3 Tables joined as previously indicated.
3. Assuming that a Text Box (Unbound) on frmMovies is called txtGenre, the followinig code in the Current() Event of the Form will produce the desired results.
   

   Private Sub Form_Current()
   Dim MyDB As DAO.Database, MyRS As DAO.Recordset
   Dim MySQL As String, strGenre As String
    
   MySQL = "Select * From qryMovieGenreJunction Where [MovID]=" & Me![MovID]
    
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
    
   Do Until MyRS.EOF
     strGenre = strGenre & MyRS![GenreName] & ","
     MyRS.MoveNext
   Loop
    
   strGenre = Left$(strGenre, Len(strGenre) - 1)
    
   Me![txtGenre] = strGenre
    
   MyRS.Close
   End Sub

Sample OUTPUT for txtGenre Field:

Action,Comedy,Romance
Unknown,Weird

I'm getting a lot of errors... might just be my setting though. Gonna check it first. btw can I know what references you had checked when you did this code. I seem to get "cannot find project or library" on the dbopendynaset line even though i have DAO 3.6

[ADezii]

I'm getting a lot of errors... might just be my setting though. Gonna check it first. btw can I know what references you had checked when you did this code. I seem to get "cannot find project or library" on the dbopendynaset line even though i have DAO 3.6

In your specifric caes, you'll need a Reference set to the Microsof DAO 3.6 Object library.