Query adding content to two fields?

[acwest]

Hi,

I am trying to create a simple library system that can issue and return loans. At the moment i have created a working system to issue loans. I have created a query that inserts the correct information into the borrowers table but for some reason it inserts the borrower ID into the first borrowers id field and also the correct one.

It is kind of hard to explain. I have attached the database. If you open it and open the form called loan, then select a borrower and cd and click issue. If you then go to the CDCopy table you will see that the borrower you selected appears twice on two different records, one correct and one not.

Is there something i have done wrong to my query or something.

Thanks in advanced.

 

[pbaldy]

Your form is bound to the table, so when you select a borrower, you're changing the record being displayed (the first one).

 

[acwest]

I have unbound it now (I de-selected record source on the form?) but now it wont let me select anything in the borrowers list box?

 

[pbaldy]

You would also want to remove the control source from the listbox.

 

[acwest]

Thanks that works a treat now.

Just one more problem I have got a return loan form with a drop down box with a list of borrowers who have a loan and a listbox which will be populated based on the dropdown box. However if for example a borrower has two loans they will appear twice on the drop down box.

Is there some sort of code that i can put in the row source to show borrowers loaning more than one item only appear once on the drop down box.

Thanks i have attached the database again.

Thanks this forum has been a great help

 

[pbaldy]

You can either change the query to a totals query or add DISTINCT:

SELECT DISTINCT Borrowers.BorrowerID, Borrowers.Forname, Borrowers.Surname, CDCopy.BorrowerID
FROM Borrowers INNER JOIN CDCopy ON Borrowers.BorrowerID = CDCopy.BorrowerID
WHERE (((CDCopy.BorrowerID) Is Not Null));

 

[acwest]

Thanks again.

This sites been a REALLY big help.

 

[pbaldy]

Happy to help!