Solved Zero Results from Requery

[HeyMoe]

Hi. Hope I can get some help for a little problem I've run into.

I have a simple form based on a SELECT query.
The form is set to Split View.
I have a text box that I use to restrict the results to what I'm looking for from the form's record source.
eg. If I put 'C*' in the text box, I see all results that start with 'C' in the data view and associated form .
This all works well.
...

Except for when the requery returns no results. Everything then disappears, The form is still split in two, but both dataview and form sections are blank.
And there is no way of resetting it because a form button I can use is no longer there!

Hopefully I've included all I need.

Thanks.

This is the simple code I use:
Private Sub Text74_AfterUpdate()
'Search text box.
DoCmd.Requery

'I believe I need some code here to check if the requery returned no results'

End Sub

This is the simple SQL I use:
SELECT *
FROM FullList2
WHERE ((FullList2.TitleName) Like [FORMS].[ToBeSold]![Text74])

 

[bob fitz]

Have you considered using the DCount() function in an IF/Else/Then and only doing the requery IF DCount returns >0

Untested:
If DCount("TitleName","FullList2","TitleName Like "*" & Me.Text74 & "*")>0 Then
DoCmd.Requery
EndIf

 

[arnelgp]

aside from the correct answer on post #2, the reason your form behaves like that
when on No-Matching record, the Form's AllowAdditions is Set to False.
You set it to Yes.
If you don't want to add record just add a code to the Form's BeforeInsert event:

Form BeforeInsert( Cancel As Integer)
Cancel = True
End Sub

 

[The_Doc_Man]

I see Bob Fitz made a good suggestion - but we don't know how experienced you are so you might not realize what actually happened.

WHERE ((FullList2.TitleName) Like [FORMS].[ToBeSold]![Text74])

When you use "LIKE" as an operator but do not include a wild-card character, that is the same as using the "=" operator. The only pattern that will match in that case is the exact match.

 

[Pat Hartman]

Hopefully, your criteria field is unbound. Move it to the form's header. And as Doc suggested, NEVER use Like when you mean =. When you use Like, you ALWAYS need to use wildcards to create the pattern you are trying to match.

 

[HeyMoe]

Hopefully, your criteria field is unbound. Move it to the form's header. And as Doc suggested, NEVER use Like when you mean =. When you use Like, you ALWAYS need to use wildcards to create the pattern you are trying to match.

Thanks Pat.
I know SQL pretty well, and Access I just haven't used it in years.
Got a bit rusty.
I moved the field into the header, and it did work...but I found Bob's DCount suggestion a bit more elegant.
Thanks very much.

 

[HeyMoe]

Thanks everyone that answered.
Much appreciated.

My next question is a lot harder !

 

[bob fitz]

Thanks everyone that answered.
Much appreciated.

My next question is a lot harder !

I'm glad that you have a satisfactory answer to your enquiry. Please flag the thread as Solved.

 

[Pat Hartman]

I moved the field into the header, and it did work...but I found Bob's DCount suggestion a bit more elegant.

Really? You prefer code to simply putting the control in the correct form section? OK. Access is a RAD tool. It does a lot of stuff for you. You only need to understand how Access works to take advantage. Code should always be your LAST choice, not your first.