asked on

Finding duplicates on continuous form

Good morning experts,

I have a question about checking for duplicates in a continuous form.

The form’s Data source is “qrySelectInvoicesToBeSent”.
It displays a list of invoice records.
This query has a field “Client ID”
During the load event for the form I want to say:
If ANY clients have more than one invoice – display a MsgBox.

I think I will be using DCount() but can’t figure out the syntax to use in this case.

Hope someone can help.

Regards

Richard

mbizup

I don't think DCount alone would work for that.

Try this:

Dim strSQL as string
dim rs as DAO.recordset
strSQL = "SELECT Count(InvoiceNo) AS TotInv, ClientID FROM qrySelectInvoicesToBeSent " _
            & "GROUP BY ClientID HAVING Count(InvoiceNo) >1"
Set rs = CurrentDB.OpenRecordset (strSQL)
if rs.Recordcount > 0 then
    MsgBox "You have at least one client with multiple invoices"
End If

Open in new window

This code is assuming that you have an InvoiceNo or some other field that we can count while grouping by ClientID.

rltomalin

ASKER

Thanks mbizup

Just got to leave the office for a bit. Will look when I get back.

Thanks for the rapid response.

Regards

Richard

rltomalin

ASKER

Hi mbizup

That is excellent, thanks. It does just what I expected - except -

I would like the message to appear AFTER the form is displayed. Curently it shows before.
I also tried the form open event, but the same.

Is there an event that I can use? If not I will live with it how it is.

Regards

Richard

Gustav Brock

You could use the OnCurrent event.

/gustav

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rltomalin

ASKER

Hi gustav

This doesn't seem to work. I have a button on a menu form that opens a Continuous form to display a list of records.

I have the code in the VB of the continuous form. But whatever event I choose (incl Open and Current) the msg displays on the menu form, before the continuous form in displayed.

Regards

Richard

rltomalin

ASKER

Hi mbizup
That worked perfectly - thanks

Richard