Thank you Austin for taking the time to make this video. I spent several hours trying to get a message box to pop up when a user enters a number that already exists in the database and could not get it to work (looking at books, websites, etc). I took your video and closely followed what your were doing. The only difference was I used an 'on exit' instead of after_update so the user would know right away that number exists already. Thank you for your help and teaching.
I have been working with databases for over 10 years, but just started developing a week ago. This tutorial is the only 1 that actually really helped me verify if a record already exists in the database. Thank you for this video! #Subscribed
please if I may ask, how do iI write a VBA code that prevent me from saving purchase entry twice, example, lets say i have an order form with order IID (primary key)( customer ID(foreign key), product ID (foreign key), price, amount...and i saved an order and should i mistakenly hit the save button it saves again..how do i prevent it from happening so that a message would pop up saying"order id have been saved"
Hi Austin Thanks for the video. I have little issue, that duplicate entry pop up works only with one entry for me. Like, i have plant numbers to be entered. If i enter say 20 and again 20 then it pops up saying duplicate entry. then i enter 30 and enter 30 again the pop up does not pop up and let the entry to take place. at this point if enter 20 again it pops up. And again if i enter 40 as many times as i want, it just accepts it.
Hi Austin i have try this but the code is restricting all the entry. I mean if there is new entry the message popup and i am not able to enter new entry
It says "invalid use of null"..do i have to input all data to that table especially the table which dlookup applies??because some of it doesnt have the OR No..maybe thats the reason why it says invalid use of null..help me please
If I have a First Name and Last Name field instead of one "Customer Name" field, can I still use a similar code to check for duplicates? I'd love to have it do what this tutorial shows by having the pop up say it already exists and then opens that record.
you will need to use And operator to combine the criteria or looking duplicate data for both fields. I have similar example here: www.iaccessworld.com/prevent-duplicate-data-entry-two-fields/
Hi Sir, checking if record exists before inseting or updating data how to prevent not exsit Msgbox when i enter a number and it was not exist in the table thank you
really appreciate this video, but please, I need a help. I have a continuous form, where users interact with the database from, now in this form assuming we have two fields, Name and Status the name field should not have duplicate except if that name has be turned COMPLETED on the status. It actually mean, there can be duplicates but only if the first or previous entries in the Status Field have been turned by user into COMPLETED. this is because a process must be completed for the same user before he initiate the second process. I will appreciate to have some help
Dear Austin, Your videos have been really helpful, thanks. However I need some help, because I did everything step by step as it was shown here and the debug popped up stating run-time error 94, Invalid use of null. What could go wrong? It marks the NewCustomer = Me.Customername.Value line
may i ask, what if the customer name is the same and it flags it up, but its actually a different customer. how do i then proceed to enter the new customers data?
Video just show the example for one field. You can add another field like last name to that code. If both first and last name is still the same then you can make them different by adding middle name etc.
Ah I understand. Although what I'm looking for is some thing that would flag any duplicates but ask me if I'd like to continue entering new record and adding the record rather than deleting it.
hello austin i finally did the duplicate check code but it does not allow my edit, update and save other fields it throughs the error the terminal and date has been entered please how what line of code can i add to allow me update or edit records through an already existing record on the form Private Sub Form_BeforeUpdate(Cancel As Integer) Dim NewTerminal As String Dim stLinkCriteria As String Dim dtTimCriteria As String Dim NewDate As String NewTerminal = Me.cboTerID.Value stLinkCriteria = "[serialptrid] = " & "'" & NewTerminal & "'" NewDate = Me.txtDateLogged.Value dtTimCriteria = "[datelogged] = #" & NewDate & "#" If Me.SerialptrID = DLookup("[serialptrid]", "Fault_Log", stLinkCriteria) Then If Me.DateLogged = DLookup("[datelogged]", "Fault_Log", dtTimCriteria) Then MsgBox "This terminal " & NewTerminal & ", " & NewDate & ", has already been entered in this database." _ & vbCr & vbCr & "Please check terminal selected", vbInformation, "Duplicate information" Me.Undo End If End If End Sub
please i have checked the property format there is no field for edit its onlt split datasheet that has allow edit... please in ur code try to update a field n see if it goes through. coz if i remove the Me.undo it goes through
use "And" operator like Dim StrAddress As String NewAddress= Me.CustomerAddress.Value strAddress = "[customerAddress] = " & "'" & NewAddress & "'" If Me.CustomerName = DLookup("[customername]", "tbl_customer", stLinkCriteria) And Me.CustomerAddress = DLookup("[customerAddress]", "tbl_customer", strAddress) ThenThen ....... end if
I haven't test Beforeupdate(). I used Afterupdate() to check after typing new name completed and use Me.undo if name match. This will not enter new name to database if name matches. please review my video again.
austin72406 I'm not really following you on your response to use the "And" operator. Can you clarify what you mean by that please. I would like to check multiple fields within a record and I'm a bit confused. Thank you
just a meaning of And. sometime we have same customer first name, but different last name. if we check duplicate name for first name only, it will be incorrect checking. we have to check first name and last name for that customer. check if both customer first and last name already in database, then we don't want to input that name in database again because it's same person. so we use And such as if this firstname = firstname in customer table And this lastname = lastname in customer table then undo the process.
I hope you can help me. I created a Navigation form and one of the Navigation Tab is related to one query that filter range of dates. But upon placing it in the navigation form it did not work. Can you please help how to make it work or what is the sequence will I have to put in the Expression Builder. Thank you.
the VB or form that used under the Navigation form will be different from a stand alone form. The code now is referenced to Navigation form then your stand alone form.
Does this work if the form is based on a query instead of a single table??? Could this below version work as a formula??? I am having a hard time to get this code working. If DCount("Serial_No", "Customer Database", "Serial_No=" & Me.Serial_No) > 0 Then MsgBox "SN has already been entered", vbInformation, "Duplicate Serial No" Me.Undo End If End Sub
Let me give you a little background on what I want to do. There are events that can be either Live events or Test Events with they both have the same "Event ID" ( a Text data type) and "Test" (a yes/no data type). This is the last part of my code. I have tried using different approaches to get to the right entry, but when it works it only gives me the first entry on the database. Right now I am getting Error 13, Data type mismatch. I'd really appreciate your help!! I'm a total newbie with MSAccess. Thanks, If EventStatusCriteria = True Then custNo = DLookup("[ASL ID]", "Advanced Signal Log", stLinkCriteria And "[Test'] = " True) End If If EventStatusCriteria = False Then custNo = DLookup("[ASL ID]", "Advanced Signal Log", stLinkCriteria) And DLookup("[Test]", "Advanced Signal Log", EventStatusCriteria) End If Me.DataEntry = False DoCmd.FindRecord custNo, , , , , acCurrent = custNo End Sub
two criteria must be inside one Dlookup function for custNo like custNo = DLookup("[ASL ID]", "Advanced Signal Log", stLinkCriteria And EventStatusCriteria) if [ASL ID] is text data type then you have to set Dim custNo as StringI still don't have enough info since you don't provide the whole code for click button. I don't know your code for stLinkCriteria or EventStatusCriteria.
austin72406 This is all the code that I have for this function. [ASL ID] is an integer autogenerated on the table. It is the key element of the table "Advance Signal Log" Private Sub Event_ID_AfterUpdate() ' Variable declaration Dim NewEventID As String Dim stLinkCriteria As String Dim custNo As Integer Dim EventStatusCriteria As Boolean ' Declaring variable values NewEventID = Me.Event_ID.Value stLinkCriteria = "[Event ID] = " & "'" & NewEventID & "'" EventStatusCriteria = False 'Check for duplicate values and instruct to try again If Me.Event_ID = DLookup("[Event ID]", "Advanced Signal Log", stLinkCriteria) And DLookup("[Test]", "Advanced Signal Log", Test = True) Then MsgBox "This Event ID, " & NewEventID & ", already exists in our records as a TEST Event." _ & vbCr & vbCr & "Please check the Event Number and try again.", vbInformation, "Duplicate Event Number" Me.Undo 'undo the process and clear all fields EventStatusCriteria = True End If If Me.Event_ID = DLookup("[Event ID]", "Advanced Signal Log", stLinkCriteria) And Me.TestCHK = False Then MsgBox "This Event ID, " & NewEventID & ", already exists in our records as a Live Event." _ & vbCr & vbCr & "Please check the Event Number and try again.", vbInformation, "Duplicate Event Number" Me.Undo 'undo the process and clear all fields EventStatusCriteria = False End If ' MsgBox "This stLinkCriteria, " & stLinkCriteria & ", already exists in our records." _ ' & vbCr & vbCr & "Please check the Event Number and try again.", vbInformation, "Duplicate Event Number" 'If the Event Number Exist, it will populate the form with its information If EventStatusCriteria = True Then custNo = DLookup("[ASL ID]", "Advanced Signal Log", stLinkCriteria) And DLookup("[Test]", "Advanced Signal Log", Test = True) 'And DLookup("[Test]", "Advanced Signal Log", EventStatusCriteria = True) End If If EventStatusCriteria = False Then custNo = DLookup("[ASL ID]", "Advanced Signal Log", stLinkCriteria) And DLookup("[Test]", "Advanced Signal Log", EventStatusCriteria = False) End If 'MsgBox "This custNo, " & custNo & ", already exists in our records." _ ' & vbCr & vbCr & "Please check the Event Number and try again.", vbInformation, "Duplicate Event Number" Me.DataEntry = False DoCmd.FindRecord custNo, , , , , acCurrent = custNo End Sub Thanks for all your help!!
your code is too complecate. it's not working that way. I think you can use a regular code as shown on my video. I don't think you need to make it separate by Test = true or false. Can one event be both Live and Test? if not just dlook up for EventID.
The event can be both Live and Test. That is the reason that I have to query about two variables. Otherwise it would be a simple Dlook. Do you have any ideas? your help is much appreciated.
Hello @austin72406 thanks for this video its very helpful. But how about if its a number field is the one i dint want to be duplicate its like an example Employee Number..my code is like this Dim NewNumber As String Dim stLinkCriteria As String NewNumber = Me.EmployeeNumber.Value stLinkCriteria = "[EmployeeNumber]= " & "'" & NewNumber & "'" If Me.EmployeeNumber = DLookup("[EmployeeNumber]", "Employees", stLinkCriteria) Then MsgBox "This Employee Number, " & NewNumber & ", has already been entered in the database." _ & vbCr & vbCr & "Please check the Employee Number again.", vbInformation, "Duplicate Employee Number" Me.Undo End If after I run it the error is Run-time error '3464': Data Type mismatch in criteria expression. and its pointing on this line " If Me.EmployeeNumber = DLookup("[EmployeeNumber]", "Employees", stLinkCriteria) Then" Hope you could help me.. :)
yes, data type doesn't match. I guess the data type of EmployeeNumber in the Employees table is number. However, you declare NewNumber as string. it should be NewNumber as integer. and stLinkCriteria = "[EmployeeNumber]= " & NewNumber & ""
Thank you for the response sir @austin72406 I followed your advised now the error is on the line NewNumber = Me.EmployeeNumber.Value is this right or I am missing something? Thanks :)
Very good video. I was able to use the code to detect a duplicate value and open a message box. However, when I added the code to display the original record, it opens all records starting with AutoNumber 1. I cannot figure out why it will not display the specific record. I've checked and double checked the code but can't figure it out. Any help that you can provide would be appreciated.
the video was great and easy to understand but after following this steps i encountered one problem all of my entries was marked as duplicates even if i entered it for the first time any suggestions?
Good video however, after following your steps the code seems to have a big problem. The code counts ALL NEW entries (companies that have NOT been entered in the database at all) as duplicates. Is it possible to advise how to get this to work correctly?
Hello Austin, than you its really amazing learning episode, i did try it, the first part working good m but when i typed VBA codes to show up the existing customer , it goes to show up the first Customer : CustomerNo = DLookup("CustomerID", "tblCustomers", stLinkCriteria) Me.DataEntry = False DoCmd.FindRecord CustomerNo, , , , , acCurrent so I wonder !!!
still getting error here is th complete VBA Codes : Private Sub CustomerCoName_AfterUpdate() Dim NewCustomer As String Dim stLinkCriteria As String Dim CustomerNo As Integer NewCustomer = Me.CustomerCoName.Value stLinkCriteria = "[CustomerCoName]= " & "'" & NewCustomer & "'" If Me.CustomerCoName = DLookup("CustomerCoName", "tblCustomers", stLinkCriteria) Then MsgBox " This Customer " & NewCustomer & " Already in data base " _ & vbCr & vbCr & " Duplicate Info ", vbInformation, "Press OK to go to Customer" Me.Undo End If CustomerNo = DLookup("CustomerID", "tblCustomers", stLinkCriteria) Me.DataEntry = False DoCmd.FindRecord CustomerNo, , , , , acCurrent End Sub
Hi sir, i have a problem when i write this code in number filed the error in "stLinkCriteria" i put it as integer and double it show error also. please help
thanks i solve it by editing some thing in the code this code if the txtbox containing numbers: Private Sub item_no_AfterUpdate() Dim newitem As Double Dim stLinkCriteria As String newitem = Me.item_no.Value stLinkCriteria = "item_no = " & newitem If Me.item_no = DLookup("[item_no]", "items", stLinkCriteria) Then MsgBox " This item , " & newitem & " is already been entred in database " _ & vbCr & vbCr & " please check name again ", vbInformation, "Dublicate error" Me.Undo End If End Sub
Thank you so much Friend, one more request can you make it 3 fields for exp. Date field to check if the same date or different? and if its different continue adding..
Hi Austin, i tried every way but its not working can u see what is the problem in this code Private Sub Form_BeforeUpdate(Cancel As Integer)Dim NewCustomer, NewAddress As String Dim stLinkCriteria As String Dim custNo As Integer Dim Datess As String 'Assign the entered customer name and address to a variableNewCustomer = Me.Iqama.Value NewAddress = Me.Reasons.ValueDatess = Me.Dates.Value stLinkCriteria = "[Iqama] = " & "'" & NewCustomer & "' And [Reasons] = " & "'" & NewAddress & "' And [Dates] = " & "'" & Datess & "'" If Me.Iqama = DLookup("[Iqama]", "tblmain", stLinkCriteria) Then MsgBox "This customer, " & NewCustomer & ", has already been entered in database." _ & vbCr & vbCr & "with addresss " & NewAddress & "" _ & vbCr & vbCr & "Please check customer name and address again.", vbInformation, "Duplicate information" Me.Undo 'undo the process and clear all fields 'show the record of matched customer name and address from the customer table 'custNo = DLookup("[Iqama]", "tblmain", stLinkCriteria) 'Me.DataEntry = False 'DoCmd.FindRecord custNo, , , , , acCurrent End IfEnd Sub
line stLinkCriteria is incorrect. it should be like this: stLinkCriteria = "[Iqama] = '" & NewCustomer & "' And [Reasons] = '" & NewAddress & "' And [Dates] = '" & Datess & "'"
PLEASE HELP ME IN FIXING THIS DCOUNT("[PROJECT_NUMBER]", "[JP_PO_VENDOR_INFO_TABLE]", "[PROJECT_NUMBER] =' " & Me.PROJECT_NUMBER & " ' " And "[PO_NUMBER] = " & Me.PO_NUMBER) USING THIS IN MACRO BUILDER WHERE [PROJECT_NUMBER ] IS STRING AND[ PO_ NUMBER] IS NUMBER
thank you for wonderful video, I am working on a Microsoft -Access database, When I clicked button to show the screen for write code this error appeared(Microsoft access failed to create the visual Basic module)(if you database is on a network drive, check your network connection, and then try gain) how can I fixed it? please advise, how I can alert user when entering duplicate records after update I really need someone to guide me Thank You.
Hi Austin..im just a beginner in ms access but because of your detailed video im become much knowledgeable..thank you :) I just have a little concern, first with your search button video,it runs smoothly on my project but when i forgot to erase my search keyword and navigate to other subform of my navigation form..that keyword adds on my database..is it okay if i put me.undo on exit command of that form? 2nd is about this duplicate tutorial..it works but my problem is whenever i add a new data it creates error.. ORNo = ("[ProcurementID]", "Transmittal", "stLinkCriteria") Thats the code that's been highlightened. Thank you very much and i hope to get some of your advice regarding my problem soo.
Hi Tewan great video. Sometimes one ends up with duplicate entries and data has already been added against both duplicate entries. If you delete one or other entry data is inevitably lost. Can you demonstrate code to show all related data with each duplicate item and then add a button to move all selected data from one duplicate to the other and then delete the empty duplicate?
Hey, well done Austin! I am looking into helping my wife with her Access project and quickly realized there is no Fn way I can do this lol. You did a fantastic job, but this is way beyond my simple abilities. Do you know of someone that we could hire to implement this into our database? Elance maybe? Thanks again!
I don't know anyone. you're right. there is no short cut to learn Access in one day or week. Access is about creating a program. I may contain of 10 of tables, 20 of of forms, 20 of reports, and 1000 of coding. I have learned and practised Access over 10 years myself. I have tried to break it down peice by peice to the individual video. Thanks for watching my video.
Thank you so much, This video save my life. But I still need some help please. My table has autonumber field so even it doesn't entry my duplicate data but the autonumber still counting, So each record that show on my table they are like skipping. could I put some vba code that stop the autonumber counting when the data is not entried ?
is it possible that the database, for exmple, when the computer automatically shut down, all the data i enter will not save automatically. is there a possible code for this?? i hope you can help with my problem. tnx😊
you can add this code on your current form under On Load event like: Private Sub Form_Unload(Cancel As Integer) DoCmd.RunCommand acCmdSaveRecord End Sub
Yes sir that what i mean but i have one field is string and the other is numeric like customer name and telephone numbers how these will be done by using "And" operator, can you clarify it to me by an example i will be thanked sir. Regards
Very useful - I've added this code to my own database for Miles Davis CDs, and it works perfectly.Thank you! How do you get that 'elephant trumpeting' noise when the message box opens?
Thanks Austin..its okay now..i only input data on that table so it will not result into null..thanks for all your videos..it helps a lot on my project.. One thing is my concern..my login form :) It accepts password even if its not the password on my table where the username is..and it accepts the temp password even if i am the admin.
How to prevent duplicate data entry YOU HAVE TO MAKE A COMPOUND INDEX To create a multiple field unique index (Compound index) --Open up the table in design mode --Select View: Index from the menu --Enter a name for the index in first row under Index Name --Select one field in the index under Field Name --Set Unique to Yes --Move down one line and select the next FieldName (Do NOT skip rows, do NOT enter the index name again) --Continue moving down and selecting fieldnames until all needed fields are included. --Close the index window and close and save the table I would still use some code in the entry form to detect the atttempt at duplication, so I could give the user a better "error" message than the one generated by the database.
I want to thank you for posting these videos. They're very helpful. I have a request. What if the user is trying to enter a customer which is not on the database? Can a message box prompt the user to either select a customer that does exist or allow the user to add a new customer via a form. Thank you.
Hi Dear, first of all thank you for this upload which is really helpful, well, could you please guide me that what to do if I want to duplicate that record like in your case the John Smith?
ly samoul YOU HAVE TO MAKE A COMPOUND INDEX To create a multiple field unique index (Compound index) --Open up the table in design mode --Select View: Index from the menu --Enter a name for the index in first row under Index Name --Select one field in the index under Field Name --Set Unique to Yes --Move down one line and select the next FieldName (Do NOT skip rows, do NOT enter the index name again) --Continue moving down and selecting fieldnames until all needed fields are included. --Close the index window and close and save the table I would still use some code in the entry form to detect the atttempt at duplication, so I could give the user a better "error" message than the one generated by the database.
can you please do that for a date field, I cant get rid of type mismatch. Waiting.... Private Sub txtDate__AfterUpdate() Dim Dnew As Date Dim seldate As Date Dim Gid As Long Dnew = Me.txtDate_.Value seldate = "[Date_] = " & "#" & Dnew & "#"
![Removing Duplicate Records - Microsoft Access [ontrackTV video]](http://i.ytimg.com/vi/ko1ma9iBfKA/mqdefault.jpg)
Thank you Austin for taking the time to make this video. I spent several hours trying to get a message box to pop up when a user enters a number that already exists in the database and could not get it to work (looking at books, websites, etc). I took your video and closely followed what your were doing. The only difference was I used an 'on exit' instead of after_update so the user would know right away that number exists already. Thank you for your help and teaching.
I have been working with databases for over 10 years, but just started developing a week ago. This tutorial is the only 1 that actually really helped me verify if a record already exists in the database.
Thank you for this video! #Subscribed
Can we download that database for more practice from somewhere? I would really appreciate it.
please if I may ask, how do iI write a VBA code that prevent me from saving purchase entry twice, example, lets say i have an order form with order IID (primary key)( customer ID(foreign key), product ID (foreign key), price, amount...and i saved an order and should i mistakenly hit the save button it saves again..how do i prevent it from happening so that a message would pop up saying"order id have been saved"
Hi Austin
Thanks for the video.
I have little issue, that duplicate entry pop up works only with one entry for me.
Like, i have plant numbers to be entered. If i enter say 20 and again 20 then it pops up saying duplicate entry. then i enter 30 and enter 30 again the pop up does not pop up and let the entry to take place. at this point if enter 20 again it pops up. And again if i enter 40 as many times as i want, it just accepts it.
Hi Austin i have try this but the code is restricting all the entry. I mean if there is new entry the message popup and i am not able to enter new entry
Is there a way to prevent duplicate records using multiple fields (another field along with name of the customer )? If yes how do I program it?
Single Quote mark is the issue, would you modify the" stLinkCriteria" and post in here please
It says "invalid use of null"..do i have to input all data to that table especially the table which dlookup applies??because some of it doesnt have the OR No..maybe thats the reason why it says invalid use of null..help me please
you're right. If the result of Dlook up is Null then you will get that message.
If I have a First Name and Last Name field instead of one "Customer Name" field, can I still use a similar code to check for duplicates? I'd love to have it do what this tutorial shows by having the pop up say it already exists and then opens that record.
you will need to use And operator to combine the criteria or looking duplicate data for both fields. I have similar example here: www.iaccessworld.com/prevent-duplicate-data-entry-two-fields/
Hi Austin :) im planning to upgrade my ms office to 2013..did my project done at ms access 2010 be affected??
it shouldn't be any problem with 2013.
Hi Sir,
checking if record exists before inseting or updating data
how to prevent not exsit Msgbox when i enter a number and it was not exist in the table
thank you
really appreciate this video, but please, I need a help. I have a continuous form, where users interact with the database from, now in this form assuming we have two fields, Name and Status the name field should not have duplicate except if that name has be turned COMPLETED on the status. It actually mean, there can be duplicates but only if the first or previous entries in the Status Field have been turned by user into COMPLETED. this is because a process must be completed for the same user before he initiate the second process. I will appreciate to have some help
Dear Austin, Your videos have been really helpful, thanks. However I need some help, because I did everything step by step as it was shown here and the debug popped up stating run-time error 94, Invalid use of null. What could go wrong? It marks the NewCustomer = Me.Customername.Value line
may i ask, what if the customer name is the same and it flags it up, but its actually a different customer. how do i then proceed to enter the new customers data?
Video just show the example for one field. You can add another field like last name to that code. If both first and last name is still the same then you can make them different by adding middle name etc.
Ah I understand. Although what I'm looking for is some thing that would flag any duplicates but ask me if I'd like to continue entering new record and adding the record rather than deleting it.
hello austin i finally did the duplicate check code but it does not allow my edit, update and save other fields it throughs the error the terminal and date has been entered please how what line of code can i add to allow me update or edit records through an already existing record on the form
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim NewTerminal As String
Dim stLinkCriteria As String
Dim dtTimCriteria As String
Dim NewDate As String
NewTerminal = Me.cboTerID.Value
stLinkCriteria = "[serialptrid] = " & "'" & NewTerminal & "'"
NewDate = Me.txtDateLogged.Value
dtTimCriteria = "[datelogged] = #" & NewDate & "#"
If Me.SerialptrID = DLookup("[serialptrid]", "Fault_Log", stLinkCriteria) Then
If Me.DateLogged = DLookup("[datelogged]", "Fault_Log", dtTimCriteria) Then
MsgBox "This terminal " & NewTerminal & ", " & NewDate & ", has already been entered in this database." _
& vbCr & vbCr & "Please check terminal selected", vbInformation, "Duplicate information"
Me.Undo
End If
End If
End Sub
this code has nothing to do with edit or update. Please check your form format. maybe the form has a format of edition set to no etc.
please i have checked the property format there is no field for edit its onlt split datasheet that has allow edit... please in ur code try to update a field n see if it goes through.
coz if i remove the Me.undo it goes through
How do you check multi-fields for duplicate records?
For example. I want the code to check customer name, address, and zip for duplicate record.
use "And" operator like
Dim StrAddress As String
NewAddress= Me.CustomerAddress.Value
strAddress = "[customerAddress] = " & "'" & NewAddress & "'"
If Me.CustomerName = DLookup("[customername]", "tbl_customer", stLinkCriteria) And Me.CustomerAddress = DLookup("[customerAddress]", "tbl_customer", strAddress) ThenThen
....... end if
Can you put this code in beforeupdate() instead of afterupdate()? I don't want the data write into the table before validation of duplicate.
I haven't test Beforeupdate(). I used Afterupdate() to check after typing new name completed and use Me.undo if name match. This will not enter new name to database if name matches. please review my video again.
austin72406 I'm not really following you on your response to use the "And" operator. Can you clarify what you mean by that please. I would like to check multiple fields within a record and I'm a bit confused. Thank you
just a meaning of And. sometime we have same customer first name, but different last name. if we check duplicate name for first name only, it will be incorrect checking. we have to check first name and last name for that customer. check if both customer first and last name already in database, then we don't want to input that name in database again because it's same person. so we use And such as if this firstname = firstname in customer table And this lastname = lastname in customer table then undo the process.
Dude any value (whether it exists or not) I enter
I get this message.
What am I doing wrong ?
I hope you can help me. I created a Navigation form and one of the Navigation Tab is related to one query that filter range of dates. But upon placing it in the navigation form it did not work. Can you please help how to make it work or what is the sequence will I have to put in the Expression Builder. Thank you.
the VB or form that used under the Navigation form will be different from a stand alone form. The code now is referenced to Navigation form then your stand alone form.
Thanks you very much Mr.Austin. I watch your videos & learned lot of things. I much grateful to you for kindly share your knowledge generously.......
you're welcome
Does this work if the form is based on a query instead of a single table??? Could this below version work as a formula??? I am having a hard time to get this code working.
If DCount("Serial_No", "Customer Database", "Serial_No=" & Me.Serial_No) > 0 Then
MsgBox "SN has already been entered", vbInformation, "Duplicate Serial No"
Me.Undo
End If
End Sub
Let me give you a little background on what I want to do. There are events that can be either Live events or Test Events with they both have the same "Event ID" ( a Text data type) and "Test" (a yes/no data type).
This is the last part of my code. I have tried using different approaches to get to the right entry, but when it works it only gives me the first entry on the database.
Right now I am getting Error 13, Data type mismatch. I'd really appreciate your help!!
I'm a total newbie with MSAccess.
Thanks,
If EventStatusCriteria = True Then
custNo = DLookup("[ASL ID]", "Advanced Signal Log", stLinkCriteria And "[Test'] = " True)
End If
If EventStatusCriteria = False Then
custNo = DLookup("[ASL ID]", "Advanced Signal Log", stLinkCriteria) And DLookup("[Test]", "Advanced Signal Log", EventStatusCriteria)
End If
Me.DataEntry = False
DoCmd.FindRecord custNo, , , , , acCurrent = custNo
End Sub
two criteria must be inside one Dlookup function for custNo like custNo = DLookup("[ASL ID]", "Advanced Signal Log", stLinkCriteria And EventStatusCriteria)
if [ASL ID] is text data type then you have to set Dim custNo as StringI still don't have enough info since you don't provide the whole code for click button. I don't know your code for stLinkCriteria or EventStatusCriteria.
austin72406
This is all the code that I have for this function. [ASL ID] is an integer autogenerated on the table. It is the key element of the table "Advance Signal Log"
Private Sub Event_ID_AfterUpdate()
' Variable declaration
Dim NewEventID As String
Dim stLinkCriteria As String
Dim custNo As Integer
Dim EventStatusCriteria As Boolean
' Declaring variable values
NewEventID = Me.Event_ID.Value
stLinkCriteria = "[Event ID] = " & "'" & NewEventID & "'"
EventStatusCriteria = False
'Check for duplicate values and instruct to try again
If Me.Event_ID = DLookup("[Event ID]", "Advanced Signal Log", stLinkCriteria) And DLookup("[Test]", "Advanced Signal Log", Test = True) Then
MsgBox "This Event ID, " & NewEventID & ", already exists in our records as a TEST Event." _
& vbCr & vbCr & "Please check the Event Number and try again.", vbInformation, "Duplicate Event Number"
Me.Undo 'undo the process and clear all fields
EventStatusCriteria = True
End If
If Me.Event_ID = DLookup("[Event ID]", "Advanced Signal Log", stLinkCriteria) And Me.TestCHK = False Then
MsgBox "This Event ID, " & NewEventID & ", already exists in our records as a Live Event." _
& vbCr & vbCr & "Please check the Event Number and try again.", vbInformation, "Duplicate Event Number"
Me.Undo 'undo the process and clear all fields
EventStatusCriteria = False
End If
' MsgBox "This stLinkCriteria, " & stLinkCriteria & ", already exists in our records." _
' & vbCr & vbCr & "Please check the Event Number and try again.", vbInformation, "Duplicate Event Number"
'If the Event Number Exist, it will populate the form with its information
If EventStatusCriteria = True Then
custNo = DLookup("[ASL ID]", "Advanced Signal Log", stLinkCriteria) And DLookup("[Test]", "Advanced Signal Log", Test = True) 'And DLookup("[Test]", "Advanced Signal Log", EventStatusCriteria = True)
End If
If EventStatusCriteria = False Then
custNo = DLookup("[ASL ID]", "Advanced Signal Log", stLinkCriteria) And DLookup("[Test]", "Advanced Signal Log", EventStatusCriteria = False)
End If
'MsgBox "This custNo, " & custNo & ", already exists in our records." _
' & vbCr & vbCr & "Please check the Event Number and try again.", vbInformation, "Duplicate Event Number"
Me.DataEntry = False
DoCmd.FindRecord custNo, , , , , acCurrent = custNo
End Sub
Thanks for all your help!!
your code is too complecate. it's not working that way. I think you can use a regular code as shown on my video. I don't think you need to make it separate by Test = true or false. Can one event be both Live and Test? if not just dlook up for EventID.
The event can be both Live and Test. That is the reason that I have to query about two variables. Otherwise it would be a simple Dlook. Do you have any ideas? your help is much appreciated.
Hello @austin72406 thanks for this video its very helpful. But how about if its a number field is the one i dint want to be duplicate its like an example Employee Number..my code is like this
Dim NewNumber As String
Dim stLinkCriteria As String
NewNumber = Me.EmployeeNumber.Value
stLinkCriteria = "[EmployeeNumber]= " & "'" & NewNumber & "'"
If Me.EmployeeNumber = DLookup("[EmployeeNumber]", "Employees", stLinkCriteria) Then
MsgBox "This Employee Number, " & NewNumber & ", has already been entered in the database." _
& vbCr & vbCr & "Please check the Employee Number again.", vbInformation, "Duplicate Employee Number"
Me.Undo
End If
after I run it the error is Run-time error '3464':
Data Type mismatch in criteria expression.
and its pointing on this line " If Me.EmployeeNumber = DLookup("[EmployeeNumber]", "Employees", stLinkCriteria) Then"
Hope you could help me.. :)
yes, data type doesn't match. I guess the data type of EmployeeNumber in the Employees table is number.
However, you declare NewNumber as string. it should be NewNumber as integer.
and stLinkCriteria = "[EmployeeNumber]= " & NewNumber & ""
Thank you for the response sir @austin72406 I followed your advised now the error is on the line
NewNumber = Me.EmployeeNumber.Value is this right or I am missing something?
Thanks :)
Very good video. I was able to use the code to detect a duplicate value and open a message box. However, when I added the code to display the original record, it opens all records starting with AutoNumber 1. I cannot figure out why it will not display the specific record. I've checked and double checked the code but can't figure it out. Any help that you can provide would be appreciated.
if there is not filter on record, the program will open form or subform with all record starting with record 1 on display.
the video was great and easy to understand but after following this steps i encountered one problem all of my entries was marked as duplicates even if i entered it for the first time any suggestions?
Good video however, after following your steps the code seems to have a big problem. The code counts ALL NEW entries (companies that have NOT been entered in the database at all) as duplicates. Is it possible to advise how to get this to work correctly?
Please check your code again. I have the How To at my website here: www.iaccessworld.com/prevent-duplicated-data-entry/
how do check a record exists from more than one field ,> eg;- customer and address pl
use And operator between field similar to search on more than one field.
I have the How To at my website here: www.iaccessworld.com/prevent-duplicate-data-entry-two-fields/
Hello Austin, than you its really amazing learning episode, i did try it, the first part working good m but when i typed VBA codes to show up the existing customer , it goes to show up the first Customer :
CustomerNo = DLookup("CustomerID", "tblCustomers", stLinkCriteria)
Me.DataEntry = False
DoCmd.FindRecord CustomerNo, , , , , acCurrent
so I wonder !!!
you need to set the stLinkCriteria for your Dlookup
for example,
stLinkCriteria = "CustomerID = " & Me.CustomerID & ""
still getting error here is th complete VBA Codes :
Private Sub CustomerCoName_AfterUpdate()
Dim NewCustomer As String
Dim stLinkCriteria As String
Dim CustomerNo As Integer
NewCustomer = Me.CustomerCoName.Value
stLinkCriteria = "[CustomerCoName]= " & "'" & NewCustomer & "'"
If Me.CustomerCoName = DLookup("CustomerCoName", "tblCustomers", stLinkCriteria) Then
MsgBox " This Customer " & NewCustomer & " Already in data base " _
& vbCr & vbCr & " Duplicate Info ", vbInformation, "Press OK to go to Customer"
Me.Undo
End If
CustomerNo = DLookup("CustomerID", "tblCustomers", stLinkCriteria)
Me.DataEntry = False
DoCmd.FindRecord CustomerNo, , , , , acCurrent
End Sub
it should be like:
stLinkCriteria = "[CustomerCoName]= '" & NewCustomer & "'"
Can you tell me how to apply your code to the number text field
assign Dim intNumber as integer
intNumber = me.textField.value
then compare similar method
Hi sir,
i have a problem when i write this code in number filed the error in "stLinkCriteria" i put it as integer and double it show error also.
please help
can you show your code for line of strLinkCriteria
thanks i solve it by editing some thing in the code
this code if the txtbox containing numbers:
Private Sub item_no_AfterUpdate()
Dim newitem As Double
Dim stLinkCriteria As String
newitem = Me.item_no.Value
stLinkCriteria = "item_no = " & newitem
If Me.item_no = DLookup("[item_no]", "items", stLinkCriteria) Then
MsgBox " This item , " & newitem & " is already been entred in database " _
& vbCr & vbCr & " please check name again ", vbInformation, "Dublicate error"
Me.Undo
End If
End Sub
Sorry i what another Help:
i want to copy data from txtbox1 to txtbox2 in the same form.
please help
Hi, how to operator for multiple fields? please help?
I have the How To here:
How to prevent duplicate data entry for two fields
www.iaccessworld.com/prevent-duplicate-data-entry-two-fields/
Thank you so much Friend, one more request can you make it 3 fields for exp. Date field to check if the same date or different? and if its different continue adding..
Hi Austin, i tried every way but its not working can u see what is the problem in this code
Private Sub Form_BeforeUpdate(Cancel As Integer)Dim NewCustomer, NewAddress As String
Dim stLinkCriteria As String
Dim custNo As Integer
Dim Datess As String
'Assign the entered customer name and address to
a variableNewCustomer = Me.Iqama.Value
NewAddress = Me.Reasons.ValueDatess = Me.Dates.Value
stLinkCriteria = "[Iqama] = " & "'"
& NewCustomer & "' And [Reasons] = " & "'"
& NewAddress & "' And [Dates] = " & "'" &
Datess & "'"
If Me.Iqama = DLookup("[Iqama]",
"tblmain", stLinkCriteria) Then MsgBox "This customer, " &
NewCustomer & ", has already been entered in database." _
& vbCr & vbCr & "with addresss " & NewAddress &
"" _
& vbCr & vbCr & "Please check customer name and address
again.", vbInformation, "Duplicate information" Me.Undo 'undo the
process and clear all fields
'show the record of matched customer name and
address from the customer table
'custNo = DLookup("[Iqama]",
"tblmain", stLinkCriteria)
'Me.DataEntry = False
'DoCmd.FindRecord custNo, , , , ,
acCurrent
End IfEnd Sub
line stLinkCriteria is incorrect.
it should be like this:
stLinkCriteria = "[Iqama] = '" & NewCustomer & "' And [Reasons] = '" & NewAddress & "' And [Dates] = '" & Datess & "'"
the correct on Date field should be like this:
Datess = "#" & Me.Dates & "#"
PLEASE HELP ME
IN FIXING THIS
DCOUNT("[PROJECT_NUMBER]", "[JP_PO_VENDOR_INFO_TABLE]", "[PROJECT_NUMBER] =' " & Me.PROJECT_NUMBER & " ' " And "[PO_NUMBER] = " & Me.PO_NUMBER)
USING THIS IN MACRO BUILDER
WHERE [PROJECT_NUMBER ] IS STRING AND[ PO_ NUMBER] IS NUMBER
thank you for wonderful video,
I am working on a Microsoft -Access database, When I clicked button to show the screen for write code this error appeared(Microsoft access failed to create the visual Basic module)(if you database is on a network drive, check your network connection, and then try gain) how can I fixed it?
please advise, how I can alert user when entering duplicate records after update
I really need someone to guide me
Thank You.
Brilliant, this totally solved my problem!! Thanks SO MUCH!!
Hi ,
can u tell me what is "tblcustomer"
Table Customer - a type of Customers Table or record of your customers
What about multiple fields?
you can use and operator for multiple fields.
Hi Austin..im just a beginner in ms access but because of your detailed video im become much knowledgeable..thank you :)
I just have a little concern, first with your search button video,it runs smoothly on my project but when i forgot to erase my search keyword and navigate to other subform of my navigation form..that keyword adds on my database..is it okay if i put me.undo on exit command of that form?
2nd is about this duplicate tutorial..it works but my problem is whenever i add a new data it creates error..
ORNo = ("[ProcurementID]", "Transmittal", "stLinkCriteria")
Thats the code that's been highlightened.
Thank you very much and i hope to get some of your advice regarding my problem soo.
Hi Tewan great video. Sometimes one ends up with duplicate entries and data has already been added against both duplicate entries. If you delete one or other entry data is inevitably lost. Can you demonstrate code to show all related data with each duplicate item and then add a button to move all selected data from one duplicate to the other and then delete the empty duplicate?
Hey, well done Austin! I am looking into helping my wife with her Access project and quickly realized there is no Fn way I can do this lol. You did a fantastic job, but this is way beyond my simple abilities. Do you know of someone that we could hire to implement this into our database? Elance maybe? Thanks again!
I don't know anyone. you're right. there is no short cut to learn Access in one day or week. Access is about creating a program. I may contain of 10 of tables, 20 of of forms, 20 of reports, and 1000 of coding. I have learned and practised Access over 10 years myself. I have tried to break it down peice by peice to the individual video. Thanks for watching my video.
Thank you so much, This video save my life. But I still need some help please. My table has autonumber field so even it doesn't entry my duplicate data but the autonumber still counting, So each record that show on my table they are like skipping. could I put some vba code that stop the autonumber counting when the data is not entried ?
is it possible that the database, for exmple, when the computer automatically shut down, all the data i enter will not save automatically. is there a possible code for this?? i hope you can help with my problem. tnx😊
you can add this code on your current form under On Load event like:
Private Sub Form_Unload(Cancel As Integer)
DoCmd.RunCommand acCmdSaveRecord
End Sub
austin72406 thank you very much.. 😊😊
i hope you can do also a video that inactivate the current form when user not use the system. tnx by the way.
you can use AutoExe macro to open a specific form when program is loaded and hidden then you put the code above under the On Unload form.
Hi Sir,
I applied these code it's worked perfectly but if i want to appear these code for duplicate two records how these code will changed.
Regards
did you mean for two fields like Customer name and Address? if yes, it will need "And" operator for both field.
test on both fields.
Yes sir that what i mean but i have one field is string and the other is numeric like customer name and telephone numbers how these will be done by using "And" operator, can you clarify it to me by an example i will be thanked sir.
Regards
Please see a new How To at my website for an idea:
www.iaccessworld.com/prevent-duplicate-data-entry-two-fields/
Very useful - I've added this code to my own database for Miles Davis CDs, and it works perfectly.Thank you! How do you get that 'elephant trumpeting' noise when the message box opens?
Great Work Sir👍👍
Thanks Austin..its okay now..i only input data on that table so it will not result into null..thanks for all your videos..it helps a lot on my project..
One thing is my concern..my login form :)
It accepts password even if its not the password on my table where the username is..and it accepts the temp password even if i am the admin.
Hi Austin
Thanks for the video. It's work..
HEY this video was awesome i need helo with error 94 im able to see the duplicates but im not able to input any new data, it says invalid use of null
Fantastic video, Many thanks for sharing
How to prevent duplicate data entry YOU HAVE TO MAKE A COMPOUND INDEX
To create a multiple field unique index (Compound index)
--Open up the table in design mode
--Select View: Index from the menu
--Enter a name for the index in first row under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
(Do NOT skip rows, do NOT enter the index name again)
--Continue moving down and selecting fieldnames until all needed fields are included.
--Close the index window and close and save the table
I would still use some code in the entry form to detect the atttempt at duplication, so I could give the user a better "error" message than the one generated by the database.
I want to thank you for posting these videos. They're very helpful. I have a request. What if the user is trying to enter a customer which is not on the database? Can a message box prompt the user to either select a customer that does exist or allow the user to add a new customer via a form. Thank you.
you con do it on the Login form. for example, add a link to add new user on Add User Form.
Hi Dear, first of all thank you for this upload which is really helpful, well, could you please guide me that what to do if I want to duplicate that record like in your case the John Smith?
you can make it different by adding Initial or Middle name or some thing
This is a very good program. Thanks
your tutorial is clear but it doesn't work for me! so sad
ly samoul YOU HAVE TO MAKE A COMPOUND INDEX
To create a multiple field unique index (Compound index)
--Open up the table in design mode
--Select View: Index from the menu
--Enter a name for the index in first row under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
(Do NOT skip rows, do NOT enter the index name again)
--Continue moving down and selecting fieldnames until all needed fields are included.
--Close the index window and close and save the table
I would still use some code in the entry form to detect the atttempt at duplication, so I could give the user a better "error" message than the one generated by the database.
Thanks! It worked for me
Excelente me ha funcionado de maravilla
Thank you so much!
Thanks austin :)
Thank YOUUUUUUUUUUUUUUUU
can you please do that for a date field, I cant get rid of type mismatch. Waiting....
Private Sub txtDate__AfterUpdate()
Dim Dnew As Date
Dim seldate As Date
Dim Gid As Long
Dnew = Me.txtDate_.Value
seldate = "[Date_] = " & "#" & Dnew & "#"