Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2013

    Unanswered: Passing values between tables - no unique index found

    I have attached a db I could use some help with.

    There are three main tables: POINTID, STATION, and COUNT.

    What I would like to happen is that while in the MAIN form and after the user selects a Point ID from the dropdown list and initializes a record in the COUNT form, the X and Y values are passed from the corresponding record in the POINTID table to the same fields in the COUNT form/table. I need this to occur so that when the user then enters a distance and bearing in the COUNT form, it can take the X and Y coordinates and run the VBA code that recalculates the position.

    I've tried to create relationships between these tables with the idea that if the coordinates changed in the POINTID field then the values will update throughout all related tables. If that's not possible b/c the VBA code only works when its called upon in the form, I'd still like to have the X and Y values passed from the POINTID table initially so that the user doesn't have to copy and paste them for each record, which will number in the thousands+.

    The way I have it set up now is that when I select a Point ID and initiate a record in the COUNT form, it does pass the POINTID and SURVEYID values to the count table automatically. I suspect that it can do this b/c of the Link Master and Child fields are set to the primary keys and the relationships I established. I'm hoping this same thing can happen with the X and Y fields in either a similar way or alternative that isn't too complicated and can be explained via this forum.

    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2013
    Please see attached image that illustrates the relationship I'm trying to create. I would like the X and Y values passed between tables when linked in a subform.

    Are there limits to the number (i.e., 3) of link fields between the main form and subform?
    Attached Thumbnails Attached Thumbnails DBrelation.png  

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    in order to force referential integrity you must have a one to zero/one/many relationship. the one to must use its priamry key, the zero/one/many side should have the same columns (datatypes) as the one to table

    your problem is that the primary key of pointID is objectID, but you are trying to enforce relational integrity using pointid, X & Y
    the solution is either:-
    use an autonumber in both tables
    or define (column) pointid, x & y as the composite primary key in pointid

    but do you need pointid (and objectid in table pointid, what do they represent?

    X & Y are good candidates for a composite primary key assuming that you can only have one row for a specific x:y pairing. however bear in mind 3.01:5.62 is not the same as 3.0099999:5.62 so it comes down to your data if that is relevant.
    people may enter data with different precision for the same X:Y coordinate (ferinstance if they are using a GPS one with 6dp, one with 4dp)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2013
    Thanks for your reply healdem; your suggestion worked and allowed me to create a relationship with those fields.

    I ended up altering code from the thread below to automatically populate the X and Y fields between tables.

Posting Permissions

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