Solved Multiple search engine with dropdowns

[zelarra821]

You should be able to first filter using the FAYT and then use the combos and other controls


Yes just repeat the process


You can add the triple state back. It makes no difference.

I just tested it and it doesn't filter, that is, either filter using FAYT, or filter using combos and other controls, not both, with an AND or an OR

You can add the triple state back. It makes no difference.

If I activate it, when entering the form, it goes well, it shows me the 399 records, but if I then activate (-1), deactivate (0) and go to the null value, in the latter case it does not show me any records. Before they were shown to me. I don't understand why that happens.

 

[zelarra821]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

I have recorded a video for you to see the behavior.

Another thing. Can you put this function that you use in to filter as you type in one dropdown in the other for the textbox as a search bar?

Private Function InternationalCharacters(ByVal strText As String) As String
   InternationalCharacters = strText
   'If you type international turn first to english
    'Type international and get english
    InternationalCharacters = Replace(InternationalCharacters, "á", "a")
    InternationalCharacters = Replace(InternationalCharacters, "é", "e")
    InternationalCharacters = Replace(InternationalCharacters, "í", "i")
    InternationalCharacters = Replace(InternationalCharacters, "ó", "o")
    InternationalCharacters = Replace(InternationalCharacters, "ú", "u")
    InternationalCharacters = Replace(InternationalCharacters, "ü", "u")
    InternationalCharacters = Replace(InternationalCharacters, "ñ", "n")
    'Add others as necessary á, é, í, ó, ú, ü, ñ
    

   'Type english and get international
    InternationalCharacters = Replace(InternationalCharacters, "A", "[AÁÀÂÄaá]")
    InternationalCharacters = Replace(InternationalCharacters, "E", "[EÉÈÊËeé]")
    InternationalCharacters = Replace(InternationalCharacters, "I", "[IÍÌÎÏií]")
    InternationalCharacters = Replace(InternationalCharacters, "O", "[OÓÒÔÖ0oóøØ]")
    InternationalCharacters = Replace(InternationalCharacters, "U", "[UÚÙÛÜuú]")
    InternationalCharacters = Replace(InternationalCharacters, "N", "[NnñÑ]")
    InternationalCharacters = Replace(InternationalCharacters, "C", "[CcçÇ]")

End Function

 

[zelarra821]

I've already done it. I don't know if you would do it any other way. This class module is simpler than the other and I am more daring to touch it. The other one scares me.

Private Sub FilterForm()

  On Error GoTo errLabel

  Dim strFilter As String
  mTextBox.SetFocus
  If Not Trim(mTextBox.Text & " ") = "" Then
    mTextBox.Value = mTextBox.Text
    mForm.Filter = getFilter(InternationalCharacters(mTextBox.Text))
    mForm.FilterOn = True
    If mForm.Recordset.RecordCount = 0 Then
      MsgBox "No items matched filter " & vbCrLf & mForm.Filter, vbInformation, "No Items Found"
      mForm.FilterOn = False 'needed to set focus on textbox
      DoEvents
      mTextBox.SetFocus
      mTextBox.Value = Left(mTextBox.Text, Len(mTextBox.Text) - 1)
      FilterForm
    End If
  Else
    Call unFilterForm
  End If
  mTextBox.SetFocus
  mTextBox.SelStart = Len(mTextBox.Text)
  Exit Sub
errLabel:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  ElseIf Err.Number = 2185 Then
    MsgBox "No item found.", vbInformation, "No Item Found."
    unFilterForm
    Exit Sub
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Private Sub unFilterForm()
  On Error GoTo errLabel
  mTextBox.SetFocus
  mForm.Filter = ""
  mForm.FilterOn = False
  mTextBox.Value = ""
  mTextBox.SetFocus
   Exit Sub
errLabel:
   MsgBox Err.Number & "  " & Err.Description
End Sub

 

[MajP]

No do not do that. Do not touch the class module. Simply put the requery in the Filterme function of the form.

As for the triple state. You can see once you click into a checkbox you can never get back to a null state. You can only choose true or false. You will need to add a little button or an event to force it back to null. I made a little button next to serie. I put this code in it to make it null.

Private Sub SerieNull_Click()
  Me.EsSerie1 = Null
  FilterMe
End Sub

Then in filter me add an if check for these buttons.

If IsNull(Me.EsSerie1) Then
    strSerie = ""
  Else
    strSerie = GetFilterFromControl(Me.EsSerie1, , sdt_Boolean, , "EsSerie")
  End If

You will have to do the same for each checkbox.

 

[MajP]

Combining the FAYT and the other controls may be complicated. I do not really see the utility where you would need both.

 

[MajP]

Can you put this function that you use in to filter as you type in one dropdown in the other for the textbox as a search bar?

Can you clarify that question? I do not understand.

 

[zelarra821]

Combining the FAYT and the other controls may be complicated. I do not really see the utility where you would need both.

If it's complicated, nothing happens. You have done too much.

No do not do that. Do not touch the class module. Simply put the requery in the Filterme function of the form.

Can you clarify that question? I do not understand.

You have not understood me. You have mixed two different things.

1. I told you that you could add a Me.ActiveControl.Requery, or something similar (I don't know what), in the GetFilterFromSingleListOrCombo function of the normal mdlControlFilters module, so that it does it by system, and you don't have to do it in the FilterMe function to each combo, because the first time you update the combo (after starting the form) it does not take the value you choose, but the first one in the list.

2. And the other thing is that you could add the InternationalCharacters function of the class module that you use to filter the combo to measures that you write in the class module to filter as you write in the textbox, so that it discriminates the accents, because I am from Spain and we have that problem? advantage? Who knows! I have added it, in my own way, but that you are the one who has to say the best way, that you have done it for that and you know better how it works.

Thanks!!!!

 

[MajP]

Also one trick with the FAYT. You can convert numeric fields to strings. StrPaginas:cstr([Paginas])
Now you can find these fields using the fAYT

 

[zelarra821]

Also one trick with the FAYT. You can convert numeric fields to strings. StrPaginas:cstr([Paginas])
Now you can find these fields using the fAYTView attachment 87519

I have to do that in the source query of the form, right?

 

[MajP]

I see I never had the international characters function in the class.
1. Copy the function from the FAYTcombobox class to the FAYTformclass
2. add this line in the getfilter method

Dim strFilter As String
Dim strLike As String
Dim rs As DAO.Recordset
Dim fld As DAO.Field
TheText = Replace(TheText, "'", "''")
TheText = InternationalCharacters(TheText)

I have to do that in the source query of the form, right?

Yes. Then bind the new control like StrPaginas to the form.
For dates you can do the same using the Format Function which converts to a string.

 

[MajP]

Not fully tested
1. combines the FAYT and controls
2. Handles the triple state for serie
3. handles international characters

 

[zelarra821]

There's an error

 

[zelarra821]

It gave me an error when trying to activate the triple state

 

[MajP]

The message box is not an error but you probably want to change this. If you type in ABC and nothing matches ABC it tells you that there are no matches and moves the string back one character to AB.

I would simply change the message box to something like this

MsgBox "No items matched filter.", vbInformation, "No Items Found"

Or if you want, you can get rid of the messagebox, but this may be a little confusing.

 

[zelarra821]

The message box is not an error but you probably want to change this. If you type in ABC and nothing matches ABC it tells you that there are no matches and moves the string back one character to AB.

I would simply change the message box to something like this

MsgBox "No items matched filter.", vbInformation, "No Items Found"

Or if you want, you can get rid of the messagebox, but this may be a little confusing.

How can I change it?

 

[zelarra821]

It is not a personalized message that you can customize, but it is from Access, that's why I ask you

 

[zelarra821]

I mean, in which part of the code, or in which function do I have to put error handling.

 

[MajP]

Can you explain how you create the error? I can not create that. I do not think you can error trap it. Need to figure out the cause. I think I know the cause, but not the means to recreate it.

 

[zelarra821]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[zelarra821]

The error is when creating the filter, which adds an AND there. You know how the function you have created works, so it will be easier for you to find the fault.

Let me make a suggestion. Wouldn't it be better to reduce international characters to just String?

I'm going to wait for you to tell me about this bug to finally implement it in the database, and to be able to test better if everything works correctly, because I have to apply this to two more forms, but I don't think there are problems. If everything works fine, you will surely apply it to more databases.

Another thing. Comienzo means start. You're going to learn Spanish with me, I'm so sorry.