Create a form to filter records in a table 2

[doobybug]

Hi,

I need to create a form to perform a search in a table, with any fields found in that table. Can anyone help me pls?

 

[Mayhem9]

Thanks MajP,

I created a class module and it seems happier. However, I have not had any luck in converting the combo box in my form into a fyat

I noticed that in the class module you defined three variables:

TheComboBox: Your Combobox object passed as an object
FilterFieldName: The name of the field to Filter as string
FilterFromStart: Determines if you filter a field that starts with the text or if the text appears anywhere in the record.

I looked at the remainder of the code but I couldn’t see where or even if I was suppose to do this. Also, with the code that goes into the form I was unsure if I was to modify it. I tried a few variations but nothing worked and

Option Compare Database
Option Explicit
Public faytProducts As New FindAsYouTypeCombo
Private Sub Form_Load()
  faytProducts.InitalizeFilterCombo Me.cmbProducts, "ProductName", False
End Sub

If I do need to modify it, does that mean that I can only use one of these faty boxes in a single form?

What am I doing wrong?

Cheers,
Darren

 

[MajP]

You should not touch or alter the class module in any way. The class module has to be named exactly "FindAsYouTypeCombo"

On your form you need the following code:

Option Compare Database
Option Explicit
Public faytProducts As New FindAsYouTypeCombo
Private Sub Form_Load()
faytProducts.InitalizeFilterCombo Me.cmbProducts, "ProductName", False
End Sub

Where in this example
fayProducts: can be any name. Make it represent what you are doing. "faytType", "faytManufacturer"
me.cmbProducts: is your combobox
"ProductName": is the field you want to filter


So assume I want to do two one for type and one for manufacturer

Option Compare Database
Option Explicit

Public faytType as new FindAsYouTypeCombo
Public faytMan As New FindAsYouTypeCombo
Private Sub Form_Load()
faytType.InitalizeFilterCombo Me.cmbType, "Type", False
faytMan.initializeFilterCombo Me.cmbMan, "Man", False
End Sub

 

[Mayhem9]

Thanks MajP,

It works perfectly now. Fortunately I had not altered the code in the class module, so it was just a case of changing the form code. Now this is working, I am saving a copy to the archive folder and I am going to see if I can get the code that opens the manufacturer form to enter a new manufacturer when the combo box in the tool log form is double clicked. It was working when the lookup tables had autonumber PKs, so I think I might be able to fix it.

Thanks for all your help on this MajP,
Darren

 

[Mayhem9]

Hi MajP,

In all the excitement I forgot about this bit of code:

Private Sub TypeID_KeyDown(KeyCode As Integer, Shift As Integer)  If KeyCode = 40 Then    Me.TypeID.Dropdown  End IfEnd Sub

I have placed in the to KeyDown even for the TypeID combo box but I cannot get it to work. If I type the first letter into the combo box and then I use the down arrow key it selects the first entry.

Is it because I am using the FAYT combo boxes now?

Cheers,
Darren

 

[MajP]

Yes they were not designed to work together. So when you arrow down to the first item lets say "Bosch", then it filters the list to "Bosch". So it is one or the other. Might be able to fix this in the class module and get you a new module, but it would be a little involved. If I get time I will take a look.

 

[MajP]

this seems to work. Save you old module just in case you do not like the behavior. Remove the keydown event from the main form.

Option Compare Database
Option Explicit

'Class Module Name: FindAsYouTypeCombo
'Purpose: Turn any combobox into a "Find As You Type" 'Combobox
'Created by: MajP
'
'Use:To use the class, you need code similar to the 'following in a form's module.
'Also requires a reference to DAO
'Parmaters:
'  TheComboBox: Your Combobox object passed as an object
'  FilterFieldName: The name of the field to Filter as string
'  FilterFromStart: Determines if you filter a field that starts with the text or if the
'                   text appears anywhere in the record.
'
'*******Start Form Code*******************
'
'Option Compare Database
'Option Explicit
'Public faytProducts As New FindAsYouTypeCombo
'Private Sub Form_Load()
'  faytProducts.InitalizeFilterCombo Me.cmbProducts, "ProductName", False
'End Sub
'
'******* END Form Code ******************


Private WithEvents mCombo As Access.ComboBox
Private WithEvents mForm As Access.Form
Private mFilterFieldName As String
Private mRsOriginalList As DAO.Recordset
Private mFilterFromStart As Boolean
Private mStopFilter As Boolean

Public Property Get FilterComboBox() As Access.ComboBox
  Set FilterComboBox = mCombo
End Property
Public Property Set FilterComboBox(TheComboBox As Access.ComboBox)
  Set mCombo = TheComboBox
End Property
Private Sub mCombo_Change()
  If Not mStopFilter Then
    Call FilterList
  End If
End Sub
Private Sub mCombo_GotFocus()
   mCombo.Dropdown
End Sub
Private Sub mCombo_AfterUpdate()
  mStopFilter = False
  Call unFilterList
End Sub

Private Sub mCombo_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = 40 Then
    mStopFilter = True
  End If
End Sub

Private Sub mForm_Current()
  Call unFilterList
End Sub
Private Sub FilterList()
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim strText As String
  Dim strFilter As String
  strText = mCombo.Text
  If mFilterFieldName = "" Then
    MsgBox "Must Supply A FieldName Property to filter list."
    Exit Sub
  End If
  strText = Replace(strText, "'", "''")
  If mFilterFromStart = True Then
    strFilter = mFilterFieldName & " like '" & strText & "*'"
  Else
    strFilter = mFilterFieldName & " like '*" & strText & "*'"
  End If
  Set rsTemp = mRsOriginalList.OpenRecordset
  rsTemp.Filter = strFilter
  Set rsTemp = rsTemp.OpenRecordset
  If rsTemp.RecordCount > 0 Then
    Set mCombo.Recordset = rsTemp
  End If
  mCombo.Dropdown
  Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Private Sub unFilterList()
  On Error GoTo errLable
  Set mCombo.Recordset = mRsOriginalList
  mStopFilter = False
  Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Public Property Get FilterFieldName() As String
  FilterFieldName = mFilterFieldName
End Property
Public Property Let FilterFieldName(ByVal theFieldName As String)
  mFilterFieldName = theFieldName
End Property
Private Sub Class_Terminate()
    Set mForm = Nothing
    Set mCombo = Nothing
    Set mRsOriginalList = Nothing
End Sub
Public Sub InitalizeFilterCombo(TheComboBox As Access.ComboBox, FilterFieldName As String, Optional FilterFromStart = True)
   On Error GoTo errLabel
   If Not TheComboBox.RowSourceType = "Table/Query" Then
      MsgBox "This class will only work with a combobox that uses a Table or Query as the Rowsource"
      Exit Sub
   End If
   Set mCombo = TheComboBox
   Set mForm = TheComboBox.Parent
   mFilterFieldName = FilterFieldName
   mFilterFromStart = FilterFromStart
   mForm.OnCurrent = "[Event Procedure]"
   mCombo.OnGotFocus = "[Event Procedure]"
   mCombo.OnChange = "[Event Procedure]"
   mCombo.AfterUpdate = "[Event Procedure]"
   mCombo.OnKeyDown = "[Event Procedure]"
   With mCombo
     .SetFocus
     .AutoExpand = False
   End With
   Set mRsOriginalList = mCombo.Recordset.Clone
   Exit Sub
errLabel:
    MsgBox Err.Number & " " & Err.Description
End Sub

 

[Mayhem9]

Yes they were not designed to work together.

OK - well that makes sense. I have the new FAYT class module up and running and will have a play to see which version works best for me.

Thanks for all your help,
Darren

 

[Mayhem9]

Thanks to you both for your comments and help on this topic. My database is developing and is doing things that I wanted but had no idea how to develop. Also, I am starting to understand the workings a bit more.

The topic was actually started by someone else and it ended up moving through teaching me about look-up tables and then turning a standard combo box into a nice FAYT combo box. Now these issues are solved, I’d like to return to the topic of searching.

MajP, throughout this thread you made the following comments:

The search form is primitive because it only allows you to search one field at a time.If you can understand this then, I will show the next step to have a button to make a complex search and a key word search.

I still think the type of search form I showed is far better than the generic keyword search. Just my opinion with a couple of thousand databases under my belt.

If you fix this throughout I can send you a real nice search form with a lot of utility, based on what you have.

Do these all relate to the database you posted the link to (SearchForm)? If so, I am going to work through it and see if I can understand it. If not, I’d like to hear your other suggestions.

Cheers,
Darren

 

[MajP]

http://www.4shared.com/file/208091586/5b212e33/Tool_LogMod_1_.html

Take a look at the demo. There are some new buttons at the bottom left of the tool_log form. When you click to apply the filter a search form opens.

1) This type of design allows you to pick from the existing choices. Why search for Joe's Tools if there are no records with Joe's Tools?
2) The popup can be added to for more fidelity. Just keep adding controls.
3) It provides unlimited types of filters. It allows you to choose from one or many combos. I left a message box to show you how detailed the search is with several selections.

 

[Mayhem9]

Thanks MajP,

I have had a quick look and it looks very good. I'll play with it some more and work on it with the instructions you have left.

Thanks again,
Darren

 

[Mayhem9]

Hi MajP,

I have been looking through the code in both the Tool_Log and frmFilter forms and I don't really understand it yet, but I am going to keep looking through it.

I'm just curious if it is possible to include some combo boxes in the frmFilter that contains "and" and "or" and then use the selection to control whether the search is (for example) TypeID and Size. I would set “or” as the default.

If I created the combo boxes and called them sType1, sType2 etc how should I do this? I tried creating one combo box, using the type in values option and tried the following code but I failed dismally:

 If Not Trim(Me.qType1 & " ") = "" Then
      strType = "[TypeID] = '" & qType1 & strType ="[sType1] = '" & sType1 "
  End If

As you can see I have a long way to go!

I tried changing “or” to “and” in the code and it works but it would be great to have the option in the form.

Also, I noticed that you commented that the Manufacturer search wasn’t working but it appears to be OK when I have been testing this.

Cheers,
Darren

 

[MajP]

See demo

http://www.4shared.com/file/209011675/d1f19d6a/Tool_LogModSearch.html

And this adds even more utility.

You can then take it even farther. Instead of combos you use multiselect listboxes. Then you can choose multiple criteria within a single category.

Man = "Black & Decker" or Man = "Bosch" And Type = "Socket"

But I would figure this one first.

 

[Mayhem9]

Thanks MajP,

This looks fantastic. I will try to figure out what is going on and how far off the mark I was with my thinking. I just need to find a good source of information on VB that I can relate to. I find that a lot of them are written for people who already program.

Cheers,
Darren

 

[Mayhem9]

Hello,

I have recently been updating my db to follow a more conventional naming protocol and also relacing the field name TypeID with CategoryID. I had been concentrating on the main form and functionality (which is all working) and now turned my attention the form filter that MajP developed. In fixing the issues the renaming caused I have buggered something up!

When applying the filter or selecting a specific item I get the following runtime error:

Run-time error '2465':

Microsoft Office Access can't find the field '|' referred to in your expression.

Once I click Debug, the following code is highlighted in the main form (in the code for the button that opens the filter form):

 Private Sub cmdFilterAdvanced_Click()
  'put in your error checking
  DoCmd.OpenForm "frmFilterSearchAdvanced", , , , , acDialog
  'code stops here until search form closed/hidden
  If CurrentProject.AllForms("frmFilterSearchAdvanced").IsLoaded Then
          [COLOR=green]'this section of code is highlighted when the Go To Selected Tool button is clicked in the filter form[/color]
[COLOR=red]Me.Filter = Forms("frmFilterSearchAdvanced").getFilter[/color]
    If Not Me.Filter = "" Then
      [COLOR=green]'this section of code is highlighted when the Apply Filter button is clicked in the filter form[/color]
      [COLOR=red]Me.FilterOn = True [/color]
    End If
    If Me.Recordset.RecordCount = 0 Then
      MsgBox "No Records Found"
      Me.Filter = ""
      Me.FilterOn = False
    Else
      MsgBox Me.Recordset.RecordCount & " Tools found meeting the Filter."
    End If
  DoCmd.Close acForm, "frmFilterSearchAdvanced"
  End If

End Sub

Any suggestions as to what I have stuffed up?
Darren

 

[MajP]

At a minimum, the get filter function references a lot of field names. You need to update all the field names in the function.

 

[Mayhem9]

Thanks MajP,

I'll go back and see if I can find the ones I missed.

Cheers,
Darren

 

[Mayhem9]

Hello,

I have been racking my brain trying to find the problem and decided to go back to an earlier version to look for clues. I found that it actually stopped working a few versions back but I hadn't noticed, as I was focusing on one thing at a time.

I imported the form from when it last worked and then went through all the name changes and I have come to the same conclusion.

I have looked in the code behind the filter form and the main form and also at the query but I cannot find anything that has the wrong name (obviously this is not to say that it isn't there).

Any suggestions?

Thanks,
Darren

 

[MajP]

That form became pretty advanced with a lot of features. You will probably need to post it so I can take a look.

 

[Mayhem9]

Thanks MajP,

http://www.4shared.com/file/6A5r06lP/20100430_-_Tool_Log_v5531.html

I really do appreciate your help.

Cheers,
Darren

 

[MajP]

You would have to include the backend tables for me to take a look.