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.
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.
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.
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.
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.