-
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.
Code:
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.
-
A little note about it
Try this corrections:
Code:
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...;)
-
Thanks
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.
