Need help fixing a form that uses combo boxes to retrieve data.
I have a home movies DB and have forms set up for keeping track of loaned movies, but my AddEditLoans form isnt working rite and I dont know why.
First, I have a combo box in the main form to select friends names. When you select a friend from the list, the form is populated with his/her details including contact info, movies borrowed, dates, and if the movie was returned or not.
That part works okay (I think...) because it does populate all the data accordingly. The problem I'm having, however, is with the subform.
In the subform are the borrow date, movie name, and a "returned" checkbox. The movie field is a combo box to allow the user to select movies to add a new loan, or to edit existing loans.
This subform combo box lists all the movies in the database, but some movies it wont let me add to a loan, and others it will (seems random and quite strange...).
When I select the movies it wont let me add, I get the following error message:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship"
But as far as I can tell my LoansList table doesnt have any PK's or indexed fields (this is the only table the form should be updating), and the Friends table only has one PK (FriendID) but this form should'nt be trying to update the FriendID... So the error message doesnt make any sense...
When the form does let me select a movie something even MORE strange happens. I remember which friend I just edited, close the form, open the Friends table and find his/her ID, then I open the LoanList table to find that friends ID and it should list the MovieID's of the movies I just added to this friend. Well, heres the weird part; instead of assigning the correct MovieID to this friend, the form somehow creates a NEW MovieID, assigns the friend with this new MovieID AND creates a NEW MovieID in the Movies table with blank movie details!!!! AHHHHHH!!!!
It's wonky, and I'm confused.
Can someone help me sort this out? 
I'll attach the DB below.
Ultimately the form should allow me to assign new movie loans to friends, or edit existing loans, WITHOUT adding any data to the Movies table.
I have a home movies DB and have forms set up for keeping track of loaned movies, but my AddEditLoans form isnt working rite and I dont know why.
First, I have a combo box in the main form to select friends names. When you select a friend from the list, the form is populated with his/her details including contact info, movies borrowed, dates, and if the movie was returned or not.
That part works okay (I think...) because it does populate all the data accordingly. The problem I'm having, however, is with the subform.
In the subform are the borrow date, movie name, and a "returned" checkbox. The movie field is a combo box to allow the user to select movies to add a new loan, or to edit existing loans.
This subform combo box lists all the movies in the database, but some movies it wont let me add to a loan, and others it will (seems random and quite strange...).
When I select the movies it wont let me add, I get the following error message:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship"
But as far as I can tell my LoansList table doesnt have any PK's or indexed fields (this is the only table the form should be updating), and the Friends table only has one PK (FriendID) but this form should'nt be trying to update the FriendID... So the error message doesnt make any sense...
When the form does let me select a movie something even MORE strange happens. I remember which friend I just edited, close the form, open the Friends table and find his/her ID, then I open the LoanList table to find that friends ID and it should list the MovieID's of the movies I just added to this friend. Well, heres the weird part; instead of assigning the correct MovieID to this friend, the form somehow creates a NEW MovieID, assigns the friend with this new MovieID AND creates a NEW MovieID in the Movies table with blank movie details!!!! AHHHHHH!!!!
It's wonky, and I'm confused.
Can someone help me sort this out? I'll attach the DB below.
Ultimately the form should allow me to assign new movie loans to friends, or edit existing loans, WITHOUT adding any data to the Movies table.
Last edited:
