Solved Error An object is needed

[zelarra821]

Hello, I am trying to pass this code to a module:

    cmbGenero.InitalizeFilterCombo Me.Genero1, "Genero", FromBeginning, True, True

This code belongs to the function that MajP created.

The function that I have in the module, and in which it should go, is the following:

Public Sub DespuesDeActualizarFormato(FName As Form, NumFormato As Long, cmbFormato As String)
    FName.Genero1.RowSource = "SELECT [TGeneros].[Genero], [TLibros].[Estado], [TFormatos].[Formato] " _
        & "FROM TFormatos INNER JOIN ((TGeneros INNER JOIN TSubgeneros ON [TGeneros].[ID] = [TSubgeneros].[Genero]) INNER JOIN TLibros ON [TSubgeneros].[ID] = [TLibros].[Subgenero]) ON [TFormatos].[ID] = [TLibros].[Formato] " _
        & "GROUP BY [TGeneros].[Genero], [TLibros].[Estado], [TFormatos].[Formato] " _
        & "HAVING ((([TLibros].[Estado]) = " & NumFormato & ") And (([TFormatos].[Formato]) = " & cmbFormato & "))" _
        & "ORDER BY [TGeneros].[Genero]"
        FName.Genero1 = ""
        cmbGenero.InitalizeFilterCombo FName.Genero1, "Genero", FromBeginning, True, True
        FName.Requery
End Sub

I call it from the form like this:

Call DespuesDeActualizarFormato(Me, 4, "[Forms]![FPendientes]![Formato1]")

However, it tells me that an object is needed, and points out the following line of the module's function:

cmbGenero.InitalizeFilterCombo FName.Genero1, "Genero", FromBeginning, True, True
End Sub

I have tried to pass different parameters, but all give me some kind of error.
Let's see if you could tell me what's wrong. Thank you!

 

[Gasman]

Do you have (presumably) a combo called cmbGenero ?

 

[zelarra821]

No, cmbGenero started it when loading the form:

Public cmbGenero As New FindAsYouTypeCombo

 

[Gasman]

Well that looks like a custom event to me and custom object.?

Best let MajP answer you, as he wrote the code and would know what is needed where.

 

[zelarra821]

Yes, I think so. Thanks

 

[The_Doc_Man]

If that function is in a GENERAL module, the combo is not in scope. That is the reason that "Me." references don't work in general modules. Your "cmbGenero" is not a formal or actual parameter to the sub, so it has to be either Public or in local scope. But a combo box name is never Public. Therefore, it is going to be important to provide the combo box reference.

If that function is in the form's CLASS module (for the form that contains the combo box), then I don't know what is wrong and would need MajP's opinion. Even so, I would try to avoid scope-limited side-effects like referring to a specific combo in a subroutine that was designed to make some function re-usable among other combos.

 

[zelarra821]

I am analyzing the code and if I put this in the module (not class module):

Public Sub DespuesDeActualizarFormato(FName As Form, NumFormato As Long, cmbFormato As String, cmbGenero1 As Object)

And then I called it from form:

    Call DespuesDeActualizarFormato(Me, 4, "[Forms]![FPendientes]![Formato1]", cmbGenero1)

It tells me that I'm missing parameters in the module function here:

        cmbGenero1.InitalizeFilterCombo FName.Genero1, "Genero", FromBeginning, True, True

And a problem with the focus in the class module:

Private Sub FilterList()
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim strText As String
  Dim strFilter As String
  strText = mCombo.Text
  strText = Replace(strText, "'", "''")
  strText = Replace(strText, "#", "[#]")
  If mHandleInternationalCharacters Then
    strText = InternationalCharacters(strText)
  End If
  If mFilterFieldName = "" Then
    MsgBox "Must Supply A FieldName Property to filter list."
    Exit Sub
  End If
  'Debug.Print mAutoCompleteEnabled
  If mAutoCompleteEnabled = False Then Exit Sub
  If mSearchType = SearchType.FromBeginning Then
    strFilter = mFilterFieldName & " like '" & strText & "*'"
  Else
    strFilter = mFilterFieldName & " like '*" & strText & "*'"
  End If
  Set rsTemp = mRsOriginalList.OpenRecordset
  rsTemp.Filter = strFilter
  Set rsTemp = rsTemp.OpenRecordset
 
  If Not (rsTemp.EOF And rsTemp.BOF) Then
    rsTemp.MoveLast
    rsTemp.MoveFirst
    'Debug.Print rsTemp.RecordCount & " Count " & strFilter
  Else
    Beep
    mAutoCompleteEnabled = True
  End If
  Set mCombo.Recordset = rsTemp
  If rsTemp.RecordCount > 0 Then
    
    mCombo.Dropdown
  End If
 
  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

 

[The_Doc_Man]

On your problem with the focus in the class module: Since that isn't an event routine, what we would need is to know the error number and which line of the code is highlighted if you take the DEBUG option when the error notice pops up.

As to that call to DespuesDeActualizarFormato, I don't see anything that stands out as a clear-cut error between the declaration and the call types. I.e. the data types seem to correspond. However, the error isn't in the call, it is something inside the call. So the question is, what are the formal parameters/definitions for .InitalizeFilterCombo? That is where you need to look.

 

[Deleted member 147267]

maybe the missing object reference is the parent of the combo as in
FName.cmbGenero.InitalizeFilterCombo or
Me.cmbGenero.InitalizeFilterCombo

If you were successfully using Me. before, then the code must have been on the form module.

 

[zelarra821]

Let's see, we go in parts:

There are two sites where I change the code: in the form, and in the normal module.

If I add an object parameter in the module (called Combo), that is:

Public Sub DespuesDeActualizarFormato(FName As Form, NumFormato As Long, cmbFormato As String, Combo As Object)
    FName.Genero1.RowSource = "SELECT [TGeneros].[Genero], [TLibros].[Estado], [TFormatos].[Formato] " _
        & "FROM TFormatos INNER JOIN ((TGeneros INNER JOIN TSubgeneros ON [TGeneros].[ID] = [TSubgeneros].[Genero]) INNER JOIN TLibros ON [TSubgeneros].[ID] = [TLibros].[Subgenero]) ON [TFormatos].[ID] = [TLibros].[Formato] " _
        & "GROUP BY [TGeneros].[Genero], [TLibros].[Estado], [TFormatos].[Formato] " _
        & "HAVING ((([TLibros].[Estado]) = " & NumFormato & ") And (([TFormatos].[Formato]) = " & cmbFormato & "))" _
        & "ORDER BY [TGeneros].[Genero]"
        FName.Genero1 = ""
        Combo.InitalizeFilterCombo FName.Genero1, "Genero", FromBeginning, True, True
        FName.Requery
End Sub

And I call it from the form in this way:

    Call DespuesDeActualizarFormato(Me, 4, "[Forms]![FPendientes]![Formato1]", cmbGenero1)

Keeping in mind that I declared cmbGenero1 at the beginning of the form:

Public cmbGenero1 As New FindAsYouTypeCombo

I get two errors:

The first, that there are few parameters missing (I enclose a capture). And the line that marks as having the error is this (this line is in the normal module above):

        Combo.InitalizeFilterCombo FName.Genero1, "Genero", FromBeginning, True, True

And the second, related to the focus (I attach capture), and, making debug in the code, the problem is in this function of the class module:

Private Sub FilterList()
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim strText As String
  Dim strFilter As String
  strText = mCombo.Text
  strText = Replace(strText, "'", "''")
  strText = Replace(strText, "#", "[#]")
  If mHandleInternationalCharacters Then
    strText = InternationalCharacters(strText)
  End If
  If mFilterFieldName = "" Then
    MsgBox "Must Supply A FieldName Property to filter list."
    Exit Sub
  End If
  'Debug.Print mAutoCompleteEnabled
  If mAutoCompleteEnabled = False Then Exit Sub
  If mSearchType = SearchType.FromBeginning Then
    strFilter = mFilterFieldName & " like '" & strText & "*'"
  Else
    strFilter = mFilterFieldName & " like '*" & strText & "*'"
  End If
  Set rsTemp = mRsOriginalList.OpenRecordset
  rsTemp.Filter = strFilter
  Set rsTemp = rsTemp.OpenRecordset
 
  If Not (rsTemp.EOF And rsTemp.BOF) Then
    rsTemp.MoveLast
    rsTemp.MoveFirst
    'Debug.Print rsTemp.RecordCount & " Count " & strFilter
  Else
    Beep
    mAutoCompleteEnabled = True
  End If
  Set mCombo.Recordset = rsTemp
  If rsTemp.RecordCount > 0 Then
    
    mCombo.Dropdown
  End If
 
  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

I also leave the class module, if you want to take a look.

Thanks a lot.

 

[MajP]

I think your issues is you never declare Combo as a FindAsYouTypeCombo. You declare it as an object. So even though you declare cmbGenero1 as an FAYT type, it appears you are casting it back to a general object type.

(FName As Form, NumFormato As Long, cmbFormato As String, Combo As Object)

Try

(FName As Form, NumFormato As Long, cmbFormato As String, Combo As FindAsYouTypeCombo)

 

[zelarra821]

Hi.
Thank you. I just put it as you tell me, and it gives me the error 3061 "Few parameters, expected 1"; and also an error 2185, that can not refer to a property or a method for a control unless the control has the focus.

 

[pbaldy]

I'm on a mobile so can't look at the db right now. The second error is usually due to using the .Text property of a control that doesn't have focus. The first is often caused by something being misspelled. Double check spellings of field names, etc.

 

[MajP]

Can you post a trimmed down version of the db? From what you show it looks like it should work.

The initialize method has 5 parameters, but only first two are required

Public Sub InitalizeFilterCombo(TheComboBox As Access.ComboBox, FilterFieldName As String, _
                      Optional TheSearchType As SearchType = SearchType.AnywhereInString, _
                      Optional HandleArrows As Boolean = True, _
                      Optional HandleInternationalCharacters As Boolean = True)

Your call passes five

Combo.InitalizeFilterCombo FName.Genero1, "Genero", FromBeginning, True, True

So I am not sure why you are getting the to few parameters. The other focus error, could be an issue with already throwing an error.

 

[zelarra821]

Here is the database. I have deleted everything, except for the form that is giving the error, and the tables, that I have to leave them, because you will need data to see why it fails.

Thanks a lot.

 

[MajP]

Right now I cannot explain what is going on. Makes no sense to me. Did you ever decompile the database as recommended?

In order to try to determine what was going on, I commented out the error handler ('On Error goto..). So now I should be able to see where the code is failing. Instead it just works as if no error is thrown. Never seen anything like that before. This is kind of like what Isladogs' calls SchrodinBugs.
https://www.access-programmers.co.uk/forums/showthread.php?t=303865&highlight=schrodinger
Just by looking at it changes it state.

 

[zelarra821]

Yes, of course I have decompiled the database. Several times. What I have not imported into a database, because I change the format of the buttons, and it is a nuisance.
I suppose that, in view of this error, the solution is not to use it in modules, right?
Thank you!

 

[MajP]

I suppose that, in view of this error, the solution is not to use it in modules, right?

No, these are real errors not related to your module.

I think you can fix the lost focus issue. The reason is if you have a combobox or text box in the header and you have filtered out all the records in the detail section the control loses focus. It is a weird Access feature. So even though you can type in the combobox and it will fire the change event, it will immediately lose focus. This line of code fails in filterlist because the change event calls the method but focus is lost even though you are physically in the control.
strText = mCombo.Text

So try this to set the focus and ignore the lost focus error 2185.

  ...
  Dim strFilter As String
  mCombo.SetFocus 'add this to reset the focus
  strText = mCombo.Text
  ....
'add this to the error handler to ignore  this error just in case
If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  ElseIf Err.Number = 2185 Then
    Debug.Print "Lost Focus."
  Else
    MsgBox Err.Number & "  " & Err.Description & " In FilterList."
  End If

The issue with too few parameters has to do with your cascading combos. You cannot have control parameters in the rowsource for the FAYT. The below will not work. You need to resolve it and pass in that resolved string.

TGeneros.Genero) = [Forms]![FPendientes]![Genero1]

You need resolve the string first. Something like

"SELECT TSubgeneros.Subgenero, TLibros.Estado, TGeneros.Genero FROM (TGeneros INNER JOIN TSubgeneros ON TGeneros.ID = TSubgeneros.Genero) INNER JOIN TLibros ON TSubgeneros.ID = TLibros.Subgenero GROUP BY TSubgeneros.Subgenero, TLibros.Estado, TGeneros.Genero HAVING (((TLibros.Estado) = 4) And ((TGeneros.Genero) = 'Ensayo'))ORDER BY TSubgeneros.Subgenero"

The FAYT has a rowsource property to allow you to do cascading combos.

 

[MajP]

Pass the value not the name of the control

Private Sub Formato1_AfterUpdate()
    Call DespuesDeActualizarFormato(Me, 4, "'" & [Forms]![FPendientes]![Formato1] & "'", cmbGenero1)
End Sub

Private Sub Genero1_AfterUpdate()
    Call DespuesDeActualizarGenero(Me, 4, "'" & [Forms]![FPendientes]![Genero1] & "'", "'" & [Forms]![FPendientes]![Formato1] & "'", cmbSubgenero)
End Sub

 

[zelarra821]

Hello, thank you very much for your explanation.

I added everything you told me, but cascading combos do not work well for me.

I update the Format for the first time, and Genre appears to me well; but on second and following occasions, it does not filter me.

It seems like I did not take the second and next updates of the combo format, in this case I mention it (even if it happens with the other combos)

No matter how many laps I give him, I do not know where the error may be.

Greetings.