Access 2007 - various issues.

LeumasWhite

So, our fantastically useless teacher has given us basically no help for the assignment he gave us, and pretty much no actual lessons either (there's been numerous complaints about him, but that doesn't help right now). We've got a basic relational database to sort out, for a library computer system; entering books into a database (along with stuff like authors and publishers, in separate tables), checking them out and in, keeping track of the students who have book and issuing late notices.

Here's the database outline, from the assignment:

I need to create four forms: one adding and viewing students, a similar one for books, and two other forms for issuing and returning books. The problem I'm stuck on currently is how to update authors and publishers in the book form; either I do a lookup, and I'm limited to a set list of authors/publishers pulled from the appropriate table (meaning I need to add new entries to those tables manually), or I do a straight one-to-many relationship, which is a slightly more awkward version of the same thing. I can't figure out an option to update those tables when you add an entry that doesn't yet exist; if I have authors A, B, and C, and I enter D in the book form, it'll just add it to the author table.

There'll be other issues later, but that's the immediate one. Halp?

Mr. Pokeylope

I think the problem is the record source your using for the forms. Are you trying to use the book table as the record source?

To solve the problem off the top of my head you can use a query to pull info from multiple tables as the record source. Then as new info is placed in the form it will automatically be entered into the proper table.

LeumasWhite

Queries do that? How would I go about creating one? Just add all the fields from tblBook, and work off that?

Chen

The easiest way to create stuff is to use the wizard. Right click a query, go to query design and hopefully you can edit the relations in the database. It's been too long ago since I worked with Access, so I could be wrong.

LeumasWhite

Okay, so, bringing this back up. How would you get a drop-down list on a form that displays the possible entries for it (so, a list of publisher names, for example), but have the actual data stored be a different value relating to that record (such as the record ID)?

Mr. Pokeylope

You can select both the ID and the data to be in the drop down box. The drop down box can be configured to only show the data if you wish.

It should allow you to do that while setting up the drop down box. In either an option to hide the ID or by adjusting the columns during set up. During setup you should be able to pick which column is going to be stored in the field too.

LeumasWhite

Cool. Now how do I set up a form that will truncate entries? If somebody puts in "Science" or "English" on the form, I need it to be cut down to "CS" or "CE" in the actual table.

LeumasWhite

Well, okay, I'll try a couple of easier ones: I have a field in a query that needs to be equal to a date (also in the query), plus three weeks. No idea how to do the expression. Also, if I've got a button that opens a form with a list of authors, how do I get that list to update in the first form I open it from?

Pheezer

Use the Help file. We don't do homework here.