Avatar of rltomalin
rltomalin
Flag for United Kingdom of Great Britain and Northern Ireland 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
Microsoft Access

Avatar of undefined
Last Comment
rltomalin

8/22/2022 - Mon
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Gustav Brock

You could use the OnCurrent event.

/gustav
ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.