Event in forms for No records?

[Sean25]

What would one use to make an 'invisible' field visible if no record are returned by the query that a form is based on?

IE: Searching a FAQ database for keywords. The keyword searched for has no 'hits'. I want a text field to appear that states the term searched on returned no results. How can I do this? Is there anything equvalent to the Reports' "NoRecords" Event?

S.

 

[dan-cat]

What would one use to make an 'invisible' field visible if no record are returned by the query that a form is based on?

IE: Searching a FAQ database for keywords. The keyword searched for has no 'hits'. I want a text field to appear that states the term searched on returned no results. How can I do this? Is there anything equvalent to the Reports' "NoRecords" Event?

S.

How about using DCount?

If DCount("Id","YourQueryName") < 1 Then
Me.YourFieldName.Visible = False
End If

 

[Sean25]

How about using DCount?

If DCount("Id","YourQueryName") < 1 Then
Me.YourFieldName.Visible = False
End If

Nope, when I run that in the OnOpen or OnCurrent, i get an error. If I run it in AfterFinalRender, nothing happens.

 

[Mile-O]

If you are using Access 97 then you can use the RecordsetClone

i.e. (in the Current event)

Me.MyControl.Visible = IIf(Me.RecordsetClone.RecordCount = 0, True, False)

If you are using Access 2000 or greater then you'll need to set a reference to DAO, I believe, to do the same thing.

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Me.MyControl.Visible = IIf(rs.RecordCount = 0, True, False)

 

[Rich]

Private Sub Form_Open(Cancel As Integer)
If (RecordsetClone.RecordCount = 0) Then
DoCmd.Close
Beep
MsgBox "There are no records for that period.", vbInformation, "No Records"
End If

End Sub

 

[Sean25]

Private Sub Form_Open(Cancel As Integer)
If (RecordsetClone.RecordCount = 0) Then
DoCmd.Close
Beep
MsgBox "There are no records for that period.", vbInformation, "No Records"
End If

End Sub

Thanks, that seems to have done the trick, although it still wouldn't accept changing the text box visible property to true from false, so I used the msgbox function, instead.

(IE., it wouldn't take:

If (RecordsetClone.RecordCount = 0) Then
Me.NoAnswer.Visible = True
End If

S.