how do you make a autocomplete form with a editable subform underneath

[cappuccino]

hi i am new to this forum thing and i am making a library database which would be used in a school.
i need to make a form which allows me to type in the persons id number then the persons profile will come up automatically then underneath there will be the persons book loans in a subform which i dont know how to do

so the top bit will have the persons details like personid, surname, first name, tutor group and then below there would b a subform with fields in from two different tables like from the book table there will be author surname, author firstname, bookname then from the loan table there will be the date the book was loaned field

the subform would also have to let me edit it so that i can add new loans or remove loans which i also dont know how to do but i was thinking if i had to add new records of the book would the subform have to autocomplete too? to save time typing all the details of the book

so so far i have made the tables added some test data and made relationships between the tables

a person can have many loans
a book can have one loan

i think there is something to do with queries to make this whole thing work but i am totally lost what i have to do, ive been reading some forum posts which are similar about autocomplete forms which i have done but it wont work when i do it with the problem i have

sorry that this is so long winded but please help!!!!

i understand that my question isnt exactly very clear, im not that good at explaining things but please help!

[alansidman]

Here's how to do the first part.

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

Then look at this on subforms

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

That should get you started. Post back with specific issues.

Alan

[cappuccino]

hi i read your posts and im sorry for posting at multiple forums!!! i was just quite desperate aha
anyway i looked at both of the videos you sent and they were a great help!

however i am still stuck on the subform thing
let me tell you what is happening so far

so now in my form i can use the combo box to select the person i want to view the loans he/she has taken out in the subform and then the details of the person are at the top

however i cant edit anything in the subform like add a new book in their loans which is from the books table

because i was hoping that when i typed the barcode into the subform under the barcode field, the other information would come next to it like book name, author etc but it wont even let me edit and idk how to it it either

thanks again!!!!!

[alansidman]

Difficult to analyze without seeing your db. Suggest you sanitize it for confidential information. Compact and Repair it and then post it for review on this forum. Suggest you convert it to a 2003 version database for the upload as many here still have not upgraded and you want maximum review.

[cappuccino]

so shall i upload it here? all data in it is fake anyway so that would b no problem if u would like to look at it for me !
thanks again!

[alansidman]

Upload it here. Click on Reply, then advanced.

[cappuccino]

how do i change it into 2003 format?
i tried uploading it in my format but it said it exceed file limit or something like that

[alansidman]

Do a Save and Publish. Then a Save As. Select .mdb. Then do a compact and repair. If it is still to large, then zip the file.

[cappuccino]

ok i have attached it underneath

thanks again!

[alansidman]

In setting up your custom filter, there were a couple of things you overlooked. You did not set the form record source as shown in the video nor did you set the requery in the after update for the selector. I changed these. Take a look at the video again on custom filters and you will see them. Then look at your db that I fixed which is attached.

[cappuccino]

i dont understand what you did because before when i use the drop down list and i select a person the persons details will fill in automatically (like name, personid and their book record)

then you said you changed something which i dont understand what you changed, but when i opened the file (which u changed) and i go on top the drop down list and select a person, the fields doesnt update anymore, neither do the books.

also i was wondering why it wont let me add records to the subform because people would want to get more books out but wen i try typing something in it would say "field could not be updated"

[alansidman]

Interesting. When I initially opened your file, the custom filter did not operate and it did not update the subform. I made changes and it worked for me. Post your AC 2010 file and I will look at it tonight when I get home. I have 2010 there but not in the office. I wonder if it is an issue between versions and something is changing because of it. I am very confused right now.

[cappuccino]

thank you, i have sipped the file
thanks for helping me look at it!

[alansidman]

I've gone over your db and am completely stumped on how to fix it. Hopefully, someone else will jump in and give it a try.

[June7]

Jumping in! I downloaded the last posted version of the db and made following mods.

Change the RecordSource query of the subform from INNER to RIGHT JOIN. Also, need the BarcodeID field from the tbl_loan not tbl_book. The textbox is bound to wrong field - it is trying to populate tbl_book not tbl_loan:
SELECT tbl_loan.BarcodeID, tbl_book.DCode, tbl_book.Surname, tbl_book.Forename, tbl_book.BookName, tbl_loan.DateOut, tbl_loan.PersonID
FROM tbl_book RIGHT JOIN tbl_loan ON tbl_book.[BarcodeID] = tbl_loan.[BarcodeID];

I also had to set BarcodeID and PersonID fields in tbl_loan as compound primary key.

Do you care about the loan history of each book or do you just want to know who has it NOW?

Might check out the Lending Library template from Microsoft. It can be chosen from the templates offered when you create a new db.