Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add records to both sides of MtoM (A2K)

    I need to create a form combination that will allow me to add a record to each side of a many to many relationship. I need to be able to add a new consumer and a new organization and have the two linked via a join table that contains lngConsumerID and lngOrgID. Does anyone have a sample I can look at or tell me a good strategy?

    The organizations attached to the consumer also need to be readily visible to the user.

    E

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

    Re: add records to both sides of MtoM (A2K)

    You can search for many-to-many in this forum. For example, <post#=364203>post 364203</post#> has a simple sample database attached with two forms that display a many-to-many relationship from both sides.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add records to both sides of MtoM (A2K)

    Suggest you think about using an unbound data entry form. You can then add information about both the new consumer and organisation into variables on the unbound form and put some code under an Add button to save the 2 records and the linking record using a transaction to ensure that all the saves take place.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add records to both sides of MtoM (A2K)

    Can you tell me where I can learn more about using unbound data entry forms along with the code required. I think this would help me with my room data sheet problems.

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

    Re: add records to both sides of MtoM (A2K)

    I hope your problem can be solved by using subforms instead of linked forms.

    If you want to create an unbound data entry form, I would do it as follows:
    - Start by creating a bound form, the way you usually do. You can use a wizard, or create it manually, or a combination of both.
    - When it looks OK, clear the Control Source of all controls bound to a field, and clear the Record Source property of the form.
    - Put two command buttons on the form: cmdOK, with caption OK (or Save), and cmdCancel with caption Cancel (or Close).
    - In the On Click event procedure for cmdOK, open a recordset and create a new record; this can be done in DAO or ADO. If you want to add records to several tables, handle them separately, making sure to create the necessary primary keys first.

    For example using DAO (you need a reference to the Microsoft DAO 3.6 Object Library for this):

    Private Sub cmdOK_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler

    ' Perform a check
    If IsNull(Me.txtLastName) Then
    MsgBox "Please enter a last name.", vbExclamation
    Me.txtLastName.SetFocus
    Exit Sub
    End If

    ' Open recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblSomething", dbOpenDynaset)
    ' Add a new record
    With rst
    .AddNew
    !LastName = Me.txtLastName
    !FirstName = Me.txtFirstName
    !BirthDate = Me.txtBirthDate
    .Update
    End With

    ExitHandler:
    ' Clean up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    DoCmd.Close
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub


    - In the On Click event procedure for cmdCancel, just close the form:

    Private Sub cmdCancel_Click()
    DoCmd.Close
    End Sub

  6. #6
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add records to both sides of MtoM (A2K)

    Sorry for delay in responding. Been off the system due to upgrade faults. Hans has shown how to create an unbound form.

    Looking back at your original query you want to add a new consumer and organisation at the same time. You can certainly do this in code, but I now wonder what you achieve by it. You have a table tblConsumers and a table tblOrganisations (or whatever their names are). By definition a many-to-many join allows you join one consumer to many organisations AND one organisation to many consumers. Is this a good description of how your organisation works?

    Whilst you may want to add a new consumer and a new organisation at the same time there must be many more occasions where you want to add a new consumer to an existing organisation, and vice-versa. I think you would be better to add records for the two tables separately with two different add forms. Ask yourself the question do all consumers have to be linked to at least one organisation? or the other way round? That will determine which bit of info needs to be added first.

    I have a scenario where I record details of applicants for our properties. Although this is many-to-many, a property need not have any applicants but all applicants must be linked to at least one property. Properties are therefore added without any linking as there is no need (indeed we are unlikely to have applicants at the point we take the property on to our books). The applicant add form, however, collects data about a new applicant and has a list box of all current properties. This list box is multi select. On saving the code checks basic data and that the user has selected at least one property to link to the applicant. That's the beauty of unbound entry forms - you can error check to you're hearts content. The save routine then saves the new applicant details and reads the list box adding a record in the link table for each selected. The whole saving is done within a transaction to ensure that an applicant cannot be saved unless all the link entries are also successful.

    If the scenario I describe is what you are looking for, I'll post a stripped down form and code but it will not be until 8th July as I am out of the office.

Posting Permissions

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