MS ACCESS: NO RECORDS MESSAGE FOR A FORM 1

[PPJOSEPH]

I need a message to pop up stating that there are no records, when there are no records to show in a form. The record source for the form is a query.

 

[willir]

PPJOSEPH

One way is to count the number of records the query will return and then either display the error message if the query returns zero records, or run the query.

You can the number of records in various ways...
- DCount
- DAO or ADO recordset

 

[TheAceMan1]

How are ya PPJOSEPH . . . . .

or you can try the following in the [purple]OnCurrent Event[/purple] of the form:

[blue]   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   If Not Me.RecordsetClone.RecordCount Then
      Msg = "No records returned from source!" & DL & _
            "Change criteria and try again . . ."
      Style = vbInformation + vbOKOnly
      Title = "No Records Available Notice! . . ."
      MsgBox Msg, Style, Title
   End If[/blue]

See Ya! . . . . . .

 

[PPJOSEPH]

Thank you so much Aceman. Couple of problems. I'm getting the message when I open the form but I want it only if there are no records to show and the form to close automatically if there are no records. Is it too much to ask
Thanks again

 

[TTF]

'---------------------------------------------------------
'Set rsResp = dbR.OpenRecordset("Query", dbOpenQuery)
'On Error Resume Next
'txtStr = rsResp![Field]
'If txtStr = "" Then
'MsgBox "Search Produced No Results! Please, try again!"
'End If
'------------------------------------------------------------
Hope this helps

 

[TheAceMan1]

PPJOSEPH . . . . .

No problem . . . delete the old code, then in the [purple]OnOpen Event[/purple] of the form, copy/paste the following:

[blue]   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   If Me.RecordsetClone.RecordCount = 0 Then
      Msg = "No records returned from source!" & DL & _
            "Change criteria and try again . . ."
      Style = vbInformation + vbOKOnly
      Title = "No Records Available Notice! . . ."
      MsgBox Msg, Style, Title
      [purple][b]Cancel = True[/b][/purple]
   End If[/blue]

[purple]Cheers![/purple]

See Ya! . . . . . .

 

[PPJOSEPH]

Thanks again Mr. Aceman1, Cheers, one quick guess Aussie!!
It worked perfect you are awesome.
thanks to tektipsfanny, i tried your code but got stuck on the first line. I don't want you to put any more time on it as the Aceman's code works well.