MessageBox if filter event contains no records

[BJF]

Hello,

I am having trouble figuring out how to make a msgbox come up if my filter contains no records.

I have the following code on the afterupdate event of a combobox that searches my forms recordset by customer name:

Me.Filter = "[tblSalesOrders.Acct]=" & str(Nz(Me![LocateSalesOrderCustomer], 0))
Me.FilterOn = True

thanks for any suggestions
BJF

 

[static]

Me.Filter = "[tblSalesOrders.Acct]=" & str(Nz(Me![LocateSalesOrderCustomer], 0))
Me.FilterOn = True

With Me.RecordsetClone
    If Not (.BOF And .EOF) Then
        .MoveLast
        MsgBox .RecordCount & " records."
    Else
        MsgBox "no records"
    End If
End With

 

[gemma-the-husky]

that code doesn't search - it just filters the records

I take it you don't want to do this, if there aren't any records?

alternatives

you could just try to find a match in the recordsetclone
you could do a find first in the displayed data
you could do a dlookup in the form recordsource for the customer you seek
you could do a dcount of the form recordsource for the customer you want.

 

[Minty]

Why not set the combobox record source to only show customers with orders, then you can't pick one that hasn't...