Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    1 To 1 User Interface (Access 2000)

    I am requesting any ideas/suggestions/advice/guidance on a user interface form dealing with a 1 to 1 relationship.

    Here's the situation - I have three tables: tblRecruitRacks (RackID, Position, CleaningStation); tblRacksAssigned (RackID, RecruitID); tblRecruit (RecruitID, etc...).

    tblRecruitRacks is 1-1 with tblRacksAssigned which is 1-1 with tblRecruits. (tblRacksAssigned is required because the data in tblRecruitRacks is permanent and has already been entered. The data in tblRecruit gets deleted at the end of training which would delete the data in tblRacksAssigned but would leave tblRecruitRacks intact.)

    I am trying to create an easy and efficient form that would allow the user to assign a rack ("rack" in Sailor speak is "bed") to a recruit. Only one recruit can be assigned to a rack. The rack numbers and recruit names are already in the database. Sometimes a user has to move a recruit from one rack to another. (For this I created a "fake" rack in tblRecruitRacks in order to temporarily move a recruit.) I would like to be able to assign racks/recruits by just clicking or double-clicking the existing data to tie the two together.

    I appreciate any guidance offered.

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

    Re: 1 To 1 User Interface (Access 2000)

    Despite what you write, I would put a RackID field in tblRecruit (or if you prefer, put a RecruitID field in tblRecruitRacks), and scrap tblRacksAssigned. You can create a simple update query that clears this field for all records (without clearing other fields). Put a unique index on the field, so that a value can't be used twice.

    You can use a combo box or list box to let the user select a value for the field.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: 1 To 1 User Interface (Access 2000)

    I'd strongly advise that you consider Hans' suggestion. It would certainly simplify things.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 1 To 1 User Interface (Access 2000)

    Once again you guys have shown why the Lounge is an outstanding resource for help and insight!

    I don't know why I was trying to make it more complicated than it needed to be but Hans suggestion works great. I have not yet had time to create the UI form yet but I'm sure it won't be half the trouble now that I've gotten the tables fixed.

    Thanks to both Hans and Mark for turning on the lights!

Posting Permissions

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