UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Find Duplicate records using VBA, Office 2007    
 
   
oxicottin
post Aug 3 2010, 01:04 PM
Post #1

UtterAccess Guru
Posts: 726
From: West Virginia, United States



Hello, I have a form named frmVWI and on this form is a text box named txtVWI. I wanted to run a query when data is entered in this text box to check if the same number entered is already being used and if so give a msg clear the text box. The query checks 3 fields in a table called tblVWI. It has to see it the same number was used by a department and category. Here is the sql for the query if that helps.

CODE
SELECT tblVWI.VWInum, tblVWI.VWICategory, tblVWI.Department
FROM tblVWI
WHERE (((tblVWI.VWInum)=[Forms]![frmVWI]![txtVWI]) AND ((tblVWI.VWICategory)=[Forms]![frmVWI]![cboCategory]) AND ((tblVWI.Department)=[Forms]![frmVWI]![cboDepartment]));


This post has been edited by oxicottin: Aug 3 2010, 01:05 PM
Go to the top of the page
 
+
jmcwk
post Aug 3 2010, 01:21 PM
Post #2

UtterAccess VIP
Posts: 12,311
From: Tacoma, WA.



See if This Link helps, open the form and click on the ItemID Control on the subform and look at the code in the Before Update Event I think the OP has two methods of code
Go to the top of the page
 
+
vtd
post Aug 4 2010, 02:58 AM
Post #3

Retired Moderator
Posts: 19,667



If you simply want to see if the combination already exists in the Table, then you can simply use DCount() with the saved Query. If DCount() returns 0, you know that the comibnation doesn't exist in the Table and anything > 0 means the same combination already exists.
Go to the top of the page
 
+
oxicottin
post Aug 4 2010, 07:41 AM
Post #4

UtterAccess Guru
Posts: 726
From: West Virginia, United States



QUOTE (jmcwk @ Aug 3 2010, 02:21 PM) *
See if This Link helps, open the form and click on the ItemID Control on the subform and look at the code in the Before Update Event I think the OP has two methods of code



I tried the link and added it to my before update but I cant figure out the sql?
Go to the top of the page
 
+
oxicottin
post Aug 4 2010, 10:47 AM
Post #5

UtterAccess Guru
Posts: 726
From: West Virginia, United States



Ok im getting an:

Run Time Error'-2147217913 (80040e07)'
Data type mismatch in criteria expression.

My data is all numbers?

CODE
Private Sub txtVWI_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_txtVWI_BeforeUpdate

Dim intResponse As Integer
Dim strTable As String
Dim strFind As String
Dim strSQL As String

Dim rst As ADODB.Recordset
Dim Conn As ADODB.Connection
Set Conn = CurrentProject.Connection
Set rst = New ADODB.Recordset

strTable = "tblVWI"
strSQL = "SELECT Count(*) FROM " & strTable & " WHERE " & "[Department]='" & cboDepartment & "' AND [VWINum] ='" & txtVWI & "' "


rst.Open strSQL, Conn

If rst(0) > 0 Then ' duplicated record found.
    If Me.NewRecord Then
        intResponse = MsgBox("This record exist" & vbCrLf & "Do you want to duplicate Item Number?", vbYesNo)
            If intResponse = vbNo Then
                Me.Undo
            End If
    End If
End If

Exit_txtVWI_BeforeUpdate:
    Exit Sub
    
        rst.Close
        Conn.Close
        Set rst = Nothing
        Set Conn = Nothing

Err_txtVWI_BeforeUpdate:
    MsgBox Err.Description
    Resume Exit_txtVWI_BeforeUpdate


End Sub
Go to the top of the page
 
+
vtd
post Aug 4 2010, 05:52 PM
Post #6

Retired Moderator
Posts: 19,667



What are the data-types of the Fields [Department] and [VWINum] according to design view of the Table [tblVWI]?
Go to the top of the page
 
+
oxicottin
post Aug 5 2010, 05:14 AM
Post #7

UtterAccess Guru
Posts: 726
From: West Virginia, United States



They are all number data types.

Thanks,
Chad
Go to the top of the page
 
+
oxicottin
post Aug 5 2010, 08:20 AM
Post #8

UtterAccess Guru
Posts: 726
From: West Virginia, United States



Ok I got it working but I have a few problems. First, if I try to enter a number in txtVWI it comes up with an error that I dont have the cboDepartment and cboVWICategory data. Second, if I open a record thats already there and change the txtVWI number it allows me but if I create a new record and enter the same numbers then it gives me the msg box.

Thanks,
Chad
Go to the top of the page
 
+
vtd
post Aug 5 2010, 08:53 AM
Post #9

Retired Moderator
Posts: 19,667



Sorry, I don't know what you are trying to describe...

My suggestion is to try the DCount() function in the Debug window which will be much easer for you.

Check Access help on DCount() if you are not familiar with this function.

Go to the top of the page
 
+
oxicottin
post Aug 5 2010, 01:26 PM
Post #10

UtterAccess Guru
Posts: 726
From: West Virginia, United States



I read and have been reading since your first post and I come up with this (which works)
is it the correct way to do it? Also I wanted to be able to clear the txtVWI insted of undo but it wont let me use the Me.txtVWI = "" and says I cant use it because i havent saved the record. Thanks!

CODE
Private Sub txtVWI_BeforeUpdate(Cancel As Integer)

Dim FDup As Long
Dim intResponse As Integer


FDup = DCount("[VWINum]", "tblVWI", "[Department]=" & cboDepartment & " AND [VWICategory] = " & cboVWICategory & " AND [VWINum] =" & txtVWI & " ")

If FDup > 0 Then
intResponse = MsgBox("This Document number already exist's" & vbCrLf & "Please try a different number", vbCritical, "Selection Error")
               Me.txtVWI.undo

End If
End Sub
Go to the top of the page
 
+
2ME
post Aug 5 2010, 01:51 PM
Post #11

UtterAccess Guru
Posts: 608
From: Egypt



hi,
try this
CODE
If DCount("[VWINum]", "tblVWI", "[Department]=" & Me!cboDepartment & "' AND [VWICategory]=" & Me!cboVWICategory & "' AND [VWINum] =" & ME!txtVWI ) > 0 Then
    MsgBox "this is already exist", vbExclamation
    Me.Undo
    Cancel = True


hope this helps

This post has been edited by 2ME: Aug 5 2010, 01:52 PM
Go to the top of the page
 
+
vtd
post Aug 5 2010, 05:35 PM
Post #12

Retired Moderator
Posts: 19,667



Oxicottin

You can use the intermediate VBA variables as per your code if you feel that is easier to read/understand or per 2ME's suggestion (shorter code) but you definitely need to set the Cancel argument to True.

Go to the top of the page
 
+
oxicottin
post Aug 6 2010, 09:39 AM
Post #13

UtterAccess Guru
Posts: 726
From: West Virginia, United States



VTDinh, I used 2ME's code (Thanks).... I wanted to explain the problems Im encountering now. First, If i create a new record in frmVWI and I fill the 3 controls in (cboDepartment, cboVWICategory and txtVWI) and lets say I didnt use the same number as another record but I went back to that record and mabe changed the department and left the cboVWICategory blank and the txtVWI still had a number in it then I get an expression error. How can I fix this? These 3 fields have to have something in them or I get the expression error.

Thanks!
Attached File(s)
Attached File  VWI_Test_8_5_10.zip ( 253.52K ) Number of downloads: 10
 
Go to the top of the page
 
+
2ME
post Aug 6 2010, 10:40 AM
Post #14

UtterAccess Guru
Posts: 608
From: Egypt



hi,
you should use the beforeupdate trigger on form level not the txtVWI level, take a look at the attached file

hope this helps
Attached File(s)
Attached File  VWI_v2003.zip ( 61.5K ) Number of downloads: 6
 
Go to the top of the page
 
+
oxicottin
post Aug 6 2010, 11:20 AM
Post #15

UtterAccess Guru
Posts: 726
From: West Virginia, United States



Ok but if I add info to the two combo boxes and leave txtVWI blank and try to go to the nex record it gives me error 3075


Attached File(s)
Attached File  error_3075.JPG ( 50.68K ) Number of downloads: 7
 
Go to the top of the page
 
+
2ME
post Aug 6 2010, 12:50 PM
Post #16

UtterAccess Guru
Posts: 608
From: Egypt



hi,
I assume that the three fields are required

hope this helps

This post has been edited by 2ME: Aug 6 2010, 12:52 PM
Attached File(s)
Attached File  VWI_v2003.zip ( 66.23K ) Number of downloads: 17
 
Go to the top of the page
 
+
oxicottin
post Aug 6 2010, 02:10 PM
Post #17

UtterAccess Guru
Posts: 726
From: West Virginia, United States



Thanks 2ME time to punch out (IMG:style_emoticons/default/thumbup.gif) I will have to check it on Monday!

Thanks again,
Chad
Go to the top of the page
 
+
2ME
post Aug 6 2010, 02:45 PM
Post #18

UtterAccess Guru
Posts: 608
From: Egypt



(IMG:style_emoticons/default/yw.gif)
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 19th April 2014 - 02:13 AM