Using combobox to update tables

[UKM]

I'm using Access 2003. I have three tables to model a simple
many-to-many relationship betweeen authors and titles of books (as an
example of my problem).

- Authors: has Id (autonumber, primary key) and AuthName (str, indexed, no
dups)
- Books: has Id (autonumber, primary key) and BookName (str, indexed, nop
dups)
- Book_Author: has Id, BookId and AuthId

A Titles query that maps the Book_Author id fields to their respective tables
shows me book titles and authors, just like I want.

I now want a form with two comboboxes that lets me enter new titles:

Author: -----------
Title : -----------

I'd like each combobox to show me available values or allow me to
enter a new one. My form is based on my Titles query. My Author
combobox has Authors.Name as its Control Source, and 'SELECT
Authors.Name FROM Authors;' for its Row Source.

It looks right, but doesn't work. Two problems:

1. If I enter a new author name and book name, the tables are
correctly updated, but the combobox doesn't show me the new value
when I try to add a subsequent field. Only when I close and re-open
the Form do I see this.

2. More importantly, if I select an existing name or title, I get an
error complaining that adding the field would violate an index
constraint. It makes sense to me that I'm getting this error (if I
entered an existing Author's name in my Titles query datasheet
view, I'd get the same error), but I'm not sure how I teach the
form to do what I want and use the AuthId of the author I selected
rather than try to insert a new one.

Any pointers greatly appreciated.

Thanks,
-UKM

 

[Arvin Meyer [MVP]]

Have a look at the NotInList code at:

http://www.datastrat.com/Code/NotInListCode.txt

or the demo database using this code at:

http://www.accessmvp.com/Arvin/NotInListDemo.zip

 

[UKM]

Thanks! This had everything I needed. Writing up some notes I found in case
others may find them useful or have comments:

- The form doesn't need to be based on the query: in my case, since the
table I was trying to update was Book_Author, set its Record Source to that.

- Now things make more sense: the form fields are bound to BookId and AuthId
(which of course will avoid the duplicates problem I had). The problem now is
to make sure your comboboxes actually show names and not ids. Do this by 1)
selecting both id and name in the Row Source, 2) making sure Bound Col is 1
and 3) (the part I didn't see before) set your column widths to 0";2" will
ensure that even though we're selecting and inserting ids, only names show up
in the form.

- To add new entries correctly, we really want Access to always pick an id
from the right table. So we set Limit to List to "Yes", then trigger the
NotInList event. Arvin's sample code then launches a subform; I just needed
to inject the new name into the right table. Found some useful code here:

http://allenbrowne.com/ser-27.html

.... the key parts being 1) creating a sql statement to INSERT INTO
Authors.Name 2) running that sql by calling DBEngine(0)(0).Execute and 3)
returning acDataErrAdded

Hope this is useful. Thanks again.
-UM