Query to Listbox to UDT Question

Hello everyone,

I'm a ColdFusion programmer whose been tasked with creating a VB6 application for the marketing department of my company. It's a simple application, and here's basically how it works:

You search for a product from a textbox. The results of the search populate a combo box. When you select something from the combo box, it populates a series of text fields with information about the product (title, format, catalog number, price, etc...). From there you can click an "add to shopping cart" button, which stores that product into a shopping cart database table. Make sense so far?

Okay, so I have the search working. What I can't get is populating the combo box and subsequent text fields once you select something in the combo box. After reading a few of the books we have lying around, I figured it would be best to create a UDT to store the information. So I did that:

VB Code:

Private Type ProductUDT
    the_title As String
    Title As String
    Format As String
    Price As String
    ID As String
    SKU As String
End Type
Dim Products() As ProductUDT, i As Long

Then I run the query:

VB Code:

Dim SQL_Search As ADODB.Recordset
    Set SQL_Search = New ADODB.Recordset
 
    Dim searchtext As String
    searchtext = txt_search.Text
 
    SQL_Search.Source = "SELECT (title & '('&Format&')') As the_title, title, ID, format, sku, price FROM lkpProduct WHERE (title LIKE '%" & searchtext & "%') OR (sku LIKE '%" & searchtext & "%') ORDER BY title"
    Set SQL_Search.ActiveConnection = db
    SQL_Search.Open

Then I have the code to populate the combo box (but it doesn't work yet):

VB Code:

For i = LBound(Products) To UBound(Products)
        combo_search_results.AddItem Products(i).Title
        combo_search_results.ItemData(combo_search_results.NewIndex) = i
    Next

Based on what the book says, there needs to be code to tie together the query results to the "Products" UDT that is created. Unforunately, the author purposely omitted the code to do that.

If someone can please help me to figure out (a) how to tie the query to the UDT; and then (b) how to tie the query information from the selected item in the combo box to the text fields.

MUCH APPRECIATED! The bosses will be pleased if I can get this working.

Josh Sager
www.tlavideo.com

Why the UDT? You already have the data you need in the recordset. Just populate the combo box from the recordset.

VB Code:

Set SQL_Search.ActiveConnection = db
SQL_Search.Open
 
Do
  combo_search_results.AddItem SQL_Search.Fields("Title").Value
 
  'Note, you cannot assign a value to NewIndex.  It is a read only property.  
  'Also ItemData can only be numeric.  If Id is character data this won't work
  combo_search_results.ItemData(combo_search_results.NewIndex) = SQL_Search.Fields("Id").Value
    
    SQL_Search.MoveNext
Loop Until SQL_Search.EOF

If you insist on using the UDT - again loop through the recordset

VB Code:

Dim Products() As ProductUDT, i As Long
 
'Make sure RecordCount is valid before using this statement
'depending on how you open the recordset it may return -1
Redim Products(SQL_Search.RecordCount - 1)
 
i = 0
Do
   'only use this if RecordCount is invalid.
   Redim Preserve Products(i)
 
    Products(i).Title = SQL_Search.Fields("Title").Value 
    Products(i).Id = SQL_Search.Fields("Id").Value 
    Products(i)...
    SQL_Search.MoveNext
    i = i + 1
Loop Until SQL_Search.EOF

I got the population of the combo box working; thanks. Now I need to figure out how to populate the text fields once the user selects an item from the combo box.

Is there a way to do this without having to run a second query, since I already have the information I need from the first query? In this particular case, "ID" is numeric, but since you're saying that I can't assign a value to NewIndex, how would I go about correcting this and doing what I need to.

Thanks so much for your help.

Josh


To be clear as to where I am in the game now, I've attached the code I'm working on. The top is the part of the function that runs the query. The second sub is the routine that will populate the text labels/fields when an item is clicked in the combo box.

VB Code:

' Disable the submit button so it's not clicked multiple times
    cmd_search.Enabled = False
 
    ' Search for a title by text or sku when entered in the search text field
    Dim SQL_Search As ADODB.Recordset
    Set SQL_Search = New ADODB.Recordset
 
    Dim searchtext As String
    searchtext = txt_search.Text
 
    SQL_Search.Source = "SELECT (title & '('&Format&')') As the_title, title, ID, format, sku, price FROM lkpProduct
WHERE (title LIKE '%" & searchtext & "%') OR (sku LIKE '%" & searchtext & "%') ORDER BY title"
    Set SQL_Search.ActiveConnection = db
    SQL_Search.Open
 
    With SQL_Search
        ' Loop through the results and populate the combo_search_results box
        Do While Not .EOF
            ' Populate the combo box with the query results
            combo_search_results.AddItem SQL_Search.Fields!the_title.Value
            .MoveNext
        Loop
        .Close
    End With
    
    ' When the query has finished, re-enable the submit button
    cmd_search.Enabled = True
    
End Sub
 
 
 
Private Sub combo_search_results_Click()
    label_selected_product_title.Caption = combo_search_results.Text
    ' label_price_amt.Caption = SQL_Search.
End Sub

I mis-read your initial post and for some reason thought you were trying to do this

combo_search_results.NewIndex = i

Anyways, since you do not want to do another query, you would need to keep your recordset open. Give the SQL_Search recordset greater scope by declaring it private to the Form.

Where do you close the connection? Do you keep it active during the entire program or do you close it after getting a recordset?

If you close it, use a disconnected recordset (ie the recordset is still open after the database connection has been closed).

VB Code:

SQL_Search.Source = "SELECT (title & '('&Format&')') As the_title, title, ID, format, sku, price FROM lkpProduct
WHERE (title LIKE '%" & searchtext & "%') OR (sku LIKE '%" & searchtext & "%') ORDER BY title"
    Set SQL_Search.ActiveConnection = db
 
    [b]SQL_Search.CursorLocation = adUseClient
    SQL_Search.Open ,, adOpenStatic, adLockReadOnly, adCmdText
    Set SQL_Search.ActiveConnection  = Nothing[/b]
 
    .MoveNext
 
'to access the recordset row when the user selects a combobox item use the AbsolutePosition property
SQL_Search.AbsolutePosition = combo_search_results.ListIndex + 1
 
Once the row is set just populate your text boxes.
 
Text1.Text = SQL_Search.Fields("Id").Value

Alternatively, you could call the Recordset Filter or Find methods when the user selects a combobox item.