Solved

Need to check combobox for duplicates

Posted on 2011-03-25
45
368 Views
Last Modified: 2012-05-11
I have a combobox that filters my records for a form. I would like to add an After_Update Event that analyzes the results of the returned records, and puts up a MsgBox warning the user if there are duplicates. There are 2 fields that must have duplicate information in order for the record to be considered a duplicate. They are  qry_Invoice_Detail_AllClaims. [Invoice Number] and  qry_Invoice_Detail_AllClaims.[Amount Invoiced]. Any ideas on how to approach this?
0
Comment
Question by:4charity
  • 23
  • 20
  • 2
45 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35215383
what is the rowsource of your combo box?

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

0
 

Author Comment

by:4charity
ID: 35215910
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?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35215977
can you upload a copy of the db?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35218715
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;

Open in new window

0
 

Author Comment

by:4charity
ID: 35232101
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.
0
 

Author Comment

by:4charity
ID: 35232166
Also, what is the syntax for the filter above in SQL code?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35233035
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35233055
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.
0
 

Author Comment

by:4charity
ID: 35233093
Yes...... It is a text field. No ass u here.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35233124
Are SystemID InvoiceNumber and AmountInvoiced all from the same table?
What name does that table have?
Are InvoiceNumber and AmountInvoiced also on the form?
0
 

Author Comment

by:4charity
ID: 35233148
Yes. They are all from [tblInvoiceDetail].
Yes, they are also on the form.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35233182
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 :)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35233279
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

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35233537
Ok,

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

Let me know how it goes
0
 

Author Comment

by:4charity
ID: 35234339
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)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35234603
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?



0
 
LVL 26

Expert Comment

by:Nick67
ID: 35235155
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?
0
 

Author Comment

by:4charity
ID: 35235739
Sorry. I got pulled into a meeting. Going to work on this now.
0
 

Author Comment

by:4charity
ID: 35241170
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?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35242180
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
0
 

Author Comment

by:4charity
ID: 35242314
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?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35242455
<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

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:4charity
ID: 35243028
argghhhh.
I am getting a 'type mismatch' on:

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

Expert Comment

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

Try

qdf(0) = me.systemid
0
 
LVL 26

Expert Comment

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

Author Comment

by:4charity
ID: 35243130
yuk.

Still a type mismatch.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35243163
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 :)
0
 

Author Comment

by:4charity
ID: 35243259
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

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35243443
This isn't right
qdf(0) = qdf(0) = Me.SystemID 'the first parameter


Just this to start

qdf(0) = Me.SystemID
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35243458
Let me know how that plays, and we'll work from there
0
 

Author Comment

by:4charity
ID: 35243492
Runtime error 13:
Type mismatch
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35243500
What line of code gets the error?
0
 

Author Comment

by:4charity
ID: 35243526
The same line:

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

Expert Comment

by:Nick67
ID: 35243583
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
0
 

Author Comment

by:4charity
ID: 35243731
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?
0
 

Author Comment

by:4charity
ID: 35243748
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.
0
 
LVL 26

Expert Comment

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

Author Comment

by:4charity
ID: 35243801
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35243843
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
0
 

Author Comment

by:4charity
ID: 35243924
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
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 35243944
Yup.

You have to disambiguate!
I assumed DAO but
Microsoft ActiveX Data Objects 2.1 Library
is above it, and so Access assumes ADO

Change the Dim statements:

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

Author Comment

by:4charity
ID: 35243946
Yay! It worked....

I added the DAO references as you suggested above.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35243978
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? :)
0
 

Author Closing Comment

by:4charity
ID: 35244119
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...
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35244530
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
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now