Solved Help with some error handling! (1 Viewer)

[slharman1]

I have this code in a txtSearchBox field on a form, when the text the user types is not on list in the form I get an error and have to close the form to refresh it.
I would like to figure out how to give the user a message saying " the text you typed is not in the list" and then have the code refresh the screen and let them continue.
Any help would be appreciated.
Also see attached pic of the form, each of the columns has similar code for it's textbox.

Private Sub txtJobNameFilter_Change()

Dim sText As String
 Dim strFilter As String
    On Error GoTo ErrHandler
   sText = Me!txtJobNameFilter.Text
   If sText <> "" Then
        strFilter = "[JobName] Like '*" & sText & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtJobNameFilter
        .SetFocus
        .Value = sText
        .SelStart = Len(sText)
        .SelLength = 0
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub

 

[theDBguy]

Hi. Is that really necessary? If your code is narrowing down the list as the user enters each character, they should immediately realize the list is empty as soon as they enter a non-matching search, right?

 

[slharman1]

Hi. Is that really necessary? If your code is narrowing down the list as the user enters each character, they should immediately realize the list is empty as soon as they enter a non-matching search, right?

As my post says when you type something (even by accidentally hitting a key) the list on the form goes blank and I must close the form and reopen it to requery the data.

 

[slharman1]

Hi. Is that really necessary? If your code is narrowing down the list as the user enters each character, they should immediately realize the list is empty as soon as they enter a non-matching search, right?

as soon as they enter a non matching search I get the error so no time to do anything.

 

[theDBguy]

as soon as they enter a non matching search I get the error so no time to do anything.

Hi. Are you able to post a sample db?

 

[slharman1]

Hi. Are you able to post a sample db?

no

 

[theDBguy]

no

Could we use the one you already posted in your other thread?

(1) Please help with code and ability to edit records on form | Access World Forums (access-programmers.co.uk)

 

[MajP]

See my solution in other thread.

Solved - Help with dynamic search on form with read only recordset

I'm stumped, maybe someone else has an idea. Can you attach the db here to play with?

www.access-programmers.co.uk

 

[The_Doc_Man]

OK, there are ways to trap the situation you mentioned for combo boxes or list boxes. There, a "not in list" event would work and allow you to recognize (and undo) what is going on. If you have a list of valid choices, make the search text box into a combo box based on that list. Then look up the NotInList event, which allows you to trap that case. Then, in the NotInList event, you could easily issue your message box after which you can issue a combobox.Undo to back out the selection and, in effect, start over.

However, note that there are options for combo boxes to do the table-based search dynamically so that you don't need to do anything special in the text box. Look up "find as you type" in this forum to see some things like that. I believe our member MajP has posted on this extensively and I would trust him to have a solution posted somewhere in the forum.

EDIT: And as soon as I started to answer, MajP jumped in with a link to the solution that I thought I remembered! MajP, you beat me by a matter of seconds on this post!

 

[slharman1]

Could we use the one you already posted in your other thread?

(1) Please help with code and ability to edit records on form | Access World Forums (access-programmers.co.uk)

It has been modified with multiple queries and additional fields on the form, hence the problem. When I added the sub query which has two subqueries in it, the code stop working.

 

[theDBguy]

It has been modified with multiple queries and additional fields on the form, hence the problem. When I added the sub query which has two subqueries in it, the code stop working.

Hi. I see. In your original db, I solved the problem by making all queries (orders and quotes) not read only. If you could provide the latest version of your db with all the new subqueries included, I might be able to see if I can make them not read only as well. Just a thought...

In any case, you could try @MajP's suggestion or the one I suggested where you just ignore the error. It's up to you... Cheers!

 

[MajP]

I solved the problem by making all queries (orders and quotes) not read only.

This makes this issue even more strange. I noticed the problem, but never noticed it was dependent on the query.

 

[theDBguy]

This makes this issue even more strange. I noticed the problem, but never noticed it was dependent on the query.

Hi @MajP. In the OP's original copies of the DB, the problem does not exist on the Estimates form, because the underlying query is updatable. However, in the other forms (Quotes and Orders), the problem occurs there, because the underlying queries were "read only." When I changed those queries to become updatable, the problem went away.

 

[MajP]

Hi @MajP. In the OP's original copies of the DB, the problem does not exist on the Estimates form, because the underlying query is updatable. However, in the other forms (Quotes and Orders), the problem occurs there, because the underlying queries were "read only." When I changed those queries to become updatable, the problem went away

I understand what you did, but why a read only query causes this issue is the strange part.

 

[theDBguy]

I understand what you did, but why a read only query causes this issue is the strange part.

My understanding is because a "read-only" query makes the form not allow additions. For example, in an updatable record source, when the form is empty (no results found), the record pointer goes to a new record, and the error does not happen. However, in a read only record source, the form can't go to the new record pointer and so the error comes up. That was my guess, but I could be wrong though.

 

[gemma-the-husky]

surely instead of just

ErrHandler:
    MsgBox Err.Description, vbExclamation

you could recover from it.

Maybe clear the entry, and tell the user to start over.
This sort of thing.

ErrHandler:
  if err=whatevernumber then
       msgbox "No details found - Restarting"
       resume startpoint  'add code to reset the combobox
  else
    MsgBox "unexpected error: " & err & vbcrlf & _
       Err.Description, vbExclamation
     exit sub 'or resume at an exit point.
end if

 

[theDBguy]

if err=whatevernumber then

Great minds think alike. I suggested to use 2185 in the other thread.

Help with dynamic search on form with read only recordset | Page 2 | Access World Forums (access-programmers.co.uk)