Noob needs help, combo box not populating fields with data, why? (1 Viewer)

[oZone]

I'm new to Access and could use some help figuring this out. It should be a super easy fix for someone with Access experience... I'm just a noob!!!

I have a DB that I use to keep track of friends and am trying to create a form that makes it easier for me to edit existing friends, or enter new friend info into the DB but I cant get the form to populate friend contact info when I select a friend name from a combo box.

The Name combo box should list all the friends in the DB. When you select a friend from the combo box, that friends details should be populated to the rest of the fields (Phone, Address, City, State, Zip), but it doesn't. I've tried to figure it out but I'm stumped and could use some help.

DB Attached below.

Thanks!

 

[Premy]

Well there are several ways to do this. I'll give u one of the easiest here:

In design view, select combo122, go to data tab and clear the line for ControlSource (a lookup combo should be unbound). Clear the line for RowSource and add the following code to it:

SELECT tblFriendList.FriendID, tblFriendList.Name FROM tblFriendList ORDER BY tblFriendList.Name;

Now go to the data tab and clear all existing event procedures there. Click the Procedure builder button for the afterupdate event, and add this code to the afterupdate event:

Private Sub Combo122_AfterUpdate()
Me.FilterOn = True
Me.Filter = "FriendID=" & Me.Combo122
Me.Requery
End Sub

HTH

 

[oZone]

That worked! Thanks a ton!

I have another form that's not working also, I'll apply what I learned here to that form and see if I can get it working too. If I cant, I'll post it here and see if someone could help me with it also.

Thanks again!!!

 

[oZone]

Okay, I tried to apply what you showed me above to a different form, and I'm close, but the form is still doing some strange stuff and not working quite rite.

I have a form that lists friends who have borrowed movies from me, and it has a combo box that lets me select the friends name and it's supposed to populate the rest of the fields in the form similar to the first one you helped me with above.

The friend details are populated to their respective fields in the form, and also some information about what movie(s) they've borrowed (loan date, movie title, and if it has been returned or not).

The form has a second combo box in the Details section that lists the movies they have borrowed, but also allows me to add a new movie loan by selecting a movie from the combo box and entering a loan date.

As it is now, when I first open the form, it shows an empty Name field, and lists every single movie in the database in the Details section instead of it showing just the first record in the LoansList table and just the movies that are associated with that record.

If I then select a friend from the combo box the form populates their info and borrowed movie details as it should, HOWEVER, as soon as I hit the drop down arrow in the second movie combo box (details section), the form resets itself for some reason and the friend contact information disappears from the form (dont know why...).

Also, if I select a movie already assigned to that borrower and try to change it to another movie I get this error message:
"The value you entered isn't valid for this field."

After dismissing this error message it allows me to change the movie to a different title without issue.

I tried as best I could to get it working, using the technique described above, and feel I'm much closer to getting it working this time but still theres something I'm not quite understanding.

I've attached the DB with the form in question below, could someone take a look at it and tell me how to fix my form?

Thanks

 

[Premy]

Ok, several issues:

1. The loan details should be in a subform
2. The movie combo should be bound to MovieID, not MovieTitle
3. The Rowsource for the movie combo should be something like:

"SELECT tblMovies.MovieID, tblMovies.MovieTitle FROM tblMovies ORDER BY tblMovies.MovieTitle;"

4. You should add FriendID as a hidden field on the mainform, for the friend combo Afterupdate event to work.

There may be more, but fix these first and we'll see...

HTH

 

[oZone]

Thanks for the help.

Eventually, when the form is working correctly I plan on using a query for the forms record source for the purpose of not displaying currently loaned movies (so the form will not allow me to loan the same movie to two people at one time). I've already added a "Loaned" Yes/No checkbox field to the Movies table to accommidate this change. I planned on specifycing: <>Yes for the Loaned field criteria in the query. Hopefully this will not display any currently loaned movies in the subform movie combo box... hopefully...

But before I do that, I at least need to make sure the form is working correctly. E.G. Allowing me to either edit existing loans (changing the Loaned checkbox, or changing the movie loaned), or assign more loaned movies to borrowers.


As far as your suggestions, I did what you said, and I'm closer to getting it working correctly, but the form still needs some work.

The subform does change when you select different people, but it doesnt display ALL the loaned movies for a particular person.

I'll upload the updated version of the DB, but when you open it, select Steve Spatulara (FriendID 22). It will only show one movie in the subform, but he actually has 6 movies assigned to him.

Also, the subform combobox will display movies, but wont let me select any...?

Attached updated DB.

 

[Premy]

1. The Child/Master link between sub and main should be made thru FriendID, not MoivieID.

2. Remove tblBorrowers from the sub form's record source: it has nothing to do there. U should use tblLoans FriendID

3. To prevent re-lending, u could create an index in the loan table, combining FriendID + movieID and set it to not allow dups. So u won't need the check box any longer, or change it's caption to "Returned". U could create a custom msg to pop up when the user tries to re-lend a given movie, instead of the standard access err msg.

4. Change the friend's combo rowsource (combo48, main form) to:

"SELECT DISTINCT tblLoanList.FriendID, tblBorrowers.Name FROM tblBorrowers INNER JOIN tblLoanList ON tblBorrowers.FriendID=tblLoanList.FriendID ORDER BY tblBorrowers.Name;"

This way only friends with loans will show up in the combo; as it is, all friends show up. If u decide to use the returned checkbox, as suggested above, u could take this a step further by creating an option group on the main form with 3 radio buttons: "All", "Loaned Only", "Returned Only". Selecting "All" will show all the movies a given friend has ever borrowed, including the ones he hasn't returned yet; should be clear by now what the remaining 2 options stand for. Of course u'll have to modify the combo's afterupdate event to take these options into account.

HTH

 

[oZone]

Thanks for the help Premy! Its working good now.

I prefer that the combo box shows all friends, that way I can add loans to friends that arent currently borrowing movies (add new loans). I set the Friends combo box source to use FriendID from the Borrowers table and it now lists all friends, but it's doing something strange.

When I select certain friends, it doesnt show their contact info, but for others it does. Any ideas why and how to fix this?

EDIT: I just realized it seems to be showing contact info only for those friends who are currently borrowing movies. Friends who arent borrowing movies show no contact info... I dont know how to fix it, but thought I'd throw that out there in case it helps you figure it out.

 

[oZone]

Just an update, I solved my problem

I made the forms record source a query containing the borrower details and that solved the blank details issue.

Now the form is populating all fields like it should.

Thanks for your help Premy, most appreciated!