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

[ubi]

Hello!

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

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

pic2

This is the normal combobox options

 

[Pat Hartman]

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]

This thread has links to several combo videos that might be helpful.

Combo Box woes

Good morning everyone, I downloaded a file from a Gov website that contains 3,577,514 records. By far the largest table I have ever worked with. I need to use this table as a record source for a combo-box but the 65,536 limit is a real buzz-kill. Dr. Google led me to Allen Browne's solution...

www.access-programmers.co.uk

 

[ubi]

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]

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]

Hmm, give this a try. In the KeyUp event procedure of the Combo Box (might need some tweaking)...

    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]

Hmm, give this a try. In the KeyUp event procedure of the Combo Box (might need some tweaking)...

    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]

Did you completely remove the On_Change event? You cannot Set Focus to the Control.

 

[ubi]

Did you completely remove the On_Change event? You cannot Set Focus to the Control.

yes completely removed

 

[GinaWhipp]

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]

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).

    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]

you can also save the .Text property of the combo to a variable
and use that variable as filter of your Select query:

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]

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).

    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.

 

[NoLongerSet]

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:

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]

see and test post #12.
i tested it before i posted.

 

[ubi]

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:

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!

 

[ubi]

see and test post #12.
i tested it before i posted.

unfortunatly it doesnt work on my case

 

[arnelgp]

unfortunatly it doesnt work on my case

maybe you overlooked the code, or

 

[NoLongerSet]

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]

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