Want to create a search box to find record...based on 4 columns

[mhshane]

I have a DB that tracks all our wireless devices; user, number, device, so on.

Using Access 2007

I have a form with several combo boxes to search for records:
1. Search by full name
2. Search by Last name
3. Search by cell number
4. Search by datacard number
5. Search by extension

Any way to combine all these combo boxes into one so if I type in a datacard or cell number or extension it will bring up that record?

Thanks.

 

[MajP]

Here is an example of a technique to build this. This has six controls, but you can see it is basically a cut and paste to add more controls. It returns a filter string based on the selected controls on a form and a button for either AND or OR (match all or match any).

You would just have to replace your names. Then you can return the filter string and use that in a query or as a forms filter.
But by picking from my pulldowns it builds the a string like.

[CategoryID] = 'Electrical & Electronic Service Tools' OR [Serial] = '14316' OR [ManufacturerID] = 'AHBIV' OR [LocationID] = 'Top Chest (RHS): D5' OR [SubCategoryID] = 16

Public Function getFilter() As String
 ' On Error GoTo errLable
  Dim strType As String
  Dim strManufacturer As String
  Dim strSerial As String
  Dim strSet As String
  Dim strSubCategory As String
  Dim strLocation As String
  Dim andOR As String
  Dim removeEnd As Integer
  
  If Not blnSelect Then
    
    If Me.framAndOr.Value = 1 Then
      andOR = " OR "
      removeEnd = 4
    Else
      andOR = " AND "
      removeEnd = 5
    End If
    
    If Not Trim(Me.qCat1 & " ") = "" Then
        strType = "[CategoryID] = '" & qCat1 & "'" & andOR
    End If
      
    If Not Trim(Me.qSerial1 & " ") = "" Then
        strSerial = "[Serial] = '" & qSerial1 & "'" & andOR
    End If
    
    If Not Trim(Me.qMan1 & " ") = "" Then
        strManufacturer = "[ManufacturerID] = '" & qMan1 & "'" & andOR
    End If
    
    If Not Trim(Me.qSub1 & " ") = "" Then
        strSubCategory = "[SubCategoryID] = " & qSub1 & andOR
    End If
    
    If Not Trim(Me.qSet1 & " ") = "" Then
        strSet = "[Set] = '" & qSet1 & "'" & andOR
    End If
    
    If Not Trim(Me.qLoc1 & " ") = "" Then
        strLocation = "[LocationID] = '" & qLoc1 & "'" & andOR
    End If
    
    getFilter = strType + strSerial + strManufacturer + strSet + strLocation + strSubCategory
    getFilter = Left(getFilter, Len(getFilter) - removeEnd)

  Else
    If Not IsNull(lstSearch) Then
      getFilter = "[ToolID] = " & Me.lstSearch
    End If
  End If

  'You may comment this out
  Debug.Print "Filter Criteria: " & getFilter
  Exit Function
errLable:
  MsgBox Err.Number & "  " & Err.Description
End Function

 

[mhshane]

Thank you, MajP. This will certainly expand my VBA understanding.

I have a few extra questions if you don't mind.

1. Where do I place the code?

So far my VBA has revolved around Text boxes in Forms where I select an event (On Click, or After Update) and write code to perform a function. Do I associate this code with a Text Box in my form?

2. Is this batch of code an example or do I need to change to match my Column names and insert somewhere?
[CategoryID] = 'Electrical & Electronic Service Tools' OR [Serial] = '14316' OR [ManufacturerID] = 'AHBIV' OR [LocationID] = 'Top Chest (RHS): D5' OR [SubCategoryID] = 16

3. Not sure I understand the button for AND or OR. Do I create a button and assign code to it?

4. Since the process returns a filter string are you suggestion that I create a parameter query based on this filter string?

Thanks for all the help.

 

[MajP]

the framAndOr is an option group on the form with two radio buttons "AND" and "OR".
if you choose OR you get a string that looks like (match any)

[CategoryID] = 'Electrical & Electronic Service Tools' OR [Serial] = '14316' OR [ManufacturerID] = 'AHBIV' OR [LocationID] = 'Top Chest (RHS): D5' OR [SubCategoryID] = 16

if you choose "AND" you get a string like (has to match all)

[CategoryID] = 'Electrical & Electronic Service Tools' AND [Serial] = '14316' AND [ManufacturerID] = 'AHBIV' AND [LocationID] = 'Top Chest (RHS): D5' AND [SubCategoryID] = 16

Provides some added functionality.

Public Function getFilter() As String
 ' On Error GoTo errLable
  Dim strType As String
  Dim [b]strManufacturer[/b] As String
  Dim strSerial As String
  Dim strSet As String
  Dim strSubCategory As String
  Dim strLocation As String
  Dim andOR As String
  Dim removeEnd As Integer
  
  If Not blnSelect Then
    'Need a option group with an OR button and a AND button
    If Me.framAndOr.Value = 1 Then
      andOR = " OR "
      removeEnd = 4
    Else
      andOR = " AND "
      removeEnd = 5
    End If
   
   'You need to do the following for each control 
   'qCat1 is the name of the combo that returns a 
   'categrogyID.  The the field it is text so need
   'single quotes. 
   'The variable is strType which is a bad name. Should be something like strCAT
    If Not Trim(Me.qCat1 & " ") = "" Then
        strType = "[CategoryID] = '" & qCat1 & "'" & andOR
    End If
    
    'So for the next control, the control name is qSerial
    'the field is Serial and is returns as string
    'saving the result in strSerial  
    If Not Trim(Me.qSerial1 & " ") = "" Then
        strSerial = "[Serial] = '" & qSerial1 & "'" & andOR
    End If
    
    'So for the next control, the control name is qMan
    'the field is ManufacturerID and is returns as string
    'saving the result in strManufacturer
    If Not Trim(Me.[b]qMan1[/b] & " ") = "" Then
        strManufacturer = "[b][ManufacturerID][b/] = '" & [b]qMan1[/b] & "'" & andOR
    End If
    
    If Not Trim(Me.qSub1 & " ") = "" Then
        strSubCategory = "[SubCategoryID] = " & qSub1 & andOR
    End If
    
    If Not Trim(Me.qSet1 & " ") = "" Then
        strSet = "[Set] = '" & qSet1 & "'" & andOR
    End If
    
    If Not Trim(Me.qLoc1 & " ") = "" Then
        strLocation = "[LocationID] = '" & qLoc1 & "'" & andOR
    End If
    
    getFilter = strType + strSerial + strManufacturer + strSet + strLocation + strSubCategory
    getFilter = Left(getFilter, Len(getFilter) - removeEnd)

  Else
    If Not IsNull(lstSearch) Then
      getFilter = "[ToolID] = " & Me.lstSearch
    End If
  End If

  'You may comment this out
  Debug.Print "Filter Criteria: " & getFilter
  Exit Function
errLable:
  MsgBox Err.Number & "  " & Err.Description
End Function

This code is designed to go on in the form. The easiest is to apply to the form
like by adding another sub.

public sub ApplyFilter
dim strFilter as string
strFilter = getFilter
me.filteron = false
if not strFilter = "" then
me.filter = strFilter
me.filterOn = true
end if
end sub

Then you can actually call ApplyFilter from every one of the filter controls afterupdate event. You change a combox and it refilters the form.

So for each control you need a variable and a field name. See what is in bold above. You need to put your names. Example

combobox: cmboDataCard
field name: [data card]

then
...
dim strDataCard
...

If Not Trim(Me.cmboDataCard & " ") = "" Then
strDataCard = "[data card] = '" & cmboDataCard & "'" & andOR
End If