Display message box when nothing is found

[c02dm]

I want to enter a name in a text box and if it can't be found in the list box then it returns a message box saying that. I've tried this code but it doesn't work. Can anyone help?

Private Sub txtLetters_AfterUpdate()

If lstSubOrder = Null Then

myOKBox ("The customer cannot be found")

Else

lstSubOrder.Requery

End If

End Sub

 

[RuralGuy]

Without knowing more about your system, try changing
If lstSubOrder = Null Then to If IsNull(lstSubOrder) Then

For the record, I believe even Null is <> Null. Null is the absence of anything.

 

[c02dm]

Hey, cheers for the reply but i have tried the code and it makes the message box appear if the name is there or not, which is strange.

 

[RuralGuy]

What in your code attempts to locate the TextBox data in the ListBox?

 

[c02dm]

I've got a query for the listbox if that is what you mean. Here is the line in the query. I don't have any code for it.

WHERE (((tblCustomer.LastName) Like [forms]![sfrmOrders]![txtLetters] & "*"))

 

[RuralGuy]

Have you tried to requery the ListBox before looking to see if it found anything?

 

[c02dm]

Have you tried to requery the ListBox before looking to see if it found anything?

Yes i have, the actual code works fine, it displays the surnames in the listbox if they match with the textbox, and doesnt display anything if nothing is found. Your code does all this but it displays the message box first whether anyone is found or not and then displays the findings after. Really annoying.

 

[RuralGuy]

Try this code instead:

Private Sub txtLetters_AfterUpdate()
Me.lstSubOrder.Requery
DoEvents
If IsNull(Me.lstSubOrder) Then
   myOKBox ("The customer cannot be found")
End If
End Sub

 

[c02dm]

That doesn't seem to work either it just displays the box and performs the filter, either way. I seem to be at a lose end.

 

[RuralGuy]

I think it is a timing issue. Why not try moving all of your code to the AfterUpdate event of the lstSubOrder?

 

[c02dm]

Hi, that just does the same as before, except the message box doesn't work. Might have to just give up on this one, thought it was going to be relatively easy when i had the idea, who would have guessed.

 

[RuralGuy]

The code in the ListBox AfterUpdate event should look something like:

If Me.lstSubOrder <> Me.txtLetters Then
   myOKBox ("The customer cannot be found")
End If

 

[c02dm]

Hey, sorry to say that it does nothing different, thanks for the continuous help though.

 

[RuralGuy]

Any chance you could post your db with enough data to demonstrate the problem?

 

[c02dm]

Heres a copy with just the relating table, query and form.

 

[RuralGuy]

OK, try this one!

 

[c02dm]

Excellent!!! Thank you very much!!! I'm fairly new to VBA and learning so help and advice on here is great. This has been annoying me for days, what a relief it finally works.

 

[RuralGuy]

Glad I could help.