I've made a home movie library database and for the most part it's complete but I thought of a handy feature to add last minute but I cant get it to work rite and need some help with it.
The main form lists movie details (title, genre, rating, description etc...) depending on which movie the user chooses from a combo box. I also have included borrowers tables and made a few forms that keep track of people who borrow movies.
What I want to do (and the part that isnt working) is put a text box on the main form that lists who (if anyone) is currently borrowing the currently selected movie.
The main form gets its data from a query, and I tried including the FriendID and Name from the Borrowers table in this query, then made a text box whos source was Name, but it didnt list the borrowers name.
I then tried to use a DLookup function for the text box, but I'm vaguely familiar with it and dont know how to get it working. I got it as far as displaying the FriendID, but for some reason cant get it to display the borrowers Name. Here's the DLookup that displayed the FriendID:
Since I couldnt get that to work I tried a different method that also doenst work rite. I added the FriendID and borrowers Name field to the combo box that selects a movie. I then had the text box look up the correct field in that combo box to display the borrowers name.
Well, it works, and the borrowers name does display in the text box, BUT, now the only movies displayed in the combo box are ONLY the movies that are currently being borrowed!!! lol All other movies that arent being borrowed are not displayed in the combo box... this is definately NOT acceptable.
The combo box needs to display all movies, regardless if they are loaned or not. The borrower Name text box can be blank, if nobody is borrowing that particular movie.
Can someone help me get this DLookup function working? Or provide an alternate solution to displaying who is currently borrowing a movie?
Attached DB below.
The main form lists movie details (title, genre, rating, description etc...) depending on which movie the user chooses from a combo box. I also have included borrowers tables and made a few forms that keep track of people who borrow movies.
What I want to do (and the part that isnt working) is put a text box on the main form that lists who (if anyone) is currently borrowing the currently selected movie.
The main form gets its data from a query, and I tried including the FriendID and Name from the Borrowers table in this query, then made a text box whos source was Name, but it didnt list the borrowers name.
I then tried to use a DLookup function for the text box, but I'm vaguely familiar with it and dont know how to get it working. I got it as far as displaying the FriendID, but for some reason cant get it to display the borrowers Name. Here's the DLookup that displayed the FriendID:
Code:
=DLookUp("[FriendID]","tblLoanList","[MovieID]=Forms![frmAddEditMovies]![MovieID]")
Since I couldnt get that to work I tried a different method that also doenst work rite. I added the FriendID and borrowers Name field to the combo box that selects a movie. I then had the text box look up the correct field in that combo box to display the borrowers name.
Well, it works, and the borrowers name does display in the text box, BUT, now the only movies displayed in the combo box are ONLY the movies that are currently being borrowed!!! lol All other movies that arent being borrowed are not displayed in the combo box... this is definately NOT acceptable.
The combo box needs to display all movies, regardless if they are loaned or not. The borrower Name text box can be blank, if nobody is borrowing that particular movie.
Can someone help me get this DLookup function working? Or provide an alternate solution to displaying who is currently borrowing a movie?
Attached DB below.