By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,040 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Brand New to Access need Assist with Search via txt box in a form

P: 1
I have the database imported from an excel spreadsheet.

I am using the Asset Template for Access (Office365 ProPlus version 1904).

The filter form and table were not needed so I removed them.

My need:
I am looking for a simple search function that allows me to Search across 4 - 5 columns for specific or keyword information in the form. I would prefer to not use a query if at all possible.

What I have done thus far:
I took the filters macro that came with the template and converted it to VBA for easier editing. (My VBA knowledge is very basic FYI) I added and removed things I knew I didn't need like setting up temp vars in reference to the Filter Details Form and Table. Under QuickSearch, I changed the filter names for the columns to the columns I had imported from the Excel Spreadsheet.

My Issue:
I am currently having an issue trying to get the Command Buttons to either call the function from the Module via Macro or to use an Event Procedure to call the specific Function from the Module.

I would graciously accept any assistance you guys can provide.

Thanks in Advance!

Code is below:
Expand|Select|Wrap|Line Numbers
  1. Attribute VB_Name = "Converted Macro- Filters"
  2. Option Compare Database
  3.  
  4. '------------------------------------------------------------
  5. ' Filters
  6. '
  7. '------------------------------------------------------------
  8. Function Filters()
  9. On Error GoTo Filters_Err
  10.  
  11.     Beep
  12.     ' Macro can't be run from the navigation pane.
  13.     Exit Function
  14.  
  15.  
  16. Filters_Exit:
  17.     Exit Function
  18.  
  19. Filters_Err:
  20.     MsgBox Error$
  21.     Resume Filters_Exit
  22.  
  23. End Function
  24.  
  25. '------------------------------------------------------------
  26. ' Filters_ClearFilter
  27. '
  28. '------------------------------------------------------------
  29. Function Filters_ClearFilter()
  30. On Error Goto Filters_ClearFilter_Err
  31.  
  32.     With CodeContextObject
  33.         If (CurrentProject.IsTrusted And .Form.Name = "Asset List") Then
  34.             .Form!cboFilterFavorites = ""
  35.         End If
  36.         ' Clear Filter
  37.         DoCmd.ApplyFilter "", """""", ""
  38.         If (.Form.Name = "Asset List" And Not (.Form!chkShowRetired)) Then
  39.             ' Filter out Retired assets if checkbox is cleared
  40.             DoCmd.ApplyFilter "", Eval("""[Retired Date ] is null or [Retired Date ] > date()"""), ""
  41.         End If
  42.         DoCmd.GoToControl "txtSearchBox"
  43.         DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "0"
  44.         DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0"
  45.         DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1"
  46.         If (CurrentProject.IsTrusted) Then
  47.             .txtSearchBox = ""
  48.         End If
  49.         Exit Function
  50.     End With
  51.  
  52.  
  53. Filters_ClearFilter_Exit:
  54.     Exit Function
  55.  
  56. Filters_ClearFilter_Err:
  57.     MsgBox Error$
  58.     Resume Filters_ClearFilter_Exit
  59.  
  60. End Function
  61.  
  62. '------------------------------------------------------------
  63. ' Filters_QuickSearch
  64. '
  65. '------------------------------------------------------------
  66. Function Filters_QuickSearch()
  67. On Error Goto Filters_QuickSearch_Err
  68.  
  69.     With CodeContextObject
  70.         If (.txtSearchBox = "") Then
  71.             ' Clear Filter when search box empty
  72.             DoCmd.RunMacro "Filters.ClearFilter", , ""
  73.             End
  74.         End If
  75.         If (VarType(.txtSearchBox) <> 8) Then
  76.             End
  77.         End If
  78.         DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1"
  79.         If (.txtSearchBox = "" And .cmdSearchClear.Visible <> 0) Then
  80.             DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "0"
  81.             End
  82.             DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "0"
  83.         End If
  84.         ' Handle "'s in search
  85.         TempVars.Add "strSearch", Replace(.txtSearchBox, """", """""")
  86.         ' Build the Filter for the Asset list
  87.         If (.Form.Name = "Asset List") Then
  88.             TempVars.Add "strFilter", "([Status] Like "" * " & [TempVars]![strSearch] & " * "" )"
  89.         End If
  90.         If (.Form.Name = "Asset List") Then
  91.             TempVars.Add "strFilter", TempVars!strFilter & " OR ([Account/Matter] Like "" * " & [TempVars]![strSearch] & " * "" )"
  92.         End If
  93.         If (.Form.Name = "Asset List") Then
  94.             TempVars.Add "strFilter", TempVars!strFilter & " OR ([Container] Like "" * " & [TempVars]![strSearch] & " * "" )"
  95.         End If
  96.         If (.Form.Name = "Asset List") Then
  97.             TempVars.Add "strFilter", TempVars!strFilter & " OR ([NTID] Like "" * " & [TempVars]![strSearch] & " * "" )"
  98.         End If
  99.         If (.Form.Name = "Asset List") Then
  100.             TempVars.Add "strFilter", TempVars!strFilter & " OR ([BP Barcode] Like "" * " & [TempVars]![strSearch] & " * "" )"
  101.         End If
  102.  
  103.         ' Apply the Filter
  104.         DoCmd.ApplyFilter "", TempVars!strFilter, ""
  105.         TempVars.Remove "strFilter"
  106.         TempVars.Remove "strSearch"
  107.         DoCmd.SetProperty "cmdSearchClear", acPropertyVisible, "-1"
  108.         DoCmd.SetProperty "iconSearchClear", acPropertyVisible, "-1"
  109.         DoCmd.GoToControl "txtSearchBox"
  110.         DoCmd.SetProperty "cmdSearchGo", acPropertyVisible, "-1"
  111.     End With
  112.  
  113.  
  114. Filters_QuickSearch_Exit:
  115.     Exit Function
  116.  
  117. Filters_QuickSearch_Err:
  118.     MsgBox Error$
  119.     Resume Filters_QuickSearch_Exit
  120.  
  121. End Function
  122.  
  123.  
3 Weeks Ago #1
Share this Question
Share on Google+
1 Reply


twinnyfo
Expert Mod 2.5K+
P: 3,184
soapbox3000,

Welcome to Bytes!

First, before we get too far down the "this is really an overcomplexification of avery simple thing" road, I want to ask exactly what it is you are trying to do? It looks like one of the most complicated basic searches I've every seen.

If, you simply want to "search for" something, then, if the form has the navigation buttons displayed (which is a built in feature of Access forms), then just use the search box at the bottom of your form.

But, If you must actually create a filter for the records you are displaying, that is an entirely different story and there is probably a series of additional questions, answers and calrifying details we will need on this one.

Don't give up hope--we just need to know more before we can give you better guidance.
3 Weeks Ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.