Form pulls info from 2 tables.

[Jonpro03]

I am working for a small literacy program and working to create and maintain a database for the library of books and other materials they lend.

I created a database using an excel spreadsheet that they had been maintaining and created a form called Lending Library to pull, modify, save, delete, and create records.

Previously, the name of the individual who checked out the material was manually typed in. I've been asked if I can add phone numbers to the form. I have a separate database of every person associated with the company with phone numbers and email addresses. I imported that (excel) spreadsheet to a table called Contacts.

So now I have two database tables, one called Contacts, one called Materials, and one form called Lending Library.

The Lending Library form currently has a combo box which is used to select records from the Materials table. I tried adding a second combo box to the form that gets values from the Contacts table and it works. It shows the name and phone number, but only in the combo box. I would like to have the ability to select a contact from that combo box and have it fill in a couple of text boxes on the form.

I hope this is not too confusing and I hope you can help me. I'm using Access 2010 on Windows XP 32.

[alansidman]

Perhaps this tutorial will guide you to your solution

http://www.datapigtechnologies.com/f...tomfilter.html

Alan

[Jonpro03]

Sadly, that wasn't as helpful as I had hoped. I already have a combo box that can select a record from the Materials table. How can I add another combo box that will select records from the Contacts table and add that information to the same form. Also it needs to append the selected contact to the selected Material so that when the record is pulled up later, we can see the contact that checked out the material.

[alansidman]

Perhaps, if you posted a shortened (sanitized) copy of your db, it might be easier to understand what you are attempting to do. Be sure to run a compact and repair before you post your db as that will reduce the size. It is difficult to understand without seeing the data structure of your tables and forms.

[Jonpro03]

I can do that, give me a moment to prepare it.

[Jonpro03]

I managed to achieve my goal in a round-about way. I have a combobox that pulls records from the Contacts table and saves the ID to a new cell I created on the Materials database. I labled that cell CheckoutID. I then joined CheckoutID on the Materials table to ID of the Contacts table using the relationships page.

Then I was able to create a subform with the Master field linked to the CheckoutID I created. The Child field is linked to the ID of the contacts table. Then the only thing left was to have the subform update whenever a new contact was selected form the combobox. So I edited the properties of that combobox, slected Event - On Change and added the following code

[Contact subform].Requery

I tried to upload the database but I cannot get the filesize under 500kb :/
Here's a screenshot to give you an idea of what I did.

[alansidman]

Glad to hear that you solve it. As to the size, did you try to run a compact and repair before uploading. When developing an Access db, it is good to do this often as when you make changes, Access does not reduce the size if you delete items, fields, tables, etc. The C&R does this.

Alan