Help with Split Form Query Where There Are No Results

[MichaelA]

I have a split form with a Combo Box on top to filter Book Titles and Authors. It works great unless I type a something that yields no results. As soon as I type a string where there is no author or title matching, I get a run-time error '2105' You can't go to the specific record. The whole form except the Form header disappears. I can end or go to debug. Could someone help me prevent that. I've included the code below.

Thank you!

Option Compare Database
Option Explicit
Private blnSpace As Boolean




Private Sub cboFilter_Change()
    If blnSpace = False Then
        Me.Requery
        Refresh
        cboFilter.SetFocus
        cboFilter.SelStart = Len(Me.cboFilter.Text)
    End If
End Sub


Private Sub cboFilter_KeyPress(KeyAscii As Integer)
    If KeyAscii = 32 Then
        blnSpace = True
    Else
        blnSpace = False
    End If
End Sub


Private Sub Form_Open(Cancel As Integer)
    Me.cboFilter.SetFocus
End Sub

[ranman256]

In the top of CHANGE event, try putting
Docmd.SetWarnings False

Then at the end of the sub, turn it back on.
This may prevent the msg, but split forms are strange animals.

[Gicu]

I would not use the change event as it triggers for every key stroke, move the code to the AfterUpdate event of the combo and see what happens (and you don't need all that code, just have a Me.Requery).

Cheers,

[MichaelA]

Ranman256

I tried this. I still get the same result.

Thank you!

[MichaelA]

I would not use the change event as it triggers for every key stroke, move the code to the AfterUpdate event of the combo and see what happens (and you don't need all that code, just have a Me.Requery).

Cheers,

I moved it to the AfterUpdate event and it works but it doesn't filter the results as you type and it doesn't filter the results until after you tab away from the combo box. Is there a way around this? The idea for the filter was to get real time results in case you don't know the exact title of the book or the exact spelling of the authors name. If it filters out, letter by letter as you type, it narrows the number of records with each letter. Oftentimes, resulting in finding what you are looking for before you ever type a whole word.

Thank you!

[Gicu]

Isn't that what autocomplete does for the combo list?

[MichaelA]

Isn't that what autocomplete does for the combo list?

As it is, it is not filtering the combobox but three different fields in the datasheet below.