Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a Junction table (Access 2003)

    Mainform
    ID2 primary Key
    etc

    Junction Table
    ID2 - Number
    AthleteID - Number

    Athlete Names
    AthleteID - Primary Key
    etc

    The mainform ID2 and the Athlete Names AthleteID are linked to the Junction table.

    Problem, I can add a name in the Athlete Names form using the navigation buttons, but the AthleteID value of the Athlete Names form does not get in the Junction table?

    The setup is a mainform, a subform on it for the Junction Table, and a subform on that form for the Athlete Names table.

    Any pointers where I'm going wrong. Many thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a Junction table (Access 2003)

    I wouldn't use an athletes sub-subform. You need a separate form based on the athletes table to add and edit athletes.

    See <post#=364,203>post 364,203</post#> for an example of how to handle data entry for a many-to-many relationship with a main form and subform.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a Junction table (Access 2003)

    Many thanks Hans. Looking at your attachment is a tremendous help, I can see the light at the end of the tunnel. Very much appreciated, have a good weekend.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a Junction table (Access 2003)

    Thanks Hans, the attachment did help a lot, however I am still trying to do things without seperate forms. I played around with the combo using a notinlist mode, I got it to work however it only updated the current record. Subforms seem to be the only way to get things on one mainform. I put a subform on which was bound to my junction table, and then had a listbox with a query that filled with the names that were entered already per record. I then put another subform on that subform, and by using the key in the listbox allowed editing the name. However that subform will not allow me to add a name. It aquires a new record number after typing into the first field, but comes up with a dialoge box saying I cannot do this as the form maybe read only, I think its possibly due to the fact the record number is not getting to the junction table or something.
    Have you seen any other sources of having a mainform with a subform on to allow editing names or adding them.
    Hope I have not confused you, trouble is the deeper I go the more difficult to explain what I have. Regards

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a Junction table (Access 2003)

    I have moved on a bit, the code has not been tidied up, but it seems to work. I modified the combo's not in list event code, and now put in the key values into the Junction table and it seems happy. Regards

    Private Sub AthleteID_NotInList(NewData As String, response As Integer)

    Dim db As DAO.Database, rst As DAO.Recordset, rst2 As DAO.Recordset, SQL As String, SQL2 As String
    Dim ctl As Control

    Set ctl = Me.AthleteID
    response = False 'acDataErrContinue
    If MsgBox("The Athlete Name " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then
    Set db = CurrentDb()

    SQL = "Select * From AthleteNames"
    Set rst = db.OpenRecordset(SQL, dbOpenDynaset)

    SQL2 = "Select * From JUNCTION"
    Set rst2 = db.OpenRecordset(SQL2, dbOpenDynaset)

    rst.AddNew
    NewData = UCase(NewData)
    rst![Athlete] = NewData
    rst![Country] = "Mars"
    rst.Update
    Set rst = Nothing
    response = acDataErrAdded

    rst2.AddNew
    rst2![ID2] = Forms!Courses!sbfStudents.Form.ID2
    rst2![AthleteID] = Forms!Courses!sbfStudents.Form.AthleteID
    rst2.Update
    Set rst2 = Nothing
    response = acDataErrAdded

    DoEvents


    Else

    response = acDataErrContinue
    ctl.Undo
    Me.Undo
    Me.List11.SetFocus
    End If

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •