[RESOLVED] maybe im asking the wrong questions ...

Hey everyone, I have posted a couple of threads on how to use a DBList or a ListView control to do what i want, but i havent quite got it figured out yet, so let me try a more generalized question:

I have a DB with a whole slew of information for each record. My goal is to have a search function to query the DB and display all results matching that search. (this part i have working fine).

After displaying all records that match that criteria if i were to click on one of the listed results i want to have it open a new form with all the fields from that record displayed into text boxes on that form.

My issue is that i need a few fields to display in the list of records that matched the search, to differentiate between the 2 "John Doe" records that it pulls up from a search of people in Florida.

I cant use a DBList for this becuase i can only show one field, so if i used F_Name i would see 2 listings for "John" with no other info to help me select which one i need. But i can get it to populate the fields from that record into the textboxes on the next form once i select that record from the list.

So i have been working with ListView cause i can display as many fields as i can fit from that record in the actual list so that i can see that "John Doe" (1) is in tampa, and "john doe"(2) is in Miami. But i cant figure out how to make the listview control figure out that i clicked on a record and then go back to that record to get the info i need.

Maybe im going about this the wrong way and there is another control that will give me the best of both worlds, if not; im at a complete loss here. Can some one please suggest to me a way that i can get this done?

Any ideas or comments will help me out a great deal at this point !! Thanks everyone !

--Also, i am using VB6 for this project.

Most people dont use bound controls like the DBList (for some of the reasons see here), which is probably why some of your questions have had little response.

The ListView is a fairly well used control, so I'm suprised you didn't get much response. Can you be more precise about your problem with it?

A common alternative to the ListView is the MS FlexGrid, which is a fairly simple (but very adaptable) grid. For an example of filling it from a database, see the link in my signature.

Thanks for the reply, i am looking into the links right now.

but to be more specific i have populated that ListView as Such:

VB Code:

ListView1.View = lvwReport
Dim strQueryString As String
Dim varBookmark As Variant
Dim i As Integer
 
With ListView1.ColumnHeaders
    .Add , , "FName"
    .Add , , "LName"
    .Add , , "Address"
End With
 
varBookmark = datFind.Recordset.Bookmark
strQueryString = ("[State]= '" & Text7.Text & "'")
 
datFind.Recordset.FindFirst strQueryString
i = 0
Do Until datFind.Recordset.NoMatch
    i = i + 1
    ListView1.ListItems.Add , , datFind.Recordset("FirstName")
    ListView1.ListItems(i).SubItems(1) = datFind.Recordset("LastName")
    ListView1.ListItems(i).SubItems(2) = datFind.Recordset("Address1")
    datFind.Recordset.FindNext strQueryString
Loop
ListView1.FullRowSelect = True

now i just need to figure out how to make a click function so that if i were to select one from the list that it would go back to that record to get the rest of the information so that i can populate my textboxes using something like:

VB Code:

Form1.txtAddress.Text = datFind.Recordset("Address1")
Form1.txtCity.Text = datFind.Recordset("City")
Form1.txtState.Text = datFind.Recordset("State")
Form1.txtZip.Text = datFind.Recordset("zipcode")

but, i figure, i cant get to that record set because the ListView isnt bound to that data control so i cant figure out how to get back to a specific record once i click that name on the list.

What you need to do is have some sort of unique ID for each record, perhaps as an AutoNumber or Identity field, and store this value in the control for each record (I dont know the listview well enough, but for the FlexGrid you can use .RowData).

You can then open a recordset based on this value, using a simple SQL statement.

Si_The_Geek,

I hate to be such a noob, but i need more clarification, i am failry new to VB, the last time i touched this stuff was in college, but i thought i had a grasp on what was going on... apparently i was incorrect.

Wheather i go with the MS Flexgrid or find a way to stick with the ListView, can you explain to me how i would call that autonumber associated with that record ? Like, even if i were to add an Autonumber field to the DB how would i go about saying that X is the identifier for the first listed item, and Y is the identifier for the 8th listed item; so that i can call on that for the on click event?

once i get that down i would assume it is as easy as saying

VB Code:

strSQL = "SELECT * FROM table WHERE AutoNumber = X"
datFind.RecordSource = strSQL
datFind.Refresh
Form1.txtAddress.Text = datFind.Recordset("Address1")

so if i could get just a bit more information on how to store/call that autonumber identifier i think i would be ALL SET !

To link the ListView to a specific record, store the record's Bookmark in the Tag property of the ListItem.

VB Code:

Dim oItem as ListItem
 
Do Until datFind.Recordset.NoMatch
    i = i + 1
    Set oItem =   ListView1.ListItems.Add( , , datFind.Recordset("FirstName"))
    oItem.Tag = datFind.Recordset.Bookmark
    '... rest of loop

Quote:

---

now i just need to figure out how to make a click function

---

Use the ListView ItemClick event.

VB Code:

Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
     'make the current record of the recordset the same as the listview
     datFind.Recordset.Bookmark = Item.Tag
End Sub

We were all new once, the important thing is to keep learning.. so asking questions is good. ;)


Just add another field to the table (call it "ID" or "PersonID" or something similar), and then set the data type to be Autonumber (for Access databases, in others the method is different).

The database will then automatically add values to the field (you never have to enter them), and the values will be unique numbers.

When you fill your control (ListView/FlexGrid/...) with data, simply add the ID/PersonID field to it along with the other data (or store it in a hidden property for each item, like the grids .RowData).

When a selection is made, you load the matching data using an SQL statement like you posted, but obviously getting the 'X' value from where you stored it.


edit: bruce's Bookmark idea is a decent alternative, but that assumes the recordset is still available, and has not been re-loaded (or similar). If you choose to use my method, you can see how to store data per ListView item in his post!

Ok thanks a lot for the info, I think that my issue is with how, or where to store this autonumber field. I have the column created in Access, and the numbers are working, but how do i store that record when i dont know which list item the user will select? What i really dont understand how to do (and ive looked all over to find some kind of direction) is how to have the user select one from the list and have the control figure out which record that List Item is referencing.

You need to store the value of the field for all records in the control, then you can simply read from whichever item they have selected.

Assuming that you are using a ListView, you can use this code to store the value when you are adding the other data:

VB Code:

ListView1.ListItems(i).Tag = datFind.Recordset("PersonID")

..then in the ListView1_ItemClick event, you can load the data as in your example above. You would build the SQL like this:

VB Code:

strSQL = "SELECT * FROM table WHERE PersonID = " & Item.Tag

If you were using a FlexGrid, you would use .RowData in the same same sort of way as .Tag has been used here.

PERFECT !! sorry for needing the hand holding but i get it now ! thank you so much for your help ! so the " Item.Tag " will automatically be the selected item from the list ?

Thanks again for all your help ... once i get this figured out, i get the wonderful task of trying to make it all work with MySQL, instead of Access. Ive been looking for info here, but it doesnt seem like many people want to use MySQL.

The ItemClick event of the listview is defined like this:

VB Code:

Private Sub ListView1_ItemClick(ByVal [b]Item[/b] As MSComctlLib.ListItem)

..which means that the Item variable is the ListItem that was clicked on, so yes Item.Tag will relate to the clicked (or 'selected') item.

As far as moving to MySQL is concerned, as long as the database structure is the same as your Access database, the code is virtually identical - all you need to change is the connection string. You also need to install some extra software (drivers or something similar) to each computer which will run your program.

Thanks a lot for the reply. The Item.Tag worked like a charm. I was looking around the forum for a way to adjust how the ListView sorts automatically, but all i could find was column_click commands. Is there a way that i can tell the ListView to sort them by a specific column before anything is even put on this list?

My goal here is to have the List show all records in reverse chronolgical order, so that the most recent entry will be on top.

Maybe you just know of a thread off-hand that you could direct me to ?

I don't know the answer to your actual question, but do I know a better answer - simply load the data in the order you want. This will be quicker and easier (for you, and for the program itself).

If your RecordSource is "Select * TableName" (or just "TableName"), change it to "Select * TableName ORDER BY fieldname DESC".

Si The Geek ... i know im a royal pain in your neck but im having one hell of a time here..

this is what i just tried based on your recommendation

VB Code:

strSQL = "SELECT * FROM CustomerNotes ORDER BY CreateDateTime DESC WHERE PhoneNumber LIKE " & strAposAst & strSearch & strAstApos

from this original

VB Code:

strSQL = "SELECT * FROM CustomerNotes WHERE PhoneNumber LIKE " & strAposAst & strSearch & strAstApos

and im getting a "syntax error in ORDER BY clause" .. am i not quote doing it correctly ?

You're not a pain at all, you're the kind of person we like - you are trying to learn, and have a go at getting things working yourself. :thumb:

Even tho the message isn't clear about it, the problem is simply that the Order By needs to be the last part of the SQL, eg:

VB Code:

strSQL = "SELECT * FROM CustomerNotes WHERE PhoneNumber LIKE " & strAposAst & strSearch & strAstApos & " ORDER BY CreateDateTime DESC"

Thank you sooo much ! that worked like a charm. If it is even possible that i would have figured that out with out your help, it would have taken me a year !! Im going to close this thread out, so that if i have any other questions i can start a new thread with that title !!

thanks again !!!!!