Click list box item and display info elsewhere on form

[dalton]

I've got a list box that's pulling last name and first name. I want to be able to choose an person in the list box and have their home and cell numbers to display in two seperate text boxes. When I set up the list box it took me through a wizard, but when I added primary number and secondary number, it made me save it as a query, so now the list box is pulling from a query. If you need to know anything else about my sitution, let me know.

[pbaldy]

Presuming the numbers are in the listbox row source, try a textbox control source of:

=ListboxName.Column(x)

where x is the column containing the desired info. The column property is zero based, so the second column would be 1.

[dalton]

Thanks, that helped with my problem some. I've got it displaying now. A problem now is that while I can get it to display, it's giving me trouble. If I tell the query to have the primary_phone and secondary_phone, but to hide them, it always deletes them for some reason. I save it, but when I go into the program no phone numbers are showing and when I go back into the query they aren't there. If I put them back in and say to show them in the list box, then it does fine, but I'm still seeing them. I tried to set the list box to show two columns so you couldn't see the numbers, but it wouldn't work. If I set it back to show even just three, then both numbers would pop up below. Any thoughts of what i'm doing wrong?

[pbaldy]

If I'm understanding correctly, you want the fields included in the row source of the listbox, but the listbox column width for those columns would be 0.

[dalton]

I don't know about that. As I said, I have four columns of information. They are: last name, first name, primary phone, and secondary phone. I want to display the last name and first name in the list box, but not the two phone numbers. I want them to display in two seperate text boxes. I tried messing with the list boxes column width and it only chaged the width of the first column. I changed the column count, but it worked weird. If I had it set to two where you could see last name and first name, but not the two phone numbers, then it would display like I wanted, but would only display the primary phone number which was in column three. If I changed it to 3, then it would show the names and the primary phone number and would also show the primary and secondary numbers in the two text boxes. So I can get it to work, but only while displaying the primary phone in the list box.

I know I'm asking a lot of stupid sounding questions, but I'm new to most of this. I've had some programming classes a few years ago, but I tend to forget things I'm not using on a regular basis. I know some things, but usually just enough to get a general idea of what I want to do and maybe how to start going about it. So I hope you'll help me out and suffer through my ignorance. I also say this to let you know I may need things explained out a little instead of just being given a piece of code. Thanks a lot.

[pbaldy]

The column count should be 4, the column widths property would look something like:

2";2";0";0"

That will make the third and fourth columns invisible, but still available to the formula I posted earlier. If you're still stuck, can you post the db?

[dalton]

Okay, I wasn't understanding you before, but it hit me. I tried it and it fixed the problem of the visibility of the items. The only thing else I could want to do with that is to make it so that maybe the last name and first name are in one column. I don't really care whether they are or not, but the problem is that it's not just people going into this list. It's also companies and they may have a long name. Hopefully, I could make the column width auto adjust, but even if it did, the first and last name may be too far apart and look bad.

Also, you helped me with the problem of clicking on a list item and having it open the second form on that information. I got that mostly working. I can now click on a name and have it open in the second form. However, it's still making me type the last name twice before it opens that page. It is opening on their info though.

Should I be binding it to the last name or the primary key? And lastly, in the same line of questioning, I had a problem I figured out with binding it to the last name. One of my names was a company name and it had two parts. This left a space that through the code off. Would it be better to fix this with code or to change to the primary key so there wouldn't be a space ever. Thanks.

[pbaldy]

I would typically use the primary key for binding it, and for the form-to-form code. If it's asking twice, most likely a field name is incorrect. Can you post your exact code?

[dalton]

Option Compare Database
Private Sub lstName_DblClick(Cancel As Integer)
DoCmd.OpenForm "Phone", , , "ID = " & Me.lstName
End Sub

That's the actual code in the event procedure. Now that i've fixed everything, it's only asking me to type something into a pop up data entry form once, but I don't want to do that at all. I want it to go through to the second form as soon as you click on the name with no stops in between. I added ID to the query that it's pulling from and made that the field name. So now I have to know what the ID is and type that in or it opens a blank second form. I believe you said you wanted me to post the database, at somepoint. How would I do that. Thanks for the help so far.

[pbaldy]

If you click on Post Reply, you will see an Manage Attachments button. You should be able to attach a zipped copy of your database there (compact it first to make it as small as possible).

[dalton]

Here is a copy of the program so far.

[pbaldy]

The bound column of your listbox is the last name (bound column is 1, first column is last name). You can either change that to 5 to match the ID column, or change the code to:

DoCmd.OpenForm "Phone", , , "ID = " & Me.lstName.Column(4)

[dalton]

Thanks, that has it working correctly now. I don't know the protocol on here, but I figure that if I have more questions that aren't about this particular topic, then I should start a new thread. I do have more questions, but will start them in a new topic if that's what I need to do.

[pbaldy]

Happy to help. You are correct: new, unrelated questions should be asked in a new thread.