Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56

    Red face Unanswered: Many-to-Many Problems

    Hey guys,

    I have a main form “Projects” and a subform “People”.

    The database is set up on a series of many-to-many relationships (with linking tables) so that in this case many people can go on many projects. However, I don’t want the user to be able to add a person to the same project twice.

    Any ideas?

    Rusty

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    In the Projects table have a primary key consisting of two fields:

    1) ProjectID
    2) Person
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56
    I don't think that will work...I've attached a sample of the database to illustrate the problem (Access 2000).

    On the form you can enter "Obi-Wan Kenobi" as a person on a project more than once. I want to prevent this, but still enable Obi-Wan to be entered on different projects. If you look at the table relationships this problem may become a little clearer.

    Tally Ho!

    Rusty
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Oh so you don't want to have the ability to have a team on 5 Obi-Wans...You need to have some kind of IF statement that checks the previous people on this team. Sorry I have no idea how to do that

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I would suggest

    1)

    Use a DCount() function in the Before Update event which can be cancelled if necessary ie

    If DlCount( “ProjectField”, “Projectable”, ”[ProiectField] = ‘” & [ProjectNamel & “’””[Name] = ‘” & [NameCntrol & “’”) <>”” Then
    Cancel = True
    Msgbox “Person exists in prject etc..”
    End if

    Or similar
    Not you don’t need the single apostrophe if the field types is a number.


    OR
    2)

    The better (correct?) way, I would suggest, is to create a link table of “ProjectPersonnel” with two fields (ProjectID and PersonID) with the combination defined as the key field(s). This is the normal way of dealing with many to many (isn’t it?). Of course the Project and Person table would have to have the relevant field as there primary key (or at least set to field Indexed (No Duplicates) AND define the relationships.
    You can still use DCount() as above if you done want the standard Access error message on attempted duplicate entries.

    HTH

    MTB

  6. #6
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56
    Quote Originally Posted by MikeTheBike
    The better (correct?) way, I would suggest, is to create a link table of “ProjectPersonnel” with two fields (ProjectID and PersonID) with the combination defined as the key field(s). This is the normal way of dealing with many to many (isn’t it?). Of course the Project and Person table would have to have the relevant field as there primary key (or at least set to field Indexed (No Duplicates) AND define the relationships.

    You can still use DCount() as above if you done want the standard Access error message on attempted duplicate entries.
    Thanks for the help. I went with option 2.5 and created a new field in the existing link table that is a combination of ProjectID and PersonID and indexed it as unique. This field is invisible on the subform and is automatically filled after a new person is entered onto the project.

    You mentioned the DCount as a way of suppressing that pesky default access error message if a duplicate value is entered. I have now idea of the full syntax, and keep getting error messages myself (they're everywhere!!)

    Is it something like:

    Private Sub personid_BeforeUpdate(Cancel As Integer)
    If DCount ([ProjectPersonID] <> [ProjectPersonID]) Then
    Cancel = True
    MsgBox "That person is already on this project..."
    End If
    End Sub

    Any help on this final piece of the puzzle would be greatly appreciated. Many thanks,

    Rusty

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    number = dcount("nameOfaFieldInATable", "nameOfTheTableHavingThatField", "criteria")
    you can use literal strings in "" or string-variables.

    the criteria bit is the same as the WHERE stuff in SQL but without "WHERE"

    e.g. dcount("myField", "myTable", "SomeNumberField = 999")
    instead of the usual SQL
    "WHERE SomeNumberField = 999"

    concatenate in 'text' and #date# criteria just as you do in SQL

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Jun 2004
    Posts
    46

    May help or maybe not

    hi rusty... well im doing a similar job like urs... and i've created a database with some of the criteria that u mention.... i did it with link tables and it worked just fine... here is a sample of my database that im working on... see if it works for you (i said that maybe it wont work 4 u) cuz as of my interpretation of what u r saying but still i wanted to post to ur problem .... ooh and any comments or suggestions on my database would be appreciated....
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2004
    Location
    United Kingdom
    Posts
    56
    Izy,

    Thanks for the help, I cracked it using the code below in the AfterUpdate event:

    Code:
    If Err.Number = 0 Then
        DoCmd.RunCommand acCmdUndo
    MsgBox "This person is already working on this project", vbExclamation
    End If
    To find the error number of the default Access message just put in "msgbox Err.Number" in the AfterUpdate event before adding the above code. In this case the number is 0.

    Cheers,

    Rusty

  10. #10
    Join Date
    Dec 2003
    Posts
    268

    Exit Error

    You may have a problem with the error trapping if it is not set up correctly.

    If there is an error the procedure needs a way to exit

    private sub test()
    on err goto ErrHandler
    do something here
    Exit_Here
    exit sub
    errHandler:
    msgbox "Err Number:" & err.number & vbcrls & "Error Description:"& err.descrption
    resume exit_here
    end sub

    I too was getting tons of errors with an error of 0 becuase I didn't have the second exit out of the routine

Posting Permissions

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