Forms :: Message Box To Confirm Entry Of A Duplicate Value?
Oct 24, 2013
I have a single table in my database and I have to make a form for entering contact details for clients, staff, funding bodies and everyone else into this table. There is a phone number and what code I would use the get it to check if the number already exists.
If it does, I wanted it to return a custom message telling you what entries it already exists for by displaying certain fields so that they can be identified, such as name and organisation, but then I wanted the custom message to allow you to confirm entry of the data anyway. This is because there may have been an error in entry or as some people may be contacted on the same phone number, I want it to allow the option to continue and enter the data anyway.
I have struggled with this for so long now I hope someone can help. I have a form called frm_Collections which is run from the query (qry_Collections). When I type into the Reg field in the Form I would like the user to know if the Reg details have already been used in a previous record. The database can have duplicates in this field, however a "Warning Message" would promt some investigation. I have tried loads of DLookup on Before Update, but really need setting of in the right direction. Thanks
I have a form which is based on student records. What I'm trying to get Access to do is to bring up an error message as soon a duplicate student number has been entered. At present I've managed to replace the Access standard error message 3022 with my own by using the following code:
Private Sub Form_Error(DataErr As Integer, Response As Integer) Const conDuplicateKey = 3022 Dim strMsg As String
If DataErr = conDuplicateKey Then Response = acDataErrContinue strMsg = "Duplicated record. Please recheck your data." MsgBox strMsg End If End Sub
However the error message only appears when you try to leave the form (e.g. start a new record). I would like the error message to appear as soon as the user leaves the Student Number text box on the form. Is there anyway to do this?
So now, I'm using the code below to prevent duplicate name entry and it is working great - EXCEPT when I enter a first or last name which contains a ' (ie, O'Tool, O'Malley, O'Hern)...anyone got any ideas for me on how to make this not happen?
The error I get is:
Run Time error '3075'
Syntax error (missing operator) in query expression '[Last Name]='O'Hern' And [First Name]='Lori'.
The code i'm using is:
Private Sub Last_Name_AfterUpdate()
'Check for duplicate first and last name using DCount
If DCount("*", "[Constituents]", "[Last Name]= '" & Me![Last Name] & "' And [First Name] = '" & Me![First Name] & "'") > 0 Then Beep MsgBox "This first and last name already exists in the database. Please check that you are not entering a duplicate constituent before continuing.", vbOKOnly, "Duplicate Value" Cancel = True End If
I was using the following code on a field (ItemCode) that was indexed to prevent duplication of records. The intent is that the user will get an error message that a duplicate exists before they enter all the data for the record and get the built in error message that Access 2003 provides when an index violation has occurred.
Code: Private Sub ItemCode_BeforeUpdate(Cancel As Integer) Dim Answer As Variant Answer = DLookup("[ItemCode]", "tblQuestions", "[ItemCode] = '" & Me.ItemCode & "'") If Not IsNull(Answer) Then MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate" Cancel = True Me.ItemCode.Undo Else: End If End Sub
Now, the index for this is based on two fields (ItemCode and Question Group). I would like to display the same message before update but don't know how to include the second field in the syntax.
I would like to have an update (requery) button on my form, that when clicked opens a message box that asks "ARE YOU SURE YOU WANT TO UPDATE?", if yes, a yes button is pushed, which requeries the record and closes the message box. If a no button is pushed, the message box is closed without requerying the record.
I have tried the code below, but I am not getting what I need. I have a table [Student Info] with a field [Student ID] as the primary key and many other fields. I added the Student ID field to my form, [Student Add]. When I input an ID that already exists I get an error code as expected but then my form still loads. I currently have a second form for updating student info [Student Update] for which I have a parameter form that asks for the Student ID. I would like to use just one form to add and update students if possible. So, if you add a new Student ID you can enter all pertinent data. However, if you enter a duplicate Student ID the form should populate with that student's information.
If I must have two forms (1 to add and 1 to update) and a duplicate Student ID is entered into the Add form, the user should get a message stating that the record already exists. Then, (Option 1) force the Add form to close without saving or (Option 2) give an option for opening the Update form. Either option is acceptable at this point because I am stuck.
When attempting to use the code below, I get a data mismatch error on the "Answer = DLookUp......" line.
Here's one I use on Social Security numbers for a pop up when a duplicate.... SS# is stored in table "tblApplicant"
Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer) Dim Answer As Variant Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'") If Not IsNull(Answer) Then MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
I have a data entry form linked to a table that records daily catch details (date, invoice number, buyer, kilos, price, etc). For the last 2 years I've been using the no duplicate index on the date field of the table to prevent duplicates, until he (my husband) decided that he wanted to sell his catch to different buyers on the same date.
I've changed the index to allow duplicates but now I need to create a message box which warns that he is entering a duplicate date as he has a history of trying to enter the same info twice. I've found examples of code that warns on duplicate text entries but nothing for duplicate dates?
I want to stop duplicate entries from being entered on form. I have read through the thread , however I am totally confused as it seemed to be v high level complex queries. I am looking at:
Preventing duplicate entries to be entered
It should show an error "Saying entry already exists" Do you want to check,edit or add new...
I am looking at the public function routine, that validates the password entry. I want to know how i can make a message pop up with the specific error the user has made on entry.
Public Function ValidatePwd(varPassword As Variant) As Boolean Dim blnValid As Boolean Dim blnValidCriteria As Boolean Dim intChar As Integer
I have following Fields. Allocation ID(PK), HW ID, User ID, HW Status, Assigned Date.
Values of HW Status = Active, Inactive, Decom, In Stock etc.
What I want is explaind below example
HW XXX is allocated to User ABC and the Status is Active.
Now When I want to reallocate the same HW XXX to Another User XYZ then Make the current status as Active. But before doing that the form should force me to change the Status of previous allocated to user ABC to anything other than active. Because One Device can be active to only one user at a time.
i,e Combination of HW ID & HW Status(Active Only) should not be duplicated.
table name : Schedule Field 1 = Vessel code Field 2 = Voyage Field 3 = ETA Field 4 = berthed Field 5 = Sailed
there is a query by using above table and data entry form based on that query.
need to add following facilitate
While data entering, if given voyage number is already exist for the particular vessel code, msg should be pop up immediately at that time saying " This voyage number is already exist"
I tried using a message box to try and validate text entry on a form into a bound txt box 'PaymentAmount', If there is no payment entered, using the properties box Validation Rule ,- 'is not null' then entered a validation text ="Please enter an Amount" but this does not work. How can I get a message box to appear if someone leaves the entry field empty so that they have to make some entry to move on. I have already entered this in the table validation field as well as 'Required - yes', but the message is an Access one not my own. Where or how do I do this ?
the below scrip was taken from another forum member.
i have put this in the Forms / error VBA
but i still a little lost
i have set 3 fields to be indexed not to allow duplicate information (Forename, Surename, DOB)
i was then advised to error trap so it would highlight that i have enterd a duplicate record on typing in the DOB (As that as third feild to be entered)
so i then used the below script, however this doesnt highlight that it is a duplicate it just doesnt let me go on to the next record, the only time the error message is highlighted is when i go into design mode.
anyideas on where to go from here.
Private Sub Form_Error(DataErr As Integer, Response As Integer) If DataErr = 3022 Then Response = acDataErrContinue MsgBox "Tis MerID alredy exist!" Exit Sub End If If DataErr = 2169 Then Response = acDataErrContinue MsgBox "Tis record will not be saved!" Exit Sub End If
I have a Timesheet form where you select your name from a combo, and you select a Timesheet Period from another combo. Then you can enter various details in the sub-forms.
I want to prevent a user from being able to enter two timesheets for the same period. What's the best way of achieving this? I would like a pop-up to appear saying "You have already started a timesheet for this week. Please use the Update Form Dialog"
The timesheet form is bound to tblTimesheets which contains fields TimsheetID, StaffID, and TimesheetPeriodID
In cboNameSelect, StaffID is bound In cboPeriodSelect, TimesheetPeriodID is bound.
I have a form - see attached image. The first text box is called Job_No. When a number is entered here how can I check that the number doesn't already exist when the user tabs to the next box. Then if it does exist display a custom message to the effect "This already exists" and not the Access default duplicate error message.
How can I change the default message (as you can see in the picture) that appear every time that I try to update my record?
I want to write something like "You Have to select a different Number"
And one more question, the message only appear when I'm pressing the record selectorbutton only after the user has already filled all the form) is there a possibility that I can write a code that will check for duplicate data immediately after the user typed it?
I currently have a field on a form thats indexed (no duplicate values). What I want, is if the user types a existing social in the field, that it lets them know the social is in use and that the record will not save.
I believe this will have to use the before update event for my pSocialSecurity control and that I will need to use the DCount function.
I'd like a confirm dialog to appear on button click, so when the user clicks 'yes', the on click event should continue and when the user click 'no', the event should stop.
Have read through all the threads related to this topic and can't find anything to help. Just want a message box to pop up after entry of first and last names in a form if the string already exists in the database. DLookup works wonderfully for validating on one field, but adding the second field is driving me crazy - Can I use DLookup to check more than one field? The following code gives me the error: "Run time error '2001' - You canceled the previous operation."
If Not IsNull(x) Then Beep MsgBox "This name already exists in the database. Please check that you are not entering a duplicate person before continuing.", vbOKOnly, "Duplicate Value" Cancel = True End If
I have a Form based on a table of current Players in a football league. The Form is used to edit, but mainly add new registrations. The fields are...
Surname FirstName Address Suburb Age Club YearRegd
Is it possible in some way to have an alert or message come up stating that the Player is already registered if the Table already contains a player with the same Surname and FirstName only. This would need to happen after the FirstName has been entered as it is the second field after 'Surname'. I'm not sure if Access can do this type of function or not.
How can I create an alert message to tell the user that there are an existing work order number in the table? I don't want it to restrict the entry, just inform the user that there is an existing work order number, do they want to continue.
Btw, the code will be attached to a combo box (cbowonum).
I would like to create a custom pop-up Message when one attempts to add a duplicate record.
Currently, I have a default pop-up message when you try to add a duplicate record. I set the field as Indexed= yes(No Duplicates) which generates a default pop-up message but it is technical and wordy. I would like to be able to simplify the wording to just let the user know not to add a duplicate record.
