combobox.setfocus "not working" on a continuos form (1 Viewer)

ubi

New member
Local time
Tomorrow, 00:34
Joined
May 28, 2021
Messages
22
Hello!

im having a problem a with combobox.setfocus, this is the code that im running:

Code:
Private Sub Combo0_Change()
    Me.Combo0.SetFocus
    Me.Combo0.RowSource = "SELECT taglio.[num taglio], taglio.[cod modello], taglio.Quantita, taglio.[data creazione] FROM taglio WHERE    (((taglio.filtro) Like '*" &        Me.Combo0.Text & "*')) ORDER BY taglio.[num taglio] DESC;"
    Me.Combo0.Dropdown
End Sub

basicaly a simple filter on the combobox which works fine if recordcount > 0 on the current continuos form(pic1) but it wont work if there are no records(pic2)
The error says that it doesnt have the focus that's why i tried to force the Me.Combo0.SetFocus on every OnChange event but it doesnt work, Any ideas? :c

pic1
1627747767733.png

pic2
1627747816075.png

This is the normal combobox options
1627750656062.png
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:34
Joined
Feb 19, 2002
Messages
33,677
1. you can't set the focus to a control that already has the focus
2. The change event runs multiple times. Once for each character typed.
3. While the focus is in a control, the change event refers to the control's .text property NOT its .Value property and that may be what is causing the strange error.


Typically, this type of code is used to reduce the RowSource list when it starts out too large to be useful. i.e. tens of thousands of records. I wouldn't use it for numeric values.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:34
Joined
Feb 19, 2002
Messages
33,677
This thread has links to several combo videos that might be helpful.

 

ubi

New member
Local time
Tomorrow, 00:34
Joined
May 28, 2021
Messages
22
1. you can't set the focus to a control that already has the focus
2. The change event runs multiple times. Once for each character typed.
3. While the focus is in a control, the change event refers to the control's .text property NOT its .Value property and that may be what is causing the strange error.


Typically, this type of code is used to reduce the RowSource list when it starts out too large to be useful. i.e. tens of thousands of records. I wouldn't use it for numeric values.
yea i agree thats why i use Me.Combo0.Text as input for the filter and it works fine if there are recordsets on the continuos form, but then i dont know why if there are not recordsets in the continuos form, it says that the combobox doesnt have the focus
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:34
Joined
Feb 19, 2002
Messages
33,677
Sometimes messages are not worded the way we expect. They are written by the programmer who is looking at the problem differently. Take a look at the other solutions. They may give you a clue regarding what is wrong with your code.
 

GinaWhipp

AWF VIP
Local time
Today, 18:34
Joined
Jun 21, 2011
Messages
5,813
Hmm, give this a try. In the KeyUp event procedure of the Combo Box (might need some tweaking)...

Code:
    If Len(Me.Combo0.Text) >= 2 Then
        'Set the row source to match user search criteria
         Me.Combo0.RowSource = "SELECT taglio.[num taglio], taglio.[cod modello], taglio.Quantita, taglio.[data creazione]  " & _
                                        "FROM taglio " & _
                                            "WHERE taglio.[num taglio] LIKE '*" & Me.Combo0.Text & "*'" " & _
                                                "ORDER BY taglio.[num taglio] DESC"
        'Show the search as it's typed
         Me.Combo0.Dropdown
    Else
        'set to no
         Me.Combo0.RowSource = ""
    End If

You will most like need an event like double-click to set it back to the *normal* Row Source.

Side note, you might want to consider giving your Controls some useful names so later when you come back you don't have to flip back and forth to figure out what that Control is and what it's doing on your Form.
 

ubi

New member
Local time
Tomorrow, 00:34
Joined
May 28, 2021
Messages
22
Hmm, give this a try. In the KeyUp event procedure of the Combo Box (might need some tweaking)...

Code:
    If Len(Me.Combo0.Text) >= 2 Then
        'Set the row source to match user search criteria
         Me.Combo0.RowSource = "SELECT taglio.[num taglio], taglio.[cod modello], taglio.Quantita, taglio.[data creazione]  " & _
                                        "FROM taglio " & _
                                            "WHERE taglio.[num taglio] LIKE '*" & Me.Combo0.Text & "*'" " & _
                                                "ORDER BY taglio.[num taglio] DESC"
        'Show the search as it's typed
         Me.Combo0.Dropdown
    Else
        'set to no
         Me.Combo0.RowSource = ""
    End If

You will most like need an event like double-click to set it back to the *normal* Row Source.

Side note, you might want to consider giving your Controls some useful names so later when you come back you don't have to flip back and forth to figure out what that Control is and what it's doing on your Form.
tried and it behave exactly like the OnChange event, it works perfectly fine if recordcount > 0 but if its empty it gives the same errore message :C
 

GinaWhipp

AWF VIP
Local time
Today, 18:34
Joined
Jun 21, 2011
Messages
5,813
Did you completely remove the On_Change event? You cannot Set Focus to the Control.
 

GinaWhipp

AWF VIP
Local time
Today, 18:34
Joined
Jun 21, 2011
Messages
5,813
Hmm, do you have a Set Focus anywhere behind that Form? Also, you did put it in the KeyUp event not the OnChange event right? The code I provided will fail in the OnChange event.

EDIT: Stepping away for a bit, dinner and a movie. Will check for replies when I return.
 

NoLongerSet

New member
Local time
Today, 18:34
Joined
Jul 13, 2021
Messages
21
Is the combo box in the form header or footer?

Unbound controls in the form header or footer of a bound form exhibit all sorts of strange behavior when you filter the form so that no records are returned. The best workaround I have found for this situation is to avoid setting a filter that returns no results.

Before applying a filter to the form, I check to see how many results it will return. If it will return no records, then I hide the Detail section and Navigation Buttons. Otherwise, I apply the filter and ensure the Detail and Nav Buttons are visible.

Here's an excerpt from a function that I use to apply form filters (I actually replace the form's RecordSource, but that's a topic for another day).

Code:
    If FilteredRecordCount > 0 Then
        Dim SaveOrderBy As String
        If frm.OrderByOn Then SaveOrderBy = frm.OrderBy

        If frm.RecordSource <> NewRecordSource Then
            frm.RecordSource = NewRecordSource
            If Len(SaveOrderBy) > 0 Then
                frm.OrderByOn = True
                frm.OrderBy = SaveOrderBy
            End If
        End If
        frm.Detail.Visible = True
        frm.NavigationButtons = True
    Else
        frm.Detail.Visible = False
        frm.NavigationButtons = False
    End If

Here's the note about the bug from my function's header comments:
' Hides/displays detail and nav buttons based on whether new recordsource
' has records (avoids the Access bug where controls on header/footer
' sections of bound forms with no details to display appear blank)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:34
Joined
May 7, 2009
Messages
14,555
you can also save the .Text property of the combo to a variable
and use that variable as filter of your Select query:
Code:
Private Sub Combo0_Change()
    Dim strFilter As String
    strFilter = Me.Combo0.Text & ""
    Me.Combo0.RowSource = "SELECT taglio.[num taglio], taglio.[cod modello], taglio.Quantita, taglio.[data creazione] FROM taglio WHERE    (((taglio.filtro) Like '*" & strFilter & "*')) ORDER BY taglio.[num taglio] DESC;"
    Me.Combo0.Dropdown
End Sub
 

ubi

New member
Local time
Tomorrow, 00:34
Joined
May 28, 2021
Messages
22
Is the combo box in the form header or footer?

Unbound controls in the form header or footer of a bound form exhibit all sorts of strange behavior when you filter the form so that no records are returned. The best workaround I have found for this situation is to avoid setting a filter that returns no results.

Before applying a filter to the form, I check to see how many results it will return. If it will return no records, then I hide the Detail section and Navigation Buttons. Otherwise, I apply the filter and ensure the Detail and Nav Buttons are visible.

Here's an excerpt from a function that I use to apply form filters (I actually replace the form's RecordSource, but that's a topic for another day).

Code:
    If FilteredRecordCount > 0 Then
        Dim SaveOrderBy As String
        If frm.OrderByOn Then SaveOrderBy = frm.OrderBy

        If frm.RecordSource <> NewRecordSource Then
            frm.RecordSource = NewRecordSource
            If Len(SaveOrderBy) > 0 Then
                frm.OrderByOn = True
                frm.OrderBy = SaveOrderBy
            End If
        End If
        frm.Detail.Visible = True
        frm.NavigationButtons = True
    Else
        frm.Detail.Visible = False
        frm.NavigationButtons = False
    End If

Here's the note about the bug from my function's header comments:
Its in the form header, i tried to hide the detail and navigation buttons but it still behave in the same manner.

I made it work by adding a default recordsets in case it returns zero records but its just a workaround to the problem.

1627812662502.png
 

NoLongerSet

New member
Local time
Today, 18:34
Joined
Jul 13, 2021
Messages
21
Its in the form header, i tried to hide the detail and navigation buttons but it still behave in the same manner.

I made it work by adding a default recordsets in case it returns zero records but its just a workaround to the problem.
Ubi, the problematic behavior is a quirk of Access itself. Hiding the detail and navigation buttons does not help if you also apply the filter.

My sample code may not have been clear enough about this. Here's some pseudocode:

Code:
Will the filter return at least one record?

  If Yes, then apply the filter
 
  Otherwise, hide the detail and navigation buttons (but do NOT apply the filter)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:34
Joined
May 7, 2009
Messages
14,555
see and test post #12.
i tested it before i posted.
 

ubi

New member
Local time
Tomorrow, 00:34
Joined
May 28, 2021
Messages
22
Ubi, the problematic behavior is a quirk of Access itself. Hiding the detail and navigation buttons does not help if you also apply the filter.

My sample code may not have been clear enough about this. Here's some pseudocode:

Code:
Will the filter return at least one record?

  If Yes, then apply the filter

  Otherwise, hide the detail and navigation buttons (but do NOT apply the filter)
ohh i got it now, yea it is a good way indeed thanks!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:34
Joined
May 7, 2009
Messages
14,555

Attachments

  • comboContinuous.accdb
    420 KB · Views: 27

NoLongerSet

New member
Local time
Today, 18:34
Joined
Jul 13, 2021
Messages
21
see and test post #12.
i tested it before i posted.
The situation @ubi is describing is very specific. To reproduce the odd behavior you need the following things all in place:
  1. Bound form
  2. AllowAdditions = False (or non-updateable record source)
  3. Filtered to return no results
  4. An unbound field in the header or footer section of the form
The Access quirk/bug is that if all four of the above conditions are met, then Access will treat the controls in the header/footer sections as if they are somehow invalid because the bound form has no records to show.

I do a lot of custom filtering where the above four conditions are met. Some of these issues may have been resolved in newer versions of Access, but it was a reproducible problem for a long time.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:34
Joined
May 7, 2009
Messages
14,555
if that is the case set the AllowAdditions to Yes.
and add code to the Form's BeforeInsert to Cancel
adding new record:

private form_beforeinsert(cancel as integer)
cancel = true
end sub
 

Users who are viewing this thread

Top Bottom