By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,573 Members | 904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,573 IT Pros & Developers. It's quick & easy.

Junction in a Form

P: 3
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.
Jun 3 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,632
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:
Expand|Select|Wrap|Line Numbers
  1. [tblMovie].[MovID](1) ==> [Junction].[MovID](MANY)
  2. [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.
Jun 3 '07 #2

P: 3
If you defined your Relationships as follows, you wouldn't need any type of Lookup:
Expand|Select|Wrap|Line Numbers
  1. [tblMovie].[MovID](1) ==> [Junction].[MovID](MANY)
  2. [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.
Jun 3 '07 #3

ADezii
Expert 5K+
P: 8,632
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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
    3. Dim MySQL As String, strGenre As String
    4.  
    5. MySQL = "Select * From qryMovieGenreJunction Where [MovID]=" & Me![MovID]
    6.  
    7. Set MyDB = CurrentDb()
    8. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
    9.  
    10. Do Until MyRS.EOF
    11.   strGenre = strGenre & MyRS![GenreName] & ","
    12.   MyRS.MoveNext
    13. Loop
    14.  
    15. strGenre = Left$(strGenre, Len(strGenre) - 1)
    16.  
    17. Me![txtGenre] = strGenre
    18.  
    19. MyRS.Close
    20. End Sub
Sample OUTPUT for txtGenre Field:
Expand|Select|Wrap|Line Numbers
  1. Action,Comedy,Romance
  2. Unknown,Weird
Jun 3 '07 #4

P: 3
  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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Current()
    2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
    3. Dim MySQL As String, strGenre As String
    4.  
    5. MySQL = "Select * From qryMovieGenreJunction Where [MovID]=" & Me![MovID]
    6.  
    7. Set MyDB = CurrentDb()
    8. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
    9.  
    10. Do Until MyRS.EOF
    11.   strGenre = strGenre & MyRS![GenreName] & ","
    12.   MyRS.MoveNext
    13. Loop
    14.  
    15. strGenre = Left$(strGenre, Len(strGenre) - 1)
    16.  
    17. Me![txtGenre] = strGenre
    18.  
    19. MyRS.Close
    20. End Sub
Sample OUTPUT for txtGenre Field:
Expand|Select|Wrap|Line Numbers
  1. Action,Comedy,Romance
  2. 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
Jun 4 '07 #5

ADezii
Expert 5K+
P: 8,632
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.
Jun 4 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.