I am creating a VB6 project that uses listview as my datagrid. So far I am successful in adding, editing, deleting and refreshing the table (I'm talking about the listview). My problem now is to do searching or filtering the records. This is my code on refreshing my table. I am thinking to use it as basis in doing the "filter records" event. I have added where clause on my sql statement but there is no record appearing in my listview table. Can anyone out there know how make it possible? - Dim list As ListItem
-
Dim strSQL As String
-
-
strSQL = "SELECT tbl_inventory.item_code,"
-
strSQL = strSQL & " tbl_inventory.PK,"
-
strSQL = strSQL & " tbl_inventory.item_name,"
-
strSQL = strSQL & " tbl_inventory.quantity,"
-
strSQL = strSQL & " tbl_inventory.unit_price,"
-
strSQL = strSQL & " tbl_inventory.status,"
-
strSQL = strSQL & " tbl_category.category_name,"
-
strSQL = strSQL & " tbl_supplier.supplier_name"
-
strSQL = strSQL & " FROM ((tbl_inventory INNER JOIN tbl_supplier"
-
strSQL = strSQL & " ON tbl_inventory.supplierFK=tbl_supplier.PKsup)"
-
strSQL = strSQL & " INNER JOIN tbl_category"
-
strSQL = strSQL & " ON tbl_inventory.categoryFK=tbl_category.PKcat)"
-
-
-
-
Set obj = New ADODB.Command
-
Set obj.ActiveConnection = con
-
-
obj.CommandText = strSQL
-
-
Set rs = obj.Execute
-
-
lvProd.ListItems.Clear
-
-
With rs
-
-
Do Until .EOF
-
Set list = lvProd.ListItems.Add(, , !item_code & "", , "Prods")
-
list.SubItems(item_name_idx) = !item_name & ""
-
list.SubItems(qty_idx) = !quantity & ""
-
list.SubItems(unit_price_idx) = !unit_price & ""
-
list.SubItems(status_idx) = !Status & ""
-
list.SubItems(supplier_idx) = !supplier_name & ""
-
list.SubItems(category_idx) = !category_name & ""
-
list.SubItems(PK_idx) = CStr(!PK)
-
.MoveNext
-
Loop
-
-
-
End With
-
-
With lvProd
-
If .ListItems.Count > 0 Then
-
Set .SelectedItem = .ListItems(1)
-
lvProd_ItemClick .SelectedItem
-
End If
-
End With
-
-
Set rs = Nothing
-
Set obj = Nothing
If you have difficulty analyzing my codes or my question please post your reply. I just want this project to be a success. Thanks!
11 9332
Not sure I'm understanding the question 100%, but it sounds as though you just got the WHERE clause wrong. Can you show it to us?
This code is actually attached in my cmdSearch button. I think this statement is right. My problem now is the code after my SQL statement. It is actually a running code but it displays no records. I also used this whole code to refresh the records in my listview table. It displays all the records in the recordset. No problem in displaying all the records. The problem is filtering the records. What I mean is, I want to search particular records based on the txtsearch textbox. Hope you understand. - Dim obj As ADODB.Command
-
Dim rs As Recordset
-
-
strSQL = "SELECT tbl_inventory.item_code,"
-
strSQL = strSQL & " tbl_inventory.PK,"
-
strSQL = strSQL & " tbl_inventory.item_name,"
-
strSQL = strSQL & " tbl_inventory.quantity,"
-
strSQL = strSQL & " tbl_inventory.unit_price,"
-
strSQL = strSQL & " tbl_inventory.status,"
-
strSQL = strSQL & " tbl_category.category_name,"
-
strSQL = strSQL & " tbl_supplier.supplier_name"
-
strSQL = strSQL & " FROM ((tbl_inventory INNER JOIN tbl_supplier"
-
strSQL = strSQL & " ON tbl_inventory.supplierFK=tbl_supplier.PKsup)"
-
strSQL = strSQL & " INNER JOIN tbl_category"
-
strSQL = strSQL & " ON tbl_inventory.categoryFK=tbl_category.PKcat)"
-
strSQL = strSQL & " WHERE tbl_inventory.item_name='" & txtSearch.Text & "'"
-
-
-
Set obj = New ADODB.Command
-
obj.ActiveConnection = con
-
-
obj.CommandText = strSQL
-
-
Set rs = obj.Execute
-
-
frmInventory.lvProd.ListItems.Clear
-
-
With rs
-
-
Do Until .EOF
-
Set list = frmInventory.lvProd.ListItems.Add(, , !item_code & "", , "Prods")
-
list.SubItems(item_name_idx) = !item_name & ""
-
list.SubItems(qty_idx) = !quantity & ""
-
list.SubItems(unit_price_idx) = !unit_price & ""
-
list.SubItems(status_idx) = !Status & ""
-
list.SubItems(supplier_idx) = !supplier_name & ""
-
list.SubItems(category_idx) = !category_name & ""
-
list.SubItems(PK_idx) = CStr(!PK)
-
.MoveNext
-
Loop
-
-
End With
Sorry, I'm still a little unclear. Are you saying that without the WHERE clause it shows all records, and with the WHERE clause it shows none?
Exactly.. but when i put
WHERE tbl_inventory.PK=2
it shows all the records that has an item code which is 2...
The datatype for PK is int..and the datatype for item_name is char.. is it because of the datatype??
Or does the following code after my SQL statement wrong??
Seems to me the WHERE clause is the crux of the matter. I haven't gone through the code in great detail yet, but you should be able to determine as soon as you execute the query whether it's returning any records or not. If it isn't, then you can ignore the rest of the code. If it is, but they're not being handled properly, then we can forget about the WHERE clause and look at the later code.
It's important to narrow down where the problem is.
Can you try out your query string in MS Access (or whatever your database) to make sure it works? It will probably be easier to debug there.
P.S. If your search term includes a wildcard (such as "*") then I don't believe it will match. You would probably need to use the Like operator in that case.
I am creating a VB6 project that uses listview as my datagrid. So far I am successful in adding, editing, deleting and refreshing the table (I'm talking about the listview). ...
If you mean searching using a Textbox and if you type a letter from the textbox your listview will give you automatic output. ex. if you type letter "a" from textbox then your query will execute and search all the words that have letter "a" in your database. Example - SELECT item_code FROM tbl_inventory WHERE item_code LIKE '%" & txtsearch.text & "%'
Hi, if you mean searching for specific words in your database, just use LIKE to filter the word you want. Ex. if you have a item_code 101 to 110, if you type the 101 item code then your listview will automatically generate the result.
CODE OF TEXTBOX: - SELECT item_code FROM Table1 WHERE item_code LIKE '%" & txtFilter.text & "%'
- 'Put your code here to view the record in your listview
-
Sorry for my English.
ok guys! tnx..
i am now analyzing my project.. if there is still a problem then i will post another question here.. you guys really are helpfuL!!
thanks!
Let us know how it turns out, in any case.
I have a textbox called txtretrieve that the user enter the name of an item to retrieve its code. I want to make that when the user enter the first letter the program searcher for all names begining with the letter and fills a msflexgrid with the result. As more letters are entered for the item name then less results are displayed in the msflexgrid. my code is as below:
Private Sub txtRetrieve_Change()
MSFlexGrid1.Clear
Dim this As String
this = Val(txtRetrieve.Text)
R1.Open "SELECT BARCODE,DESCRIPTION,QUANTITY FROM ITEM_MASTER_TBL where DESCRIPTION like ' " & txtRetrieve.Text & " %'", Cn, adOpenDynamic, adLockBatchOptimistic
Call LOADHEADER
Me.MSFlexGrid1.Rows = 2
Me.MSFlexGrid1.Row = 1
'Me.MSFlexGrid1.Rows = R1.RecordCount
While Not R1.EOF
Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Row, 0) = Me.MSFlexGrid1.Row
Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Row, 1) = R1.Fields(0)
Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Row, 2) = R1.Fields(1)
Me.MSFlexGrid1.TextMatrix(Me.MSFlexGrid1.Row, 3) = R1.Fields(2)
If Me.MSFlexGrid1.Row = Me.MSFlexGrid1.Rows - 1 Then
Else
MSFlexGrid1.Row = MSFlexGrid1.Row + 1
End If
R1.MoveNext
Wend
R1.Close
End Sub
Any help will be much appreciated. Consider a quick response please.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: jdph40 |
last post by:
I posted this problem previously and received excellent help from
Wayne Morgan. However, I still have an unanswered question. My form
(frmVacationWeeks) is opened from the OnClick event of a...
|
by: Nhmiller |
last post by:
This is directly from Access' Help:
"About designing a query
When you open a query in Design view, or open a form, report, or datasheet and
show the Advanced Filter/Sort window (Advanced...
|
by: Nhmiller |
last post by:
I already have a report designed under Reports. When I use filtering in Forms
or Tables, I see no way to select that filtered list when I am in Reports, and
there appears to be no way to do the...
|
by: swilson513 |
last post by:
In Access97 I was able to have an advance filter on a form that had a
Like statement so when you applied the filter EACH time it would asked
for the criteria. In 2000 the same filter doesn't asked...
|
by: dhowell |
last post by:
In reading some of the posts on this group, it appears as though it is
not strait forward at all to filter a form, which has subforms, by
criteria which are either on subforms or span more than one...
|
by: dee |
last post by:
I'd like to filter by the following criteria:
left(LeadDisposition,3) = "Sit" AND Appt_Date = Text767
I have no idea how to do this. Appreciate help.
|
by: Katherine |
last post by:
I'm trying to filter the records on the mainform (MailingList) of my
database using a field contained in a subform (Donations). I was
basing my code off Allen Browne's Access Tips page (here:...
|
by: franc sutherland |
last post by:
Hello,
I have a report which I filter using the me.filter command in the
OnOpen event.
Me.Filter = "OrderID=" & Forms!variable_form_name!
Me.FilterOn = True
I want to be able to open that...
|
by: maxamis4 |
last post by:
Hello folks,
I am still moving into the world of sql server and I need some help. In oracle there is a statement that is called "in list". Basically means what ever is returned from a select...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| |