Access Lending Library Template and customizing/linking tables to the form

[a1hunter]

I will start by stating that I am the lowest level Access user out there, in no way a programmer (GUI yes, true coding, not so much but I have a basic grasp of the concepts), but there's very little software I can't conquer eventually. However, I'm having one heck of a time getting accomplished what I'd like to here, and it seems to me that it should be relatively simple. I thought Access might be the simplest way to go, using the "Lending Library" template, as that is really what I'm trying to do - setup a relatively simple lending library for books/magazines. I have also looked into Google Forms and Excel, but can't find a good solution there, either. I'm running Win7 with OfficePro 2010 in an education environment. (I know of and used to use Tek-Tips frequently in past lifetimes when I worked in IT Help Desk environments on the software application side of things, or managed the hardware/technical side technicians)

I have an Excel document already created with the names and publishers/authors of a few books for different groups (Dance, Theater, Music, General Instruction), but the Excel list will be growing exponentially (and potentially shrinking as resources become outdated), and I want to use that as my basis - the resources will also expand to magazines or other literature/non-digital (i.e.: CD's/DVD's) types. I want to use the "Form" so that users of the resource can relatively easily check a resource out then back in, and have that form reference or populate, as per the field needs. I have attached my Excel document with queues and comments as to what I want each field to do, but will describe here, as well, since it's fairly simple:

When a user comes to check out a book, they go into the form and it automatically records the date/time that they open/use the form. The user then selects a resource from the drop-down by title, which then auto-populates the form with it's correct Course of Study (General, Art, Dance, etc.), Type (Book, Magazine, CD, DVD), Publisher/Author - these items are all pre-populated in the Excel spreadsheet. The next field(s) would be a selection for the date they Checked Out the item (this would set the time automatically), then have a text field for them to type their name (and email?). They would later go back into the form, find the checked out item, and Check it back In, by selecting the date (which would again set the time automatically).

I have figured out how to import my Excel spreadsheet and link it, but for the life of me I cannot get the table relationships to connect properly, nor figure out how to change the data on the existing "Asset List" form to correspond with my spreadsheet fields. I have tried going into Form Design view and changing the form there, but it's not really cooperating, and the same goes for trying to use the relationship option in Database Tools to "link" my fields to the existing tables. I've even gone so far as to finally try to create it new, using a blank template, and creating my own form from scratch, but that's not quite syncing up properly either, as it seems like I have too few tables running.

At the very least, if someone could direct me to a great tutorial on doing something this "simple" - whether it be using Excel, Access or Google Forms (I could do the Google Form, fairly easily, but I would still want to link a drop-down box to my spreadsheet and still have it auto-populate the Type, Publisher/Author and Course of study, and I haven't found anything indicating Google has become that advanced without some heavy back-end programming knowledge), I would greatly appreciate it!

Thank you in advance for any guidance, suggestions or help anyone is able to give!

 

[LaurieHamlin]

Can you explain your tables? i.e. what's in them
Then someone can help you with the relationships. That would be the first step.

 

[a1hunter]

LaurieHamlin, thank you. I did upload them, but some things have changed on this forum and I'm not sure if the file was readable or properly attached with the original spreadsheet.

It's SUPER basic: Excel spreadsheet with the following columns:

COS (Course of Study - Art, Dance, General/Integrated, Music, Theater), Ref Type (Book, Handout, Magazine, Catalog, etc.), Ref Description (title of book/magazine), Publisher/Author, (Would like AutoGenerated time stamp for when item is checked out), Resource user's name/School, Resource user's email, (Would like AutoGenerated time stamp for when item is checked in), Resource User's name/School, Qty (This is how many of each reference we have available to be checked out).

Basically, I want the user to be able to pull up a form, select the COS, & Ref Description, and have the Ref Type, Publisher/Author, Checked out date/time auto-populate (Ref Type & Author from the database based on the Ref Description/COS), the user input their name/School and email address (This would be saved for later reference so they only had to do so once???), then the next time they access that record, and put their name in the "Checked In" field, it auto-populates the Date/Time field for Checked in. Would also be nice if the reference item was able to be greyed out if the max quantity has been checked out, until the resource has been checked back in.

ULTIMATELY, we'll be running reports off the database/form to find out who has what resources, when they checked them out, and be able to track them down and retrieve them...but otherwise, it's a more or less (to me, seemingly) simple project.

THANK YOU!
Amy

 

[SkipVought]

HI,

How deep do you really want or need to go?

For a super simple approch you could run with one table as posted in Excel. But you'll find it wanting if you need to inventory the various items you have and really want to have a history of activity on each resource and on each user.

Are you prepared to design and launch a reasonably well designed database that has multiple tables that will store all the data you might need to trace history, predict trends. What you display in your Excel sheet might be classified as a report of current activity but bears no semblance to prior activity.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]