Datacontrol & List Boxes

[PcLinuxGuru]

I have trying to pull data from a database, list it in a list box, and when a user clicks on an entry in the listbox the data populates the fields I have setup.

Now this work with no problems if there are no duplicate entries but I have several duplicate entries for the list box. I am not sure how to code in to get the data I actually need. This is what I have that actually works. I am use a DataControl BTW.

I have 2 fields an ID & Amount in my access db. User types in Acct. Number to search for and when they click search it populates the list box with Amounts (I did not format it for currency yet). Which works. The list box get filled correctly. The problem is in selecting a record from the list box. If I have 3 or more 4.47 then it won't show the correct record in my text fields.

Private Sub cmdSearch_Click()
    
    Dim strQueryString As String
        
    strQueryString = "ID =" & ID.Text

    lstData.Clear ' Clear the Accounts list box
    
    datData.Recordset.FindFirst strQueryString
    
    Do Until datData.Recordset.NoMatch
        
        
        lstData.AddItem datData.Recordset("Amount")
        datData.Recordset.FindNext (strQueryString)
    Loop

End Sub


Private Sub lstData_Click()

' When a selection in the list is seelcted I want to fill in the fields so I can see it.
datAcctNum.Recordset.FindFirst "Amount = " & lstAccounts.List(lstAccounts.ListIndex) & ""

End Sub

I am not sure how to go about getting the results I need.

 

[JAG14]

Try This...

Private Sub lstData_Click()

' When a selection in the list is selected I want to fill in the fields so I can see it.
datAcctNum.Recordset.FindFirst "Amount = " & Trim(lstAccounts.Text)

End Sub

Hope this helps..

[tt]'Very funny, Scotty... Now Beam down my clothes.'[/tt]
​

 

[AndyLee100]

I can see you are going to have many problems doing it the way you have. I would suggest a re-think. What happens if you have 10 amounts in the list box, all with the same number. How is the user going to know which is the correct one.

I would suggest adding more information than just the ID field to the listbox so the user knows which record to select and do a search on that field. At the moment you are returning for example 3 rows in the recordset and the find method is finding the first one, which ever one that maybe and you will never know this.

Andy

 

[PcLinuxGuru]

Yes. I have tried adding more info.

I believe it was something like.

lstaccounts.additem datdata.recordset("ID") & datdata.recordset("Amount")

But when it is clicked on I get errors because it would show the data like

6 7.70

If I could split it to so I can select the record I can go by the unique ID (primary key) for record selection.

The problem is the findfirst or findnext with the same amounts and yes I ran into that already

 

[BobRodes]

Best thing to do is store the keys in the itemdata property. Searching for itemdata yields a number of threads: thread222-897290 looks good for more info. Note that you can only store integers in this property. If your key field has alpha characters, you'll need to associate an array with the listbox instead. Just match the listindex property with the offsets of the array. The thread I mentioned covers both techniques.

HTH

Bob