UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
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: 731
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.
!--c1-->
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]));
Go to the top of the page
 
+
jmcwk
post Aug 3 2010, 01:21 PM
Post #2

UtterAccess VIP
Posts: 12,519
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: 731
From: West Virginia, United States



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: 731
From: West Virginia, United States



Ok im getting an:
un 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: 731
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: 731
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: 731
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!
!--c1-->
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: 611
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
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: 731
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: 611
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: 731
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: 611
From: Egypt



hi,
Oassume that the three fields are required
Hope this helps
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: 731
From: West Virginia, United States



Thanks 2ME time to punch out border="0" alt="thumbup.gif" /> I will have to check it on Monday!
Thanks again,
Chad
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: 20th November 2014 - 03:40 PM

Tag cloud: