Help needed with Module code

[Redbeard]

First - apologies for a long post. But I figured it is best to lay the
whole situation out rather than explain my problem in isolation.

You should know that I'm a high school teacher who took a month long
Access class back in the Access 97 days, and have hardly used it since.
I also do some Java programming on the side, but I've only tinkered
with VB. So I have big gaps in my knowledge base, but I have been able
to write a few modules as well as use the visual tools.

I'm trying to create a database for my school to use to keep track of
textbooks, and I've been "reteaching" myself as I go, but I've run into
a hurdle that has stumped me. I have two external tables that contain
student information by semester - Sem1 and Sem2. I have a second table
StudentTexts with the StudentID* (*key field) and fields for up to
seven textbooks (one AP course uses seven different books). I also
have TextbookData (ISBN* (*key field), Publisher, Author, etc.) and
CourseData (CourseNum* (*key field) and ISBN's for each text used).

Lastly, I have a table that (eventually) will contain a list of every
textbook used at our school. Right now, its fields are ISBN (so it can
link to TextbookData) and TextID. Neither can be a key field.
Obviously, if you have 200 Algebra books, they all have the same ISBN
number, but different ID numbers. But it is likely that there are
Algebra books and English books that have the same ID number. I've
considered making separate tables for each textbook so the ID could be
the key, but that complicates other problems. If necessary, I could
create a calculated field that combines the two which could be a key.

I've got everything working except ONE thing. I have a form which
allows me to select textID's out of a combobox for each student and
each of the seven possible textbooks and put that data in the
StudentText table. Each combobox uses a query to list the TextID's for
the appropriate books. I have the combobox set to LimitToList. If
something that is NOT on the list is entered, I can intercept that data
and ask the user if they want to add it to the list.

FINALLY - here is my problem. (Thanks for waiting)

How do I actually add it to the list? I'm guessing that I need to
create a recordset object and set it to represent the TextID table, and
write the code that will add the data for the two fields to a new
record. But I'm not sure how to do that, or if that is the best
approach.

Any help would be appreciated.

 

[Douglas J. Steele]

If you know all of the information you need to add to the table, you can
simply run an INSERT INTO SQL statement in the combo box's NotInList event
and set Response = acDataErrAdded

Alternatively, you can open a recordset and add to it (as illustrated in
http://www.mvps.org/access/forms/frm0015.htm at "The Access Web"), but
there's really no need for that.

If you don't know all of the information required, you can open a new form
from the NotInList event to add the new record to the table.

 

[Wayne Morgan]

For an example of using a combo box's NotInList event to add data, see this
link:

http://www.mvps.org/access/forms/frm0015.htm

As far as the primary key in your book table, if the combination of ISBN and
BookID will always be unique, you can set the primary key to be both fields.
Otherwise, you'll probably need a separate ID field to use as the primary
key.

 

[Redbeard]

Wow! Thanks for the quick replies. Once I figured out how to get the
ISBN number, that did the trick.

On the book table, you mentioned setting the primary key to be both
fields. Do you highlight both fields and click the key field button?
Thanks again.

 

[Larry Linson]

On the book table, you mentioned
setting the primary key to be both
fields. Do you highlight both fields
and click the key field button?

That'll work.

But, having fields for seven books in each Student Text record will, sooner
or later, cause you grief. Try using a similar table with one book per
record... foreign keys to the StudentID and to the ISBN and TextID of the
textbook. That will work a great deal better when you have to create Queries
against the data.

I'd question having separate Student Information tables, too. I'd combine
those and add a Year and a Semester Field. Again, sooner or later, someone
is likely to realize that historical information is useful for analysis, and
expect you to Query them.

Larry Linson
Microsoft Access MVP