opening tables

[kokopeli]

I have a database with two tables. One named Customers and the other named Locations. I need to open the Locations table to match a Customer name in the Customers table. I am putting the Customers info into text boxes and the loctions info into a listview. I can open both tables, but I can't figure out how to select only the locations for the customer in the text boxes. Here is my code. This is just a sample of the open procedures.

Private Sub LoadListView()
    Dim oRS As Recordset
    Dim strSQL As String
    Dim itmx As ListItem
    Dim colx As ColumnHeader
    Dim strName As String
    
    strName = txtCustName
    
    Set colx = ListView1.ColumnHeaders.Add(, , "Location")
    Set colx = ListView1.ColumnHeaders.Add(, , "Contact")
    Set colx = ListView1.ColumnHeaders.Add(, , "Cell Number")
    Set colx = ListView1.ColumnHeaders.Add(, , "Equipment Type")
    Set colx = ListView1.ColumnHeaders.Add(, , "Size")
    Set colx = ListView1.ColumnHeaders.Add(, , "Product")
    Set colx = ListView1.ColumnHeaders.Add(, , "Tag Number")
    Set colx = ListView1.ColumnHeaders.Add(, , "Zone")
    Set colx = ListView1.ColumnHeaders.Add(, , "Lease")
    
    strSQL = "SELECT  Customer, "
    strSQL = strSQL & "Location, "
    strSQL = strSQL & "Contact, "
    strSQL = strSQL & "CellNum, "
    strSQL = strSQL & "EquipType, "
    strSQL = strSQL & "Size, "
    strSQL = strSQL & "Product, "
    strSQL = strSQL & "TagNum, "
    strSQL = strSQL & "Zone, "
    strSQL = strSQL & "Lease "
    strSQL = strSQL & "FROM Locations "
    strSQL = strSQL & "WHERE Customer " = txtCustName

    Debug.Print strSQL

    Set oRS = New Recordset
    oRS.Open strSQL, goConn, _
            adOpenForwardOnly, adLockReadOnly
    Do While Not oRS.EOF
        Set itmx = ListView1.ListItems.Add(, , _
                (oRS("Location")))
                On Error Resume Next
                
            itmx.SubItems(1) = oRS("Contact")
            itmx.SubItems(2) = oRS("CellNum")
            itmx.SubItems(3) = oRS("EquipType")
            itmx.SubItems(4) = oRS("Size")
            itmx.SubItems(5) = oRS("Product")
            itmx.SubItems(6) = oRS("TagNum")
            itmx.SubItems(7) = oRS("Zone")
            itmx.SubItems(8) = oRS("Lease")
            
    ListView1.View = lvwReport

      oRS.MoveNext
   Loop
   oRS.Close
End Sub

Private Function FormFind() As Boolean
   Dim strSQL As String
   
   strSQL = "SELECT ID, "
   strSQL = strSQL & "CustName,  "
   strSQL = strSQL & "Address, "
   strSQL = strSQL & "City, "
   strSQL = strSQL & "WorkPhone, "
   strSQL = strSQL & "Notes "
   strSQL = strSQL & "FROM Customers "
   strSQL = strSQL & "WHERE ID = " & _
                     lstNames.ItemData(lstNames.ListIndex)

   If moRS.State = adStateOpen Then
      moRS.Close
   End If

   moRS.Open strSQL, goConn, _
             adOpenKeyset, _
             adLockPessimistic, _
             adCmdText
   
   If moRS.EOF Then
      FormFind = False
   Else
      FormFind = True
   End If
End Function

If I hard code in a valid customer name I get no value given for one or more required parameters, if I use txtCustName, I get a valid value there, but it returns as false.

[D12Bit]

Try this corrections:

Private Sub LoadListView()
    Dim oRS As Recordset
    Dim strSQL As String
    Dim itmx As ListItem
    Dim colx As ColumnHeader
    Dim strName As String
    
    strName = txtCustName
    
    Set colx = ListView1.ColumnHeaders.Add(, , "Location")
    Set colx = ListView1.ColumnHeaders.Add(, , "Contact")
    Set colx = ListView1.ColumnHeaders.Add(, , "Cell Number")
    Set colx = ListView1.ColumnHeaders.Add(, , "Equipment Type")
    Set colx = ListView1.ColumnHeaders.Add(, , "Size")
    Set colx = ListView1.ColumnHeaders.Add(, , "Product")
    Set colx = ListView1.ColumnHeaders.Add(, , "Tag Number")
    Set colx = ListView1.ColumnHeaders.Add(, , "Zone")
    Set colx = ListView1.ColumnHeaders.Add(, , "Lease")
    
    strSQL = "SELECT  Customer, "
    strSQL = strSQL & "Location, "
    strSQL = strSQL & "Contact, "
    strSQL = strSQL & "CellNum, "
    strSQL = strSQL & "EquipType, "
    strSQL = strSQL & "Size, "
    strSQL = strSQL & "Product, "
    strSQL = strSQL & "TagNum, "
    strSQL = strSQL & "Zone, "
    strSQL = strSQL & "Lease "
    strSQL = strSQL & "FROM Locations "
    strSQL = strSQL & "WHERE Customer = '" & txtCustName & "'"

    Debug.Print strSQL

    Set oRS = New Recordset
    oRS.Open strSQL, goConn, _
            adOpenForwardOnly, adLockReadOnly
    Do While Not oRS.EOF
        Set itmx = ListView1.ListItems.Add(, , _
                (oRS("Location")))
                On Error Resume Next
                
            itmx.SubItems(1) = oRS("Contact")
            itmx.SubItems(2) = oRS("CellNum")
            itmx.SubItems(3) = oRS("EquipType")
            itmx.SubItems(4) = oRS("Size")
            itmx.SubItems(5) = oRS("Product")
            itmx.SubItems(6) = oRS("TagNum")
            itmx.SubItems(7) = oRS("Zone")
            itmx.SubItems(8) = oRS("Lease")
            
    ListView1.View = lvwReport

      oRS.MoveNext
   Loop
   oRS.Close
End Sub

Private Function FormFind() As Boolean
   Dim strSQL As String
   
   strSQL = "SELECT ID, "
   strSQL = strSQL & "CustName,  "
   strSQL = strSQL & "Address, "
   strSQL = strSQL & "City, "
   strSQL = strSQL & "WorkPhone, "
   strSQL = strSQL & "Notes "
   strSQL = strSQL & "FROM Customers "
   strSQL = strSQL & "WHERE ID = '" & _
                     lstNames.ItemData(lstNames.ListIndex) & "'"

   If moRS.State = adStateOpen Then
      moRS.Close
   End If

   moRS.Open strSQL, goConn, _
             adOpenKeyset, _
             adLockPessimistic, _
             adCmdText
   
   If moRS.EOF Then
      FormFind = False
   Else
      FormFind = True
   End If
End Function

Please note that i just add in the -> WHERE Customer = 'Variable' <- the quotes at each side of the variable
and in the -> WHERE ID = 'Variable' <- The same quotes



Saludos...

[kokopeli]

That worked. Now I am going to have to split that up so I can reload the listview when I click to a different customer. The way I have it now, it just reloads the same info under the current info, plus it adds the same column headers on to the end. Again, thanks for the help.