A List Box, Image, and A Database...

This is going to be hard to follow but here I go:

Here's my problem. I am pulling a list of last names from a database called members.mdb and populating a listbox called list1 with the names. What I am trying is set it up so that when you click on a last name in the list box it shows the picture of that person in an image box called Image1.
And the picture goes with the path that has been set for it in the database.

NOTE: It get's the path to the picture from the same database table in a field called PicturePath.

PLEASE HELP!!!!!!

I haven't used image boxes but I would use the same routine (basically) that you use to populate the list to take the Name they selected, goto the database, find the required record and grab the file name and then set the filename in the Image box. I haven't included code because it depends on how you are accessing the database. If this doesn't put you on the right path, email me a snippet of how you access the database and I'll see what I can do.

Matthew
[email protected]

Here is what I am doing as far as accessing the DB.

Private Sub Form_Load()
Dim name As String

Data1.RecordSource = "SELECT DISTINCT LastName, FirstName, PicturePath FROM MemberInfo"
Data1.Refresh
While Not Data1.Recordset.EOF
name = Data1.Recordset("LastName")
name = name + ", " + Data1.Recordset("FirstName")
List1.AddItem name
Data1.Recordset.MoveNext
Wend

End Sub

In the list box select/change whatever sub try this:

dim strName as string, intI as integer, strTemp as string, strNameFind as string, strPic as string
strName = List1.text
for intI = 1 to len(strName) 'need to go through string to get only the Last name
strtemp = mid(strname, inti,1)
if strtemp = "," then
exit for
end if
strNameFind = strTemp & strTemp
next intI

'now you've got the Last Name, find it in database

Data1.RecordSource = "SELECT DISTINCT LastName FROM MemberInfo where LastName = '" & strNameFind & "'"
Data1.Refresh
If not Data1.Recordset.EOF then
strPic = Data1.Recordset("PicturePath")
end if

Image1.filename = strPic


Note the '" & strNameFind & "' you need both the ' and the " around the string because it's a string, if it's a integer, you only use the ". That should work for you. I usually also include Data1.close when I'm done with it, because I don't like leaving my database connection open.

Either store the Image Paths for the Members in an Array as you fill the List, or Store the Unique ID for Each Record, (Assuming you have one setup), in the ItemData Property of the List Items, so that you can then Get the Picture from the DB when the Name is selected, eg.

Store Paths in an Array..

Code:

---

Private aPaths() As String

Private Sub Form_Load()
    Dim name As String
    Dim iName As Integer

    Data1.RecordSource = "SELECT DISTINCT LastName, FirstName, PicturePath FROM MemberInfo"
    Data1.Refresh
    While Not Data1.Recordset.EOF
        name = Data1.Recordset("LastName")
        name = name + ", " + Data1.Recordset("FirstName")
        List1.AddItem name
        ReDim Preserve aPaths(iName)       
        aPaths(iName) = Data1.Recordset("PicturePath")
        iName = iName + 1
        Data1.Recordset.MoveNext
    Wend
End Sub

Private Sub List1_Click()
    If List1.ListIndex > -1 Then Image1 = LoadPicture(aPaths(List1.ListIndex))
End Sub

---

Or Using the ItemData Property..

Code:

---

Private Sub Form_Load()
    Dim name As String

    Data1.RecordSource = "SELECT DISTINCT LastName, FirstName, ID FROM MemberInfo"
    Data1.Refresh
    While Not Data1.Recordset.EOF
        name = Data1.Recordset("LastName")
        name = name + ", " + Data1.Recordset("FirstName")
        List1.AddItem name
        List1.ItemData(List1.NewIndex) = Data1.Recordset("ID")
        Data1.Recordset.MoveNext
    Wend
End Sub

Private Sub List1_Click()
    If List1.ListIndex <0 Then Exit Sub
    Data1.RecordSource = "SELECT PicturePath FROM MemberInfo WHERE ID=" & List1.ItemData(List1.ListIndex)
    Data1.Refresh
    Image1 = LoadPicture(Data1.Recordset("PicturePath"))
End Sub

---

------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]

Method or Data Memeber Not Found

Image1.FileName = strPic - that's where I am getting the error? Any ideas?

GiD

Here's how I got it to work ( I think ) :) see any problems with this way guys?

Private Sub List1_Click()
Dim picpath As String
Data1.Recordset.MoveFirst
lblMemberName.Caption = List1.Text
Data1.Recordset.Move (List1.ListIndex)
picpath = Data1.Recordset("PicturePath")
Set Image1.Picture = LoadPicture(picpath)
End Sub

GiD

I thought you were putting both first and last names in the list box? Doesn't that screw up the match to the database?

Aaron's right, if when you grab the names from the database, dump the PicturePath into an array, then when they click on the list text, grab that array index value. Then youdon't have to go back to the database.
This will be faster then accessing the database twice. Plus less code

IE:

dim intCount as integer, PicArray() as string, intI as integer

data1.movelast
intCount = data1.recordcount
data1.movefirst
redim PicArray(intCount)
intI = 0
While Not Data1.Recordset.EOF
name = Data1.Recordset("LastName")
name = name + ", " + Data1.Recordset("FirstName")
intI = intI +1
PicArray(inti) = Data1.recordset(PicturePAth")
List1.AddItem nameData1.Recordset.MoveNext

then access the PicArray()

Private Sub List1_Click()
lblMemberName.Caption = List1.Text
Set Image1.Picture = LoadPicture(PicArray(List1.ListIndex+1))
End Sub


I know, I basically repeated Aaron's code :-) Wrote it before I read all of his post oops :-)

If I may:

How about giving pictures names according to Rec_No . So Rec_No: 12345 would have a picture file named and located: (C:\Pics\P12345). About the (P) It would be added in the saving/pulling procedures.
Data1.RecordSource = "SELECT DISTINCT LastName, FirstName, Rec_No FROM MemberInfo"
Set Image1.Picture = LoadPicture(C:\Pics\&"P"& ...Rec_No)
I think this way you wouldn't have to access the db again and no array and much faster.

G Luck.