MessageBox if filter event contains no records (1 Viewer)

B

BJF

Registered User.

Local time

Today, 03:41

Joined

Feb 19, 2010

Messages

133

• Oct 7, 2016

• #1

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

 

S

static

Registered User.

Local time

Today, 08:41

Joined

Nov 2, 2015

Messages

823

• Oct 7, 2016

• #2

Code:Copy to clipboard

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

Super Moderator

Staff member

Local time

Today, 08:41

Joined

Sep 12, 2006

Messages

15,652

• Oct 7, 2016

• #3

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

AWF VIP

Local time

Today, 08:41

Joined

Jul 26, 2013

Messages

10,371

• Oct 7, 2016

• #4

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