Forms controls disappear (Access XP)

**Rudi** · 2005-10-26 06:29

I have a form whos control source is linked to a query. On the form I have an unbound text box that allows a person to type a "search word". This word is supplied to the query and acts as criteria for a field displayed on the form. I find that when a person types a word that does not match criteria, (and the query returns no records), the forms controls disappear in the details section of the form.
How can i prevent the controls from disappearing, or at least capture this event and supply the user with some instruction of what is happening? (IE Something like the reports On No Data event?)
Tx

**HansV** · 2005-10-26 06:36

Do you have code in the On Change or After Update event of the search text box? If so, please post the code.

**Rudi** · 2005-10-26 07:01

No, there is no code in any of the forms events!
Any ideas?

**HansV** · 2005-10-26 07:05

There must be some way the form gets updated when the user enters a search word in the unbound text box. This doesn't hapen automatically.

**Rudi** · 2005-10-26 07:13

Sorry, I see what you are after! I have a code linked to the on exit event of the unbound txt box (and a command button) that requeries the form!
The code is:
<pre>Private Sub FindPS_Exit(Cancel As Integer)
Me.Requery
End Sub
</pre>

Could this be the issue?

**HansV** · 2005-10-26 07:18

You can build in a check:

Private Sub FindPS_Exit(Cancel As Integer)
If DCount("*", "[QueryName]", "[FieldName] = " & Chr(34) & Me.FindPS & Chr(34)) = 0 Then
MsgBox "No matching records!", vbInformation
Else
Me.Requery
End If
End Sub

Replace QueryName and FieldName with the appropriate names. I have assumed that the field is a text field. If it is numeric, omit both instances of & Chr(34).

**Rudi** · 2005-10-26 07:45

Hi Hans,

Tx for the assistance! The msgbox comes up every single time now! Even if the search returns records?
Sorry for being a pest!! We are half way there though!

**HansV** · 2005-10-26 07:58

What happens if you use

If DCount("*", "[QueryName]") = 0 Then

(with the correct query name substituted, of course)

**Rudi** · 2005-10-26 08:07

Yes...now it works well!!

The code looks like this now:
<pre>Private Sub FindPS_Exit(Cancel As Integer)
If DCount("*", "[qryFindPrincipleSubject]") = 0 Then
MsgBox "No matching records!", vbInformation
Me.FindPS.SetFocus
Me.FindPS.SelStart = 0
Me.FindPS.SelLength = 1000
Else
Me.Requery
End If
End Sub</pre>

When I choose OK on the msgbox, the code refuses to select the text in the FindPS txtbox, even though I set the focus on it. Any ideas while we are still on this form?
Tx

**HansV** · 2005-10-26 08:11

Instead of setting focus to FindPS, use

Cancel = True

**Rudi** · 2005-10-26 08:12

Aaaah, solved. I set the focus on another control and then back on the txtbox. Now it is working!

Like this:
<pre>Private Sub FindPS_Exit(Cancel As Integer)
If DCount("*", "[qryFindPrincipleSubject]") = 0 Then
MsgBox "No matching records!", vbInformation
Me.qryResult.SetFocus 'Select the command button
Me.FindPS.SetFocus
Me.FindPS.SelStart = 0
Me.FindPS.SelLength = 1000
Else
Me.Requery
End If
End Sub</pre>

Thread: Forms controls disappear (Access XP)

Thread Tools