Results 1 to 16 of 16

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

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Aug 2006
    Posts
    85

    Resolved [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.
    Last edited by Red_Dragon_X; Sep 6th, 2006 at 02:17 PM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: maybe im asking the wrong questions ...

    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.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Aug 2006
    Posts
    85

    Re: maybe im asking the wrong questions ...

    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:
    1. ListView1.View = lvwReport
    2. Dim strQueryString As String
    3. Dim varBookmark As Variant
    4. Dim i As Integer
    5.  
    6. With ListView1.ColumnHeaders
    7.     .Add , , "FName"
    8.     .Add , , "LName"
    9.     .Add , , "Address"
    10. End With
    11.  
    12. varBookmark = datFind.Recordset.Bookmark
    13. strQueryString = ("[State]= '" & Text7.Text & "'")
    14.  
    15. datFind.Recordset.FindFirst strQueryString
    16. i = 0
    17. Do Until datFind.Recordset.NoMatch
    18.     i = i + 1
    19.     ListView1.ListItems.Add , , datFind.Recordset("FirstName")
    20.     ListView1.ListItems(i).SubItems(1) = datFind.Recordset("LastName")
    21.     ListView1.ListItems(i).SubItems(2) = datFind.Recordset("Address1")
    22.     datFind.Recordset.FindNext strQueryString
    23. Loop
    24. 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:
    1. Form1.txtAddress.Text = datFind.Recordset("Address1")
    2. Form1.txtCity.Text = datFind.Recordset("City")
    3. Form1.txtState.Text = datFind.Recordset("State")
    4. 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.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: maybe im asking the wrong questions ...

    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.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Aug 2006
    Posts
    85

    Re: maybe im asking the wrong questions ...

    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:
    1. strSQL = "SELECT * FROM table WHERE AutoNumber = X"
    2. datFind.RecordSource = strSQL
    3. datFind.Refresh
    4. 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 !

  6. #6
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: maybe im asking the wrong questions ...

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

    VB Code:
    1. Dim oItem as ListItem
    2.  
    3. Do Until datFind.Recordset.NoMatch
    4.     i = i + 1
    5.     Set oItem =   ListView1.ListItems.Add( , , datFind.Recordset("FirstName"))
    6.     oItem.Tag = datFind.Recordset.Bookmark
    7.     '... rest of loop

    now i just need to figure out how to make a click function
    Use the ListView ItemClick event.

    VB Code:
    1. Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
    2.      'make the current record of the recordset the same as the listview
    3.      datFind.Recordset.Bookmark = Item.Tag
    4. End Sub

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: maybe im asking the wrong questions ...

    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!

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Aug 2006
    Posts
    85

    Re: maybe im asking the wrong questions ...

    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.

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: maybe im asking the wrong questions ...

    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:
    1. 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:
    1. 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.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Aug 2006
    Posts
    85

    Re: maybe im asking the wrong questions ...

    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.

  11. #11
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: maybe im asking the wrong questions ...

    The ItemClick event of the listview is defined like this:
    VB Code:
    1. 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.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Aug 2006
    Posts
    85

    Re: maybe im asking the wrong questions ...

    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 ?

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: maybe im asking the wrong questions ...

    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".

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Aug 2006
    Posts
    85

    Re: maybe im asking the wrong questions ...

    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:
    1. strSQL = "SELECT * FROM CustomerNotes ORDER BY CreateDateTime DESC WHERE PhoneNumber LIKE " & strAposAst & strSearch & strAstApos

    from this original

    VB Code:
    1. 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 ?

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: maybe im asking the wrong questions ...

    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.

    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:
    1. strSQL = "SELECT * FROM CustomerNotes WHERE PhoneNumber LIKE " & strAposAst & strSearch & strAstApos & " ORDER BY CreateDateTime DESC"

  16. #16

    Thread Starter
    Lively Member
    Join Date
    Aug 2006
    Posts
    85

    Re: maybe im asking the wrong questions ...

    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 !!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width