Hiding Subform

[PeterL]

I have a subform which can potentially contain zero records. In this event rather then just display a blank screen I would like to display a message stating that no recods have been found.

Any suggestions?

 

[TheAceMan1]

How are ya PeterL . . . . .

Try this in the [blue]OnCurrent event[/blue] of the [purple]main form[/purple] ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   If Me![purple][b]subFormName[/b][/purple].form.RecordsetClone.RecordCount=0 then
     MsgBox "No records found!"
   End If[/blue]

See Ya! . . . . . .

 

[PeterL]

Aceman
The following code isnt working:

Private Sub Form_Current()
If Me!SubToCounterHistory.Form.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found!"
End If
End Sub

I placed it OnCurrent in the main form.

Any thoughts.

 

[TheAceMan1]

PeterL . . . . .

What is the [blue]RecordSource[/blue] for [purple]SubToCounterHistory?[/purple]

If its a [blue]query[/blue], post the [blue]SQL?[/blue]

See Ya! . . . . . .

 

[PeterL]

The recordsource for the subform is a table, no query. The subform and mainform are linked via a customer name field. I display all customers in the main and if there is data in the subform it should be displayed else an error message.

 

[TheAceMan1]

OK PeterL . . . . .

Remove the old code and try the following in the [blue]OnLoad Event[/blue] of the [blue]subForm[/blue] ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim Msg As String, Style As Integer, Title As String
   
   If DCount("[[purple][b]PrimaryKeyName[/b][/purple]]", "[purple][b]YourTableName[/b][/purple]") = 0 Then
      Msg = "There are no records for subform '" & Me.Name & "'"
      Style = vbInformation + vbOKOnly
      Title = "No Records Error! . . ."
      MsgBox Msg, Style, Title
   End If[/blue]

See Ya! . . . . . .