Need to check combobox for duplicates

what is the rowsource of your combo box?

 you can use the Dcount() function in the afterupdate() event of the combo

The rowsource is:
 SELECT qry_Invoice_Detail_AllClaims.[Invoice Number], qry_Invoice_Detail_AllClaims.SystemID, qry_Invoice_Detail_AllClaims.[Amount Invoiced], qry_Invoice_Detail_AllClaims.ClaimNumber, qry_Invoice_Detail_AllClaims.[On Hold], qry_Invoice_Detail_AllClaims.Released FROM qry_Invoice_Detail_AllClaims;

Note that the entire rows will not be duplicate - the System ID's will be different.
Can I still use the Dcount? And if so, how would I code that?

can you upload a copy of the db?

You've posted the rowsource from your combo box, I think.
That's a lot of rows (6) for a combo box!

What is the recordsource for the form?
How does the combo box filter it?
Does it do an after update event with Me.Filter and Me.FilterOn, or some parameter requery?

What is the bound column of the combo box?

If you are only interested in if any duplicate exist the following SQL, if it returns a record, will indicate that
Alternatively, take out the TOP 1, and the recordset count will tell you how many Invoice Number - Invoice Amount pairings occur more than once.

SELECT TOP 1 qry_Invoice_Detail_AllClaims. [Invoice Number], qry_Invoice_Detail_AllClaims.[Amount Invoiced] , Count(qry_Invoice_Detail_AllClaims. [Invoice Number]) AS InvoiceNumberCount,Count(Invoice_Detail_AllClaims.[Amount Invoiced] ) AS AmtInvoicedCount
FROM qry_Invoice_Detail_AllClaims
Where WhateverYouComboBoxIsDoingForAFilter
GROUP BY qry_Invoice_Detail_AllClaims. [Invoice Number], qry_Invoice_Detail_AllClaims.[Amount Invoiced]
HAVING (((Count(qry_Invoice_Detail_AllClaims. [Invoice Number] ))>1) AND ((qry_Invoice_Detail_AllClaims.[Amount Invoiced]))>1))
ORDER BY Count(qry_Invoice_Detail_AllClaims. [Invoice Number]) DESC;

Select allOpen in new window

Nick,
The recordsource for the form is another query that has 5 tables involved. [QryVendorLocationInvDetail].

The filter is an After_Update Event, filtering on SystemID:
If IsNull(Me.cboSelectInvoice) Then
        Me.FilterOn = False
    Else
        Me.Filter = "SystemID = """ & Me.cboSelectInvoice & """"
        Me.FilterOn = True
    End If

The bound column is (2) - SystemID
Note that the SystemID is Unique. The problem we sometimes have is that the same record will be entered twice, under different SystemID's. That is what I want to alert the end-user to. That is why we are checking the invoice Number and AmountInvoiced.

So, I understand what you are getting at with above code. I'm a little vague on how I am going to fit this in. I want the filter to work in the usual way, however, if there is a duplicate, I would like to return a MsgBox that indicates such.

Also, what is the syntax for the filter above in SQL code?

Myself, double quotes drive me insane.
There SUCH a pain to debug.
Chr(34) is a quote mark.

Nice syntax becomes
"SystemID = " Chr(34) Me.cboSelectInvoice & Chr(34)
i'm looking over the rest and will post again shortly

Ok,

hmm.  SystemID, is that a text field?
I assumed so from the bazillion quotes.
But you know what they say about assume
ass u me.

Yes...... It is a text field. No ass u here.

Are SystemID InvoiceNumber and AmountInvoiced all from the same table?
What name does that table have?
Are InvoiceNumber and AmountInvoiced also on the form?

Yes. They are all from [tblInvoiceDetail].
Yes, they are also on the form.

Data types for them?
Amount invoiced is probably currency, but what about Invoice Number?
My own system allows for 123456-1 stuff so it's text.
String variables have to be enclosed by quotes, so it matters :)

Now,

Your filtering by SystemID, and that returns a unique record.
But, Invoice Number and Amount Invoiceed might be duplicated on a different SystemID.

So,
In the After_Update Event add this code.
Let me test it too, because the form SHOULD update after the me.FilterOn so the references work.
But I have to test that just to make sure.
Present code assumes Invoice Number and Amount Invoiced are NOT strings.
Add
& chr(34) & TheStringControlName & chr(34)
instead of just
& the StringControlName &
if one or both of them are strings

dim db as database
dim rs as recordset
dim MsgString as string

set db = currentdb 'the current app
set rs = db.openrecordset("Select SystemID from tblInvoiceDetail where [Invoice Number] = " & me.[invoice Number] & " and [Amount Invoiced] = " & me.[Amount Invoiced] & " And SystemID <> " & chr(34) & me.systemID & chr(34) & ";",dbopendynaset, dbseechanges)

If rs.recordcount = 0 then 'no duplicates
    exit sub
else
    rs.movelast
    rs.movefirst
    do until rs.eof 'walk thorough the set
        msgstring = msgstring & rs!systemid & vbcrlf ' add the systemid and a carriage return
        rs.movenext
    loop
    msgstring =msgstring & "have duplicates to this SystemID"
    Msgbox msgstring
end if

Select allOpen in new window

Ok,

I did test that assumption that the Me.FilterOn event would update the form immediately.
It does.

Let me know how it goes

Ok, I am getting
Too few parameter, expected 1.

On this line:

Set rs = db.OpenRecordset("Select SystemID from tblInvoiceDetail where [VendorInvoiceNumber] = " & Me.[VendorInvoiceNumber] & " and [AmountInvoiced] = " & Me.[AmountInvoiced] & " And SystemID <> " & Chr(34) & Me.SystemID & Chr(34) & ";", dbOpenDynaset, dbSeeChanges)

Too few parameters sometimes denotes a spelling mistake.
Still.
Let's create a new query in the query editor

Select SystemID, VendorInvoiceNumber, AmountInvoiced from tblInvoiceDetail

for the criteria have
forms!frmYourFormName![SystemID]
forms!frmYourFormName![VendorInvoiceNumber]
forms!frmYourFormName![AmountInvoiced]

for each of the three correct columns
Have your form open, and on a record.
Does the query run?

A different troubleshhoting step would be to throw some messageboxes in before the recordset

set db = currentdb 'the current app
'in here
msgbox Nz(Me.SystemID, "it was null")
msgbox Nz(Me.[VendorInvoiceNumber], "it was null")
msgbox Nz(Me.[AmountInvoiced], "it was null")
'done
set rs = db.openrecordset...

The SQL won't like a null being passed in.
Do VendorInvoiceNumber and AmountInvoiced always have values?

Sorry. I got pulled into a meeting. Going to work on this now.

Nick,
I am back on this now. Sorry for the delay.
I tried your troubleshooting (the first one) with doing a new query.
Yes, it does run.
VendorInvoiceNumber should always have a value. AmountInvoiced will often be null.

Now what?

Nz is your friend
Nz(SomethingThatMaybeNull, WhatToReplaceNullWith)

Replace Me.[VendorInvoiceNumber] in the code with
Nz(Me.[VendorInvoiceNumber],0)

But now, is that really how you want to handle Null?
SystemID   InvoiceNumber    Amount
1                 123                        Null
2                 123                         0
3                 123                         $425

Should this flag up that there are two duplicates?
You had the comparison based on Invoice Number AND Invoice Amount.
I assume that was for a reason.
So the question becomes, how do you want to handle null?

if you want null Invoice Amounts to be ignored, then you'd add

AND [AmountInvoiced] <> null to the SQL

Set rs = db.OpenRecordset("Select SystemID from tblInvoiceDetail where [VendorInvoiceNumber] = " & Me.[VendorInvoiceNumber] & " and [AmountInvoiced] = " & nz[AmountInvoiced] & " And SystemID <> " & Chr(34) & Me.SystemID & Chr(34) & " AND [AmountInvoiced] <> null;", dbOpenDynaset, dbSeeChanges)

Depends on what you need to have happen

Actually, in the example you give, there are no duplicates. A true duplicate has matching BOTH InvoiceNumber and Amount. I will mess with your code.

Meanwhile, I saved that query that you had me build, and thought that maybe I could just open that as the recordset, as I have seen that in examples in my research. So I replaced the old OpenRecordset with:
Set rs = db.OpenRecordset("CheckDupInv")

Now, I get an error message:
Too few parameters, expected 3.

Does that give another clue?

<Actually, in the example you give, there are no duplicates.>
That is the point with Nz
If you used nz to assign 0 to null, then there would be two duplicates -- the null and the 0.
So, what do you need to have happen with your nulls?

<Too few parameters, expected 3.>
Now your into something new!
When you save out a parameter query, and then use it in VBA, you have to feed it the parameters.
That uses a querydef, and querydef parameters start numbering from 0
It won't pick them up from the form like when you open the query directly.
Here is some code

dim db as database
dim rs as recordset
Dim qdf As QueryDef
dim MsgString as string

set db = currentdb 'the current app
Set qdf = db.QueryDefs("CheckDupInv") ' the saved query
qdf(0) = chr(34) & me.systemID & chr(34) 'the first parameter
qdf(1) = me.[invoice Number] 'the second parameter
qdf(2) = nz(me.[Amount Invoiced],0) 'the third parameter
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges) 'open the recordset

If rs.recordcount = 0 then 'no duplicates
    exit sub
else
    rs.movelast
    rs.movefirst
    do until rs.eof 'walk thorough the set
        msgstring = msgstring & rs!systemid & vbcrlf ' add the systemid and a carriage return
        rs.movenext
    loop
    msgstring =msgstring & "have duplicates to this SystemID"
    Msgbox msgstring
end if

Select allOpen in new window

argghhhh.
I am getting a 'type mismatch' on:

Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges) 'open the recordset

It's likely fussiness in the parameters.
Querydef may be smart enough not to need the chr(34)

Try

qdf(0) = me.systemid

Actually, looking at my code, querydef is DEFINITELY smart enough to not need the chr(34)
My bad :(
Sorry!

yuk.

Still a type mismatch.

Post the whole code block you are using and the SQL for CheckDupInv
We'll get it!

For myself, I learn more from something I had to struggle with than from something that went perfectly on the first go :)

Thanks for your words of encouragement!

Here's the SQL for the query CheckDupInv:

SELECT tblInvoiceDetail.SystemID, tblInvoiceDetail.VendorInvoiceNumber, tblInvoiceDetail.AmountInvoiced
FROM tblInvoiceDetail
WHERE (((tblInvoiceDetail.SystemID)=forms!frmInvoiceDetail1!SystemID) And ((tblInvoiceDetail.VendorInvoiceNumber)=forms!frmInvoiceDetail1!VendorInvoiceNumber) And ((tblInvoiceDetail.AmountInvoiced)=forms!frmInvoiceDetail1!AmountInvoiced));

The code block is attached.

Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim MsgString As String

Set db = CurrentDb 'the current app
Set qdf = db.QueryDefs("CheckDupInv") ' the saved query
qdf(0) = qdf(0) = Me.SystemID 'the first parameter
qdf(1) = Me.[VendorInvoiceNumber] 'the second parameter
qdf(2) = Nz(Me.[AmountInvoiced], 0) 'the third parameter
Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges) 'open the recordset

If rs.RecordCount = 0 Then 'no duplicates
    Exit Sub
Else
    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF 'walk thorough the set
        MsgString = MsgString & rs!SystemID & vbCrLf ' add the systemid and a carriage return
        rs.MoveNext
    Loop
    MsgString = MsgString & "have duplicates to this SystemID"
    MsgBox MsgString
End If

Select allOpen in new window

This isn't right
qdf(0) = qdf(0) = Me.SystemID 'the first parameter


Just this to start

qdf(0) = Me.SystemID

Let me know how that plays, and we'll work from there

Runtime error 13:
Type mismatch

What line of code gets the error?

The same line:

Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges) 'open the recordset

Alright.
Tough slogging!
1. Put the form on a record that has both an invoice number and an amount that you know will have duplicates
2. Open CheckDupInv manually

Does it open and return records?

Next, comment out the querydef parameter lines and add some hard-coded values that should work

'qdf(0) = Me.SystemID 'the first parameter
'qdf(1) = Me.[VendorInvoiceNumber] 'the second parameter
'qdf(2) = Nz(Me.[AmountInvoiced], 0) 'the third parameter

qdf(0) = "SomeSystemID"
qdf(1) = 12345
qdf(2) = 45.67

Then run it, and see if it goes

Before I get to the second part of it, I realized that I should not include the SystemID in the query. If I do, it returns only the one record that has the focus. So I removed the SystemID criteria from the query. Now it returns both records. Should I take it out of the qdf(0) code line also?

I commented out the qdf(0) line, and now it gets stuck on:
qdf(2) = Nz(Me.AmountInvoiced, 0) 'the third parameter

Item not found in this collection.

The querydefs strat numbering from 0.
Get rid of SystemID, and then re-number the others!

WE are back to type mismatch:

Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges) 'open the recordset

I will hardcode in the values and see what happens.

In the VBA editor Tools | References, what references do you have? and in what order?
I always assume
ass u me
That Microsoft DAO 3.6 is the dominant object type and not MS ActiveX Data Objects.

You can try

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

and see if disambiguating makes a difference

These are checked:

Visual basic For Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Library
Microsoft Office XP Web Components

Yay! It worked....

I added the DAO references as you suggested above.

Ok,

So your question is answered?

Now -- for extra credit :) -- do you want it to do something more than MsgBox?
Like filter the form on all the duplicates instead of just the SystemID? :)

Yes, the question is answered. I think I may move it now, though, but the hard part is done! Thanks so much.

Although your extra credit sounds intriguing, it would be purely a lesson in coding right now - I can't practically use it - now...

Okey dokey!

It's not that hard to get the Me.Filter to reflect the results of the query, if you use that form in cleaning up the duplicates.

No matter.  Glad you got it answered and I hope the querydef stuff was useful to learn.

Nick67