find and filter with vba

 Here are the screenshots:

label names here are important





And Here is the vba code :

'------------------------------------FIND----------------------------------------------
Private Sub cmd_find_Click()
fctfind_a_field

       

End Sub

Function fctfind_a_field() As String
Dim RecSrcFrmPersIdSql As String

Select Case Me!txtfield.Value
'santral;fabrika_entry;aktif_status;max_km;max_dk;ALL

Case Is = "santral"

RecSrcFrmPersIdSql = "SELECT [tbl_ref_021_1_santral].[santral],[tbl_data_01_10_01_mesafe_limit].[mesafe_limit_id], [tbl_data_01_10_01_mesafe_limit].[santral_id], [tbl_data_01_10_01_mesafe_limit].[fabrika_entry_limit], [tbl_data_01_10_01_mesafe_limit].[aktif_status_id], [tbl_data_01_10_01_mesafe_limit].[max_km], [tbl_data_01_10_01_mesafe_limit].[max_dk], [tbl_data_01_10_01_mesafe_limit].[auto_date], [tbl_data_01_10_01_mesafe_limit].[auto_time]" & _
"FROM [tbl_ref_021_1_santral] INNER JOIN [tbl_data_01_10_01_mesafe_limit] ON [tbl_ref_021_1_santral].[santral_id] = [tbl_data_01_10_01_mesafe_limit].[santral_id]" & _
"WHERE [tbl_ref_021_1_santral].[santral] Like '*' & [Forms]![frm_data_01_10_01_mesafe_limit]![txtfind] & '*';"
Forms!frm_data_01_10_01_mesafe_limit.RecordSource = RecSrcFrmPersIdSql
lbl_filter_color

Case Is = "fabrika_entry"

RecSrcFrmPersIdSql = "SELECT [tbl_data_01_10_01_mesafe_limit].[mesafe_limit_id], [tbl_data_01_10_01_mesafe_limit].[santral_id], [tbl_data_01_10_01_mesafe_limit].[fabrika_entry_limit], [tbl_data_01_10_01_mesafe_limit].[aktif_status_id], [tbl_data_01_10_01_mesafe_limit].[max_km], [tbl_data_01_10_01_mesafe_limit].[max_dk], [tbl_data_01_10_01_mesafe_limit].[auto_date], [tbl_data_01_10_01_mesafe_limit].[auto_time]" & _
"FROM [tbl_data_01_10_01_mesafe_limit]" & _
"WHERE [tbl_data_01_10_01_mesafe_limit].[fabrika_entry_limit] Like '*' & [Forms]![frm_data_01_10_01_mesafe_limit]![txtfind] & '*';"
Forms!frm_data_01_10_01_mesafe_limit.RecordSource = RecSrcFrmPersIdSql
lbl_filter_color

Case Is = "aktif_status"

RecSrcFrmPersIdSql = "SELECT [tbl_data_01_10_01_mesafe_limit].[mesafe_limit_id], [tbl_data_01_10_01_mesafe_limit].[santral_id], [tbl_data_01_10_01_mesafe_limit].[fabrika_entry_limit], [tbl_data_01_10_01_mesafe_limit].[aktif_status_id], [tbl_data_01_10_01_mesafe_limit].[max_km], [tbl_data_01_10_01_mesafe_limit].[max_dk], [tbl_data_01_10_01_mesafe_limit].[auto_date], [tbl_data_01_10_01_mesafe_limit].[auto_time], [tbl_ref_012_1_aktif_status].[aktif_status]" & _
"FROM [tbl_ref_012_1_aktif_status] INNER JOIN [tbl_data_01_10_01_mesafe_limit] ON [tbl_ref_012_1_aktif_status].[aktif_status_id] = [tbl_data_01_10_01_mesafe_limit].[aktif_status_id]" & _
"WHERE [tbl_ref_012_1_aktif_status].[aktif_status] Like '*' & [Forms]![frm_data_01_10_01_mesafe_limit]![txtfind] & '*';"
Forms!frm_data_01_10_01_mesafe_limit.RecordSource = RecSrcFrmPersIdSql
lbl_filter_color

Case Is = "max_km"

RecSrcFrmPersIdSql = "SELECT [tbl_data_01_10_01_mesafe_limit].[mesafe_limit_id], [tbl_data_01_10_01_mesafe_limit].[santral_id], [tbl_data_01_10_01_mesafe_limit].[fabrika_entry_limit], [tbl_data_01_10_01_mesafe_limit].[aktif_status_id], [tbl_data_01_10_01_mesafe_limit].[max_km], [tbl_data_01_10_01_mesafe_limit].[max_dk], [tbl_data_01_10_01_mesafe_limit].[auto_date], [tbl_data_01_10_01_mesafe_limit].[auto_time]" & _
"FROM [tbl_data_01_10_01_mesafe_limit]" & _
"WHERE [tbl_data_01_10_01_mesafe_limit].[max_km] Like '*' & [Forms]![frm_data_01_10_01_mesafe_limit]![txtfind] & '*';"
Forms!frm_data_01_10_01_mesafe_limit.RecordSource = RecSrcFrmPersIdSql
lbl_filter_color

Case Is = "max_dk"

RecSrcFrmPersIdSql = "SELECT [tbl_data_01_10_01_mesafe_limit].[mesafe_limit_id], [tbl_data_01_10_01_mesafe_limit].[santral_id], [tbl_data_01_10_01_mesafe_limit].[fabrika_entry_limit], [tbl_data_01_10_01_mesafe_limit].[aktif_status_id], [tbl_data_01_10_01_mesafe_limit].[max_km], [tbl_data_01_10_01_mesafe_limit].[max_dk], [tbl_data_01_10_01_mesafe_limit].[auto_date], [tbl_data_01_10_01_mesafe_limit].[auto_time]" & _
"FROM [tbl_data_01_10_01_mesafe_limit]" & _
"WHERE [tbl_data_01_10_01_mesafe_limit].[max_dk] Like '*' & [Forms]![frm_data_01_10_01_mesafe_limit]![txtfind] & '*';"
Forms!frm_data_01_10_01_mesafe_limit.RecordSource = RecSrcFrmPersIdSql
lbl_filter_color


Case Is = "ALL"
'function
showall
Forms!frm_data_01_10_01_mesafe_limit!txtfind.Value = ""

'function
lbl_filter_color
End Select

DoCmd.Requery
settingfocusontxt

End Function

'-----now act on label color for letting user know the data is filtered----

Function lbl_filter_color()
Select Case Forms!frm_data_01_10_01_mesafe_limit!txtfind.Value
Case Is <> ""
lbl_filter_status.ForeColor = vbRed
lbl_filter_status.BorderColor = vbRed
lbl_filter_status.Caption = "FILTER ON"
Case Is = ""
lbl_filter_status.ForeColor = vbGreen
lbl_filter_status.BorderColor = vbGreen
lbl_filter_status.Caption = "FILTER OFF"

End Select
End Function

'---------showing all data---filter off--------------

Function showall()
RecSrcFrmPersIdSql = "SELECT [tbl_data_01_10_01_mesafe_limit].[mesafe_limit_id], [tbl_data_01_10_01_mesafe_limit].[santral_id], [tbl_data_01_10_01_mesafe_limit].[fabrika_entry_limit], [tbl_data_01_10_01_mesafe_limit].[aktif_status_id], [tbl_data_01_10_01_mesafe_limit].[max_km], [tbl_data_01_10_01_mesafe_limit].[max_dk], [tbl_data_01_10_01_mesafe_limit].[auto_date], [tbl_data_01_10_01_mesafe_limit].[auto_time]" & _
"FROM [tbl_data_01_10_01_mesafe_limit]" & _
"WHERE [tbl_data_01_10_01_mesafe_limit].[mesafe_limit_id] Like '*';"
Forms!frm_data_01_10_01_mesafe_limit.RecordSource = RecSrcFrmPersIdSql
settingfocusontxt


End Function

'---------------------when form loads set focus on a textbox-----------------------------------


Private Sub Form_Load()
txtfield.Selected(1) = True
settingfocusontxt
End Sub

'---------set focus function ------

Function settingfocusontxt()

Me.txt_santral_id.SetFocus


'copythis
'set focus on text
'settingfocusontxt


End Function


Comments

Post a Comment

Popular posts from this blog

use of variable

 Sub SetFilter()     Dim LSQL  As String          LSQL = "select * from tbl_not_data_010_notlar"     LSQL = LSQL & " where madde = '" & cboShowCat & "'"          Form_frm_not_data_010_notlar.RecordSource = LSQL      End Sub
Read more

VBA - sorting data

We have an extended experience in mega projects worldwide about business management. Think of us for your business management.    The database for creating these info is designed by our company Outsideuniverse.com .  Bu çalışma için şirketimiz veri tabanını kendi tasarlayıp yapmıştır. Veriler yine şirketimiz çalışanlarınca toplanıp veri tabanına şirketimizce işlenmiştir.  Alorya Seramik ve Teknoloji A.Ş.  Ankara / Türkiye / Turkey ++90  535 930 66 18 - O. Kenan Yildirim info@alorya.com Companies:  www.alorya.com - Architectural and Decoration ceramic pottery production. /  Seramik Çini için seri üretim ve/veya butik çalışmalar atölyesi www.outsideuniverse.com  Architectural design and Software engineering. / Mimari tasarım ve Yazılım geliştirme   ++90  535 930 66 18 - contact: O. Kenan Yildirim Code :   Private Sub cmd_sort_id_Click() Me.OrderBy = "ID ASC" Me.OrderByOn = True End Sub
Read more

HTML files management with vba

'these were for updating more than 300 html files just with one code 'their objective was to make html content uniform  Private Sub cmdopnnotepad_Click() Dim dTaskID As Double, path As String, file As String path = "C:\WINDOWS\notepad.exe" Dim filevalname As String Dim filenumopn As String     For xtc = 1 To 1     simxtc = xtc + 1     filenumopn = "00" & simxtc & ".html"     filevalname = "C:\models\" & filenumopn     Me!txtfilename.Value = filenumopn     file = filevalname     dTaskID = Shell(path + " " + file, vbNormalFocus)     'MsgBox ("Text loaded")          textsd As Document          textsd = file     '****************replace************************ 'REPLACE ALL kodu asagida : txtFind = "sokolag" txtReplace = "yarrakkurek" SandR '---------------------CODE  'Private Sub makenormallistIPlist()        tx...
Read more