[RESOLVED] Advanced Search on MSFlexGrid vb6
Hello sirs
I wants Advanced Search functions on the MMSFlexGrid in vb6.
Currently iam using a combobox to select which Subject i want to use to search.
I want advanced search function.
It should be possible to select which settings i want then only what i want should be listed on the MMSFlexGrid.
currently i am using the folowwing code to search and load the database.
Code:
Private Sub cmdFind_Click()
makeGrid (cmbMode.Text)
If MSFlexGrid1.TextMatrix(1, 0) = "" Then
MsgBoxXP "Sorry !!Could not find carpart.", vbCritical, "Error!"
End If
txtSearch.SetFocusEx
End Sub
Code:
Private Sub makeGrid(qryField As String)
On Error Resume Next
If rs.State = 1 Then
rs.Close
End If
Select Case qryField
Case "Nr":
strQry = "SELECT * FROM AddressBook ORDER BY ID"
Case "Fabrikat":
strQry = "SELECT * FROM AddressBook WHERE Fabrikat LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Fabrikat"
Case "Model":
strQry = "SELECT * FROM AddressBook WHERE Model LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Model"
Case "Variant":
strQry = "SELECT * FROM AddressBook WHERE Variant LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Variant"
Case "Årgang":
strQry = "SELECT * FROM AddressBook WHERE Årgang '%" & Trim(txtSearch.Text) & "%' ORDER BY Årgang"
Case "Motor":
strQry = "SELECT * FROM AddressBook WHERE Motor LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Motor"
Case "Gear":
strQry = "SELECT * FROM AddressBook WHERE Gear LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Gear"
Case "Karosseri":
strQry = "SELECT * FROM AddressBook WHERE Karosseri LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Karosseri"
Case "Side":
strQry = "SELECT * FROM AddressBook WHERE Side LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Side"
Case "Km":
strQry = "SELECT * FROM AddressBook WHERE Km LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Km"
Case "Farve":
strQry = "SELECT * FROM AddressBook WHERE Farve LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Farve"
Case "Pris":
strQry = "SELECT * FROM AddressBook WHERE Pris LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Pris"
Case "Antal":
strQry = "SELECT * FROM AddressBook WHERE Antal LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY Antal"
End Select
rs.Open strQry, conn, adOpenKeyset, adLockReadOnly
If rs.RecordCount < 1 Then
MSFlexGrid1.Clear
MSFlexGrid1.Rows = 2
gridHeader
Else
rs.MoveFirst
MSFlexGrid1.Rows = rs.RecordCount + 1
rowcnt = 1
For I = 1 To rs.RecordCount
MSFlexGrid1.Row = rowcnt
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = "" & rs.Fields("ID")
MSFlexGrid1.Col = 1
MSFlexGrid1.RowHeight(rowcnt) = 350
MSFlexGrid1.Text = "" & rs.Fields("Fabrikat")
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = "" & rs.Fields("Model")
MSFlexGrid1.Col = 3
MSFlexGrid1.Text = "" & rs.Fields("Variant")
MSFlexGrid1.Col = 4
MSFlexGrid1.Text = "" & rs.Fields("Årgang")
MSFlexGrid1.Col = 5
MSFlexGrid1.Text = "" & rs.Fields("Motor")
MSFlexGrid1.Col = 6
MSFlexGrid1.Text = "" & rs.Fields("Gear")
MSFlexGrid1.Col = 7
MSFlexGrid1.Text = "" & rs.Fields("Karosseri")
MSFlexGrid1.Col = 8
MSFlexGrid1.Text = "" & rs.Fields("Side")
MSFlexGrid1.Col = 9
MSFlexGrid1.Text = "" & rs.Fields("Km")
MSFlexGrid1.Col = 10
MSFlexGrid1.Text = "" & rs.Fields("Farve")
MSFlexGrid1.Col = 11
MSFlexGrid1.Text = Format(rs.Fields("Pris"), "##0.00")
MSFlexGrid1.Col = 12
MSFlexGrid1.Text = "" & rs.Fields("Antal")
MSFlexGrid1.Col = 13
MSFlexGrid1.Text = "" & rs.Fields("Title")
MSFlexGrid1.Col = 14
MSFlexGrid1.Text = "" & rs.Fields("Note")
rs.MoveNext
rowcnt = rowcnt + 1
Next I
End If
Code:
Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long 'MOUSE WHEEL SCROLL
Dim X As Integer 'MOUSE WHEEL SCROLL
Dim topRow As Integer 'MOUSE WHEEL SCROLL
Dim ctl As Control 'MOUSE WHEEL SCROLL
Dim lngResult As Long 'MOUSE WHEEL SCROLL
Dim rs As New ADODB.Recordset
Dim FrameMain As String
Dim FrameNew As String
Dim framePrint As String
Private Sub Main()
dbConn
rs.Open "select * from ports", conn, adOpenKeyset, adLockOptimistic
rs.MoveFirst
portNo = rs.Fields(0)
If portNo < 1 Then
' frmSetPort.Show 1
End If
rs.Close
Set rs = Nothing
frmMAIN.Show
End Sub
Re: Advanced Search on MSFlexGrid vb6
So, what is your question? :confused:
Anyway, in the meantime I would simplify your makeGrid procedure by doing something like this:
Code:
Private Sub makeGrid(qryField As String)
Select Case qryField
Case "Nr":
strQry = "SELECT * FROM AddressBook ORDER BY ID"
Case Else
strQry = "SELECT * FROM AddressBook WHERE " & qryField & " LIKE '%" & Trim(txtSearch.Text) & "%' ORDER BY " & qryField
End Select
Set rs = Notihng
Set rs = New ADBDB.Recordset
rs.Open strQry, conn, adOpenKeyset, adLockReadOnly
If Not rs.EOF Then
MSFlexGrid1.Rows = 1
MSFlexGrid1.Cols = rs.Fields.Count
For i = 0 To rs.Fields.Count - 1
MSFlexGrid1.TextMatrix(0, i) = rs.Fields(i).Name
Next i
Do While Not rs.EOF
MSFlexGrid1.AddItem ""
For i = 0 To rs.Fields.Count - 1
MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, i) = "" & rs.Fields(i).Value
Next i
rs.MoveNext
Loop
MSFlexGrid1.FixedCols = 0
MSFlexGrid1.FixedRows = 1
End If
End Sub
Note: code above was not tested.
Re: Advanced Search on MSFlexGrid vb6
Hello thank you.
okay look at that eg.
i want to search for a Bmw, 2.0 and from year 2001.
Now i can only search for one thing at time, eg i can search on bmw, but i will then get all other bmw cars, also bmws from 1999(example).
so i want to filter the search by entering eg bmw, then year(2001) and engine 2.0)like this..
i hope it was clear know.
Re: Advanced Search on MSFlexGrid vb6
Quote:
Originally Posted by
bibob2011
...so i want to filter the search by entering eg bmw, then year(2001) and engine 2.0)like this...
Offer more filters on screen and build sql dynamically based on whether or not particular filter (or texbox, combo, etc) value is provided.
You will have base query (i.e, select * from table1 where ...) to begin with.
You will append each condition for each available filter.
Here mis a very quick sample:
Code:
Private Sub makeGrid(Optional f1 As String, Optional f2 As String, Optional f3 As String)
Dim strsql As String
strsql = "select * from table1 where 1=1"
If Not f1 = "" Then
strsql = strsql & " and field1 = '" & f1 & "',"
End If
If Not f2 = "" Then
strsql = strsql & " and field2 = '" & f2 & "',"
End If
If Not f3 = "" Then
strsql = strsql & " and field3 = '" & f3 & "'"
End If
If Right(strsql, 1) = "," Then strsql = VBA.Left$(strsql, Len(strsql) - 1)
'...
End Sub
Note: f1, f2, f3 are optional values for each field you wish to search (name them accordingly so you don't get confused).
Re: Advanced Search on MSFlexGrid vb6
oh thank you very much, but i am really not good to program in Databases, please explaing more, please try to use my code and give me just some examples with my code
Re: Advanced Search on MSFlexGrid vb6
Well, I suppose you can start learning by reading ADO
In the meantime try implementing something similar to following sample:
Code:
Private Sub makeGrid(Optional sFabrikat As String, _
Optional sModel As String, _
Optional sVariant As String, _
Optional sArgang As String, _
Optional sMotor As String, _
Optional sGear As String, _
Optional sKarosseri As String, _
Optional sSide As String, _
Optional sFarve As String, _
Optional sPris As String, _
Optional sAntal As String)
'======================================================Dim strQry As String
strQry = "SELECT * FROM AddressBook Where 1=1"
If Not sFabrikat = "" Then
strQry = strQry & " And Fabrikat = '" & sFabrikat & "',"
End If
If Not sModel = "" Then
strQry = strQry & " And Model = '" & sModel & "',"
End If
If Not sVariant = "" Then
strQry = strQry & " And Variant = '" & sVariant & "',"
End If
If Not sArgang = "" Then
strQry = strQry & " And Argang = '" & sArgang & "',"
End If
If Not sMotor = "" Then
strQry = strQry & " And Motor = '" & sMotor & "',"
End If
If Not sGear = "" Then
strQry = strQry & " And Gear = '" & sGear & "',"
End If
If Not sKarosseri = "" Then
strQry = strQry & " And Karosseri = '" & sKarosseri & "',"
End If
If Not sSide = "" Then
strQry = strQry & " And Side = '" & sSide & "',"
End If
If Not sFarve = "" Then
strQry = strQry & " And Farve = '" & sFarve & "',"
End If
If Not sPris = "" Then
strQry = strQry & " And Pris = '" & sPris & "',"
End If
If Not sAntal = "" Then
strQry = strQry & " And Antal = '" & sAntal & "'"
End If
'you may need to decide which field to use for the Order By clause;
'perhaps it could be option on screen for users to select (checkbox, option button, dropdown, etc...)
If Right(strQry, 1) = "," Then strQry = VBA.Left$(strQry, Len(strQry) - 1)
Set rs = Nothing
Set rs = New ADBDB.Recordset
rs.Open strQry, conn, adOpenKeyset, adLockReadOnly
If Not rs.EOF Then
MSFlexGrid1.Rows = 1
MSFlexGrid1.Cols = rs.Fields.Count
For i = 0 To rs.Fields.Count - 1
MSFlexGrid1.TextMatrix(0, i) = rs.Fields(i).Name
Next i
Do While Not rs.EOF
MSFlexGrid1.AddItem ""
For i = 0 To rs.Fields.Count - 1
MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, i) = "" & rs.Fields(i).Value
Next i
rs.MoveNext
Loop
MSFlexGrid1.FixedCols = 0
MSFlexGrid1.FixedRows = 1
End If
End Sub
'typical usage - I presume you may need textbox (or combo, etc) for each field you wish to use for your database search
Private Sub Command1_Click()
makeGrid Trim(txtFabrikat.Text), _
Trim(txtModel.Text), _
Trim(txtVariant.Text), _
Trim(txtArgang.Text), _
Trim(txtMotor.Text), _
Trim(txtGear.Text), _
Trim(txtKarosseri.Text), _
Trim(txtSide.Text), _
Trim(txtsFarve.Text), _
Trim(txtPris.Text), _
Trim(txtAntal.Text)
End Sub
Because all arguments in the procedure header are optional you don't have to provide all of them but it really doesn't hurt to do so.
Re: Advanced Search on MSFlexGrid vb6
yes okay it works now, thank you very much (:
Re: Advanced Search on MSFlexGrid vb6
In that case please change thread status to "resolved" from the Thread Tools menu.
btw, I'm a bit puzzled how could possibly managed to apply my last sample in 9 (?!) minutes.
Re: Advanced Search on MSFlexGrid vb6
iam new here, where i do that?
heh niice :)
Re: Advanced Search on MSFlexGrid vb6
You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
hello again
i got a problem again.
in you last code you have posted:
Code:
If Not rs.EOF Then
MSFlexGrid1.Rows = 1
MSFlexGrid1.Cols = rs.Fields.Count
For i = 0 To rs.Fields.Count - 1
MSFlexGrid1.TextMatrix(0, i) = rs.Fields(i).Name
Next i
Do While Not rs.EOF
MSFlexGrid1.AddItem ""
For i = 0 To Fields.Count - 1 <-I have changed Fields.Count with 14 becouse i didnt wants all the rows to be shown. as i have some hidden rows, but it still appear(actually the content of therse tables dont appear when i change to 14) but i dont want the hidden fields to appear in the msflexgrid table.
MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, i) = "" & rs.Fields(i).Value
Next i
rs.MoveNext
Loop
MSFlexGrid1.FixedCols = 0
MSFlexGrid1.FixedRows = 1
End If
another thing was, before i could click on a specific column on the table, and a new windows will appear like that:http://dl.dropbox.com/u/17796755/1.JPG
but when i used that code upstais, now the fields is embty on the new form
http://dl.dropbox.com/u/17796755/2.JPG
I used that code for msflexgrid
Code:
Private Sub MSFlexGrid1_Click()
On Error Resume Next
id = MSFlexGrid1.TextMatrix(MSFlexGrid1.RowSel, 0)
End Sub
Private Sub MSFlexGrid1_DblClick()
On Error Resume Next
If id <= 0 Then
MsgBox "Please choose a carpart", vbCritical, "Basic 1.0"
MSFlexGrid1.SetFocus
Exit Sub
End If
frmNew.id = id
frmNew.Show 1
End Sub
AND THE FORM_ACTIVATE ON THE ANOTHER FORM THAT OPENS IS:
Private Sub Form_Activate()
On Error Resume Next
If rs.State = 1 Then
rs.Close
End If
rs.Open "select * from AddressBook where ID = " & id, conn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
fillData
End If
end sub
i hope someone would help
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
Ouch... I strongly recommend that you read the article What is wrong with using "On Error Resume Next"? from our Classic VB FAQs (in the FAQ forum). The way you have used it is not appropriate, and it should be removed - which might cause VB tell you what the problem is.
In terms of checking if a recordset has got data, you should not use .RecordCount (which is slow and unreliable), instead you should check the .EOF and .BOF properties, eg:
Code:
If Not(rs.BOF And rs.EOF) Then
fillData
End If
Making this change might fix the problem.
If it doesn't work after those changes (and you aren't getting errors), debug to make sure that Form_Activate has the correct value of id. If you don't know how to debug, there is a tutorial about it in the "General" section of our Classic VB FAQs (in the FAQ forum)
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
Og thank you sir, it worked now,
But the problem is, the fields in the new form that opens is not sorted right , so i found out it is becouse that loop function i used it just place the columns automatic in the msflexgrid (but i like that loop becouse it is faster than that one i used before
Code:
Do While Not rs.EOF
MSFlexGrid1.AddItem ""
For i = 0 To 14 'rs.Fields.Count - 1'******************'
MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, i) = "" & rs.Fields(i).Value
Next i
rs.MoveNext
Loop
i mean before i used that to sort them like what i want
Code:
rs.MoveFirst
MSFlexGrid1.Rows = rs.RecordCount + 1
rowcnt = 1
For i = 1 To rs.RecordCount
MSFlexGrid1.Row = rowcnt
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = "" & rs.Fields("ID")
MSFlexGrid1.Col = 1
MSFlexGrid1.RowHeight(rowcnt) = 350
MSFlexGrid1.Text = "" & rs.Fields("Fabrikat")
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = "" & rs.Fields("Model")
MSFlexGrid1.Col = 3
MSFlexGrid1.Text = "" & rs.Fields("Brændstof")
see the picture example please here http://dl.dropbox.com/u/17796755/ega.JPG
the first one is what i want tha columns to be sorted, but now it is the bottom picture that happens, it just sort them automaticly
thanks sir
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
That loop is only valid if the fields are in the order you want.
There are two ways you could solve it, the first is to get the fields in the correct order, eg:
Code:
rs.Open "select ID, Fabrikat, model, ... from AddressBook where ID = "
The other is to use the longer code as in the second half of your post, but instead of using the .Row and .Col and .Text properties, just use .TextMatrix
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
Quote:
Originally Posted by
si_the_geek
That loop is only valid if the fields are in the order you want.
That loop is always "valid" - what may not "appear" right is as you said fields order. However, how is it difficult to control it via sql (someone should never use "select * ..." anyway).
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
Indeed, I certainly wasn't clear there... it would have been better if I'd written "apt for what you want" rather than "valid".
Specifying the field order via SQL is easy enough, and would certainly be my preference.
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
Thank you both of you sirs, iam not good to programming databases :/ but now i have tried to add about 10.000 records and when i search no result appear! , normally if there is some hundred records it works, but now like what i said, when i have tested and added 10.00 0 records the does not search they stops, the vb6 program dont frezze or any thnig, it just dont want to search and no error msg appear? have someone tried that before?
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
The amount of records is not a problem (databases can search everything they store), which probably means that you are either searching for something (or combination of things) that isn't in the database, or your code is not correct.
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
Quote:
Originally Posted by
bibob2011
Thank you both of you sirs, iam not good to programming databases...
The sooner you stop saying that the better you will do. ;)
Learn from the samples, read tutorials (you can start from reading our very own Database FAQ), books and of course ask questions.
Re: [RESOLVED] Advanced Search on MSFlexGrid vb6
Yes RihnoBull i understand you, but it is becouse i started programming and i have never intersted me with database so now i regret that, but you said right i should do that :) and iam glad for people who help other it is very niice
