asked on 3/26/2013

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

Last Comment

rltomalin

3/26/2013

mbizup

3/26/2013

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

3/26/2013

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

3/26/2013

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

3/26/2013

You could use the OnCurrent event.

/gustav

ASKER CERTIFIED SOLUTION

mbizup

3/26/2013

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

rltomalin

3/26/2013

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

3/26/2013

ASKER

Hi mbizup
That worked perfectly - thanks

Richard