Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2011
    Posts
    9

    Unanswered: Linking issues with subforms

    Let me see if I can explain this accurately:

    I am creating a database to track media inventory - books, CDs, the like. I am creating one basic form to enter the broadest information for each type (books, movies, etc) - author, title, etc - through a series of subforms on it. i.e. There is a subform to enter title information that will go to tblTitles, and a subform to enter author names that goes to tblAuthors, etc etc. With appropriate variations (author/stars etc).

    Here is my question: is there a way to set up my subforms so that if someone inputs a new value in a field, the subform will create new records in the appropriately linked table, but if a value is entered that is already in the linked table it will simply associate them?

    Example: one of the fields on the entry (sub)form for books is "genre." Since the tables are normalized, the text box on the subform is actually linked to a field in tblGenre. If a genre that is already in the db is entered, it would simply link to the pre-existing entry in tblGenre (technically link to the ID field in said table), but if a new genre is entered, it would create a new entry.

    Am I making sense? Is this possible?

    Thanks.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sure. The most common method is to use a combo box to list the values from the other table. You can use the Not in List event of the combo to add new values. Searching for "notinlist" should turn up a couple of methods.
    Paul

  3. #3
    Join Date
    Mar 2011
    Posts
    9
    Fantastic! This should work out great, thanks!

    (If it doesn't I'll be back.)

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, post back if you get stuck. Welcome to the site by the way!
    Paul

  5. #5
    Join Date
    Mar 2011
    Posts
    9
    Thanks for the help, Paul, but I seem to have run up against a wall here, and I'm not sure what I'm doing wrong.

    Like I said, I'm working on a system to keep track of various media, specifically who has which media (i.e. who has what book/movie/etc). I'm pretty sure I set up all my tables correctly, normalized, etc, but I'm having trouble creating forms that will allow users to actually assign items to people.

    I have a table, tblStaff (this is for a company library), and then various assorted tables for items in the library, tblBooks, tblSoftware, etc etc. tblStaff has foreign key fields for all the items people can have - a field, say, named "Books" that is one-to-many linked to the ID field in tblBooks, which is an autonumber primary key. (And so on for other items.)

    The trouble I run into is when I try to create forms that will actually assign books (or whatever) to people. Something the person using the database can pull up that they can say "this person, this book, click the button, okay now they're together."

    I've tried subforms, I've tried creating queries with the fields from both tables in them, I've tried all kinds of combo box shenanigans, but nothing seems to be working.

    How does one do this? I'm afraid I am past the limit of my Access knowledge. I just hope not TOO far past it that the problem isn't solvable.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't think tblStaff is the place for those fields. Since I assume I can have more than one book, software item, etc out, do you have multiple book fields, software fields, etc? Plus you have no history. I'd have a related table with fields for staff, item, start and end date/times. There would be any number of ways to present the data to the user. One would be a form/subform, where the form was based on tblStaff and the subform on this new table. Master/child links would keep the subform in sync with the form.
    Paul

  7. #7
    Join Date
    Mar 2011
    Posts
    9
    So, instead of what I have now, which is:

    tblStaff
    Employee_ID
    LastName
    FirstName
    Extension
    etc etc...
    Books - this being the foreign key that goes to...

    tblBooks
    Book_ID
    Title
    Author
    PurchaseDate
    etc etc

    Take the foreign key out of tblStaff and create a separate table, something like:

    tblBooksCheckedOut
    Requester - linked to Employee_ID in tblStaff
    Book - linked to Book_ID in tblBooks

    ?

    Am I reading you right?

    Oddly enough there isn't a concern about history - just in tracking who has what at the moment. (There is, in fact, a specific directive to NOT keep track of histories, but that's another story.)

    Thanks.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That's where I was heading, yes. I'd have start & end date/time, but maybe you don't care about that. If they can only have one book out and you don't want history, then your original structure could work.
    Paul

Tags for this Thread

Posting Permissions

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