Forms With names instead of ID

[mikevdv]

Hello and thanks in advance for your help.



I have a problem

I have 2 tables, Books and Authors

Authors contains these rows: AuthorID, Name, gender, Date_of_birth
Books contain these rows: BookID, AuthorID, Title, Date, Pages,

I want to make a form to add new Books to my database.
If i make a normal form, I can input a value for AuthorID, Title, Data, and pages

There is a problem with this however: If I have a book near me, and want to add it to the database, I need to know the AuthorID of the Author of the book.
This is a bit not very efficient, because this would mean that I have to look up the AuthorID of the Author of the book in the table 'Authors' first before I can add a new book to the database.


Is it possible to make a listbox or just a place where I can put in the authors name (in the form) where I can type/select the name of the author, and make it so that access automatically adds the corresponding ID to the form?

So instead of looking up the ID of the author, I want to put in the Name of the Author.
And after I put in the name of the author in the form and save it, the table should show the author ID of author.


To give you an example:
The table Authors contains the entry: 1, shakespear, male, xxxxx
When I want to add a book of Shakespear, I want to enter something like the following in the form:

BookID = 1
Name = shakespear
Title = xxx
Date = yyy
Pages = zzz

instead of

BookID = 1
AuthorID = 1
Title = xxx
Date = yyy
Pages = zzz

So when i put in the first form, the updated table (books) should contain his authorID and not his name

[Micron]

answer depends: what is AuthorID, an autonumber field? Before going any further, I strongly suggest you correct what will become a problem eventually, and that is your use of reserved words (Name, Date).
http://allenbrowne.com/AppIssueBadWord.html

Also, you seem to have a comparable ID field for book, but no comparable field as in author's name. Your tables might need some tweaking.

[Gicu]

mikevdv, this is an extremely easy thing to do and one of the main uses for combo-boxes. Open your existing form in design view, right-click on the AuthorID textbox and change it to combo-box. Then in the properties sheet click on the data tab and on the row source click the three dots to build your SQL (the row source type should stay as Table\Query), add your authors table and select AuthorID and AuthorName. Finally in the columns property enter 2 and set the column widths to 0";3" (you are hidding the authorid column but it remains the bound one).
Now if you type shakespear in the combo you will see that the books table actually stores 1 (the authorid).
Cheers,
Vlad