Search from database from multi-textbox !!

PrivateSubFilterData(strFilterAsString)
DimiAsInteger
DimjAsInteger
DimstrSQLAsString
DimfieldSQLAsString=""
DimFldAsString=""
DimsSampData()AsString
DimstrSubSQLAsString=""
DimcboFilter(6)AsString
Dimcmb(6)AsString
cboFilter(0)=cboMain.Text
cboFilter(1)=cboCategory.Text
cboFilter(2)=cboBrand.Text
cboFilter(3)=cboDescription.Text
cboFilter(4)=cboSize.Text
cboFilter(5)=cboStyleCode.Text
cboFilter(6)=cboColor.Text
cmb(0)="MainCategory"
cmb(1)="Category"
cmb(2)="Brand"
cmb(3)="Description"
cmb(4)="sSize"
cmb(5)="StyleCode"
cmb(6)="Shade"
Fori=0Tocmb.Count-1
Ifcmb(i)=strFilterThen
GoTodown
EndIf
Next
:
Forj=0Toi
Ifj=0ThenfieldSQL=fieldSQL&","&"MainCategory='"&cboFilter(j)&"'"
Ifj=1ThenfieldSQL=fieldSQL&","&"Category='"&cboFilter(j)&"'"
Ifj=2ThenfieldSQL=fieldSQL&","&"Trim(Brand)='"&""&cboFilter(j)&"'"
Ifj=3ThenfieldSQL=fieldSQL&","&"Description='"&cboFilter(j)&"'"
Ifj=4ThenfieldSQL=fieldSQL&","&"sSize='"&cboFilter(j)&"'"
Ifj=5ThenfieldSQL=fieldSQL&","&"StyleCode='"&cboFilter(j)&"'"
Ifj=6ThenfieldSQL=fieldSQL&","&"Shade='"&cboFilter(j)&"'"
Next
sSampData=Split(fieldSQL,",")
Forj=1ToUBound(sSampData)
strSubSQL=strSubSQL&"AND"&sSampData(j)
Next
strSubSQL=Mid(strSubSQL,6)
Forj=i+1TocboFilter.Count-1
Ifj=0ThenFld="MainCategory"
Ifj=1ThenFld="Category"
Ifj=2ThenFld="Brand"
Ifj=3ThenFld="Description"
Ifj=4ThenFld="[sSize]"
Ifj=5ThenFld="StyleCode"
Ifj=6ThenFld="[Shade]"
strSQL="SelectDistinct"&Fld&"FromProductMasterWhere"&strSubSQL
Ifcmb(j)="MainCategory"ThenCallFillCombo(cboMain,strSQL,Fld,0)
Ifcmb(j)="Category"ThenCallFillCombo(cboCategory,strSQL,Fld,0)
Ifcmb(j)="Brand"ThenCallFillCombo(cboBrand,strSQL,Fld,0)
Ifcmb(j)="Description"ThenCallFillCombo(cboDescription,strSQL,Fld,0)
Ifcmb(j)="sSize"ThenCallFillCombo(cboSize,strSQL,Fld,0)
Ifcmb(j)="Stylecode"ThenCallFillCombo(cboStyleCode,strSQL,Fld,0)
Ifcmb(j)="Shade"ThenCallFillCombo(cboColor,strSQL,Fld,0)
Next
EndSub

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilesh Sawardekar10y ago
Accepted answer
1. PrivateSubFilterData(strFilterAsString)
2. DimiAsInteger
3. DimjAsInteger
4. DimstrSQLAsString
5. DimfieldSQLAsString=""
6. DimFldAsString=""
7. DimsSampData()AsString
8. DimstrSubSQLAsString=""
9. DimcboFilter(6)AsString
10. Dimcmb(6)AsString
12. cboFilter(0)=cboMain.Text
13. cboFilter(1)=cboCategory.Text
14. cboFilter(2)=cboBrand.Text
15. cboFilter(3)=cboDescription.Text
16. cboFilter(4)=cboSize.Text
17. cboFilter(5)=cboStyleCode.Text
18. cboFilter(6)=cboColor.Text
20. cmb(0)="MainCategory"
21. cmb(1)="Category"
22. cmb(2)="Brand"
23. cmb(3)="Description"
24. cmb(4)="sSize"
25. cmb(5)="StyleCode"
26. cmb(6)="Shade"
28. Fori=0Tocmb.Count-1
29. Ifcmb(i)=strFilterThen
30. GoTodown
31. EndIf
32. Next
33. :
35. Forj=0Toi
36. Ifj=0ThenfieldSQL=fieldSQL&","&"MainCategory='"&cboFilter(j)&"'"
37. Ifj=1ThenfieldSQL=fieldSQL&","&"Category='"&cboFilter(j)&"'"
38. Ifj=2ThenfieldSQL=fieldSQL&","&"Trim(Brand)='"&""&cboFilter(j)&"'"
39. Ifj=3ThenfieldSQL=fieldSQL&","&"Description='"&cboFilter(j)&"'"
40. Ifj=4ThenfieldSQL=fieldSQL&","&"sSize='"&cboFilter(j)&"'"
41. Ifj=5ThenfieldSQL=fieldSQL&","&"StyleCode='"&cboFilter(j)&"'"
42. Ifj=6ThenfieldSQL=fieldSQL&","&"Shade='"&cboFilter(j)&"'"
43. Next
44. sSampData=Split(fieldSQL,",")
45. Forj=1ToUBound(sSampData)
46. strSubSQL=strSubSQL&"AND"&sSampData(j)
47. Next
49. strSubSQL=Mid(strSubSQL,6)
51. Forj=i+1TocboFilter.Count-1
52. Ifj=0ThenFld="MainCategory"
53. Ifj=1ThenFld="Category"
54. Ifj=2ThenFld="Brand"
55. Ifj=3ThenFld="Description"
56. Ifj=4ThenFld="[sSize]"
57. Ifj=5ThenFld="StyleCode"
58. Ifj=6ThenFld="[Shade]"
59. strSQL="SelectDistinct"&Fld&"FromProductMasterWhere"&strSubSQL
60. Ifcmb(j)="MainCategory"ThenCallFillCombo(cboMain,strSQL,Fld,0)
61. Ifcmb(j)="Category"ThenCallFillCombo(cboCategory,strSQL,Fld,0)
62. Ifcmb(j)="Brand"ThenCallFillCombo(cboBrand,strSQL,Fld,0)
63. Ifcmb(j)="Description"ThenCallFillCombo(cboDescription,strSQL,Fld,0)
64. Ifcmb(j)="sSize"ThenCallFillCombo(cboSize,strSQL,Fld,0)
65. Ifcmb(j)="Stylecode"ThenCallFillCombo(cboStyleCode,strSQL,Fld,0)
66. Ifcmb(j)="Shade"ThenCallFillCombo(cboColor,strSQL,Fld,0)
67. Next
68. EndSub
Update your code using above logic
0