Error Message On Creating Duplicate Record (1 Viewer)

[Gaddy]

Hi,

I need to be able to produce an error on a form where the user attempts to create a new user that already exists. The error needs to be produced when the save button is clicked.

Please see below for the existing code on my save button which exists in form, 'frmAddEditUser':

Private Sub cmdSave_Click()
        
    If IsNull(cboManagerID) = True Then
        MsgBox "Please select a manager", vbExclamation, "Details Missing!"
        Me.cboManagerID.Requery
    Else

    MsgBox "User details saved", vbInformation, "Success!"
    DoCmd.Close acForm, "frmAddEditUser", acSaveYes
    DoCmd.OpenForm "frmUserManagement"
    
    End If

End Sub

Currently, if I attempt to create a user with the same Payroll ID as an existing user, my 'User details saved' message box is displayed and to the user it looks like a new record has saved successfully which isn't the case.

I need a message box to appear displaying something like "Payroll ID n already exists" where n is the 'PayrollID' of the duplicate record. (Is it possible to combine a value from a certain control and ones own custom text?)

Note: PayrollID is a combination of letters and numbers, i.e. two letters, followed by 5 numbers, e.g. JD12345.

For reference, the table/field and and control are called, '[tblUser].[PayrollID]' and 'txtPayrollID' (on 'frmAddEditUser') respectively; it is from this field and control that I want the error to be produced if the user is entering the same values which are existing in 'tblUser'.

Many thanks.

 

[CJ_London]

If you modify your table design to not allow duplicates for payrollID then access will do this for you

 

[michaeljryan78]

You can use this to retrn a True\False and call it:

Public Function PayrollIDExists(Optional PayrollID As String) As Boolean
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Dim strSQL As String
    User = GetUserName
 
    strSQL = "select payrollID from tblusers"
    Set rs = db.OpenRecordset(strSQL)
 
    rs.FindFirst payrollID = " & "'" & payrollID & "'"
 
    If rs.NoMatch = False Then
        PayrollIDExists= True
    Else
        PayrollIDExists= False
    End If
 
        rs.Close
        Set rs = Nothing
End Function

 

[Gaddy]

If you modify your table design to not allow duplicates for payrollID then access will do this for you

Hi,

The table is already set up in this manner.

 

[Gaddy]

rs.FindFirst payrollID = " & "[B]'" & payrollID & "'"[/B]

Hi,

The bold section above seems to comment out...

Where do I put this code (I've temporarily put it under the form) and do I have to do anything else with it?

 

[Adam McReynolds]

Hi,

I need to be able to produce an error on a form where the user attempts to create a new user that already exists. The error needs to be produced when the save button is clicked.

Please see below for the existing code on my save button which exists in form, 'frmAddEditUser':

Private Sub cmdSave_Click()
        
    If IsNull(cboManagerID) = True Then
        MsgBox "Please select a manager", vbExclamation, "Details Missing!"
        Me.cboManagerID.Requery
    Else

    MsgBox "User details saved", vbInformation, "Success!"
    DoCmd.Close acForm, "frmAddEditUser", acSaveYes
    DoCmd.OpenForm "frmUserManagement"
    
    End If

End Sub

Currently, if I attempt to create a user with the same Payroll ID as an existing user, my 'User details saved' message box is displayed and to the user it looks like a new record has saved successfully which isn't the case.

I need a message box to appear displaying something like "Payroll ID n already exists" where n is the 'PayrollID' of the duplicate record. (Is it possible to combine a value from a certain control and ones own custom text?)

Note: PayrollID is a combination of letters and numbers, i.e. two letters, followed by 5 numbers, e.g. JD12345.

For reference, the table/field and and control are called, '[tblUser].[PayrollID]' and 'txtPayrollID' (on 'frmAddEditUser') respectively; it is from this field and control that I want the error to be produced if the user is entering the same values which are existing in 'tblUser'.

Many thanks.

Here is something that uses DCount to see if there is already a record:

'Will Not Check a Null Field
If Not IsNull(textbox) Then

'Will Check There is Already a Record
If DCount("Field", "TABLE", "Field = '" & Textbox & "'") = 1 Then
MsgBox "If there is already a record then this message fires "
Me.textbox.SetFocus
Cancel = True
Exit Sub
End If
End If

If you want to see if something has been entered in any record like a name you can use DLookup:

If DLookup("Field", "Table", "Field = " & Me.textbox.Value & "") = "NAME" Then......

Hope this helps in some way but there are better qualified people on here. Just trying to give back in some way.

 

[missinglinq]

If you modify your table design to not allow duplicates for payrollID then access will do this for you

Actually, it won't if you're using

DoCmd.Close

to close the Form!

It has long been recommended that the code

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

be inserted before using

DoCmd.Close

to close a Form because of a quirk in Access. When DoCmd.Close is used, Access closes the Form regardless of whether or not a PK field has been duplicated, a PK field or other Required Field has been left blank or validation rules have been violated!

If one of these things occur, Access will simply dump the Record, Close the Form, and not tell the user that the Record has been dumped!

The code If Me.Dirty Then Me.Dirty = False or DoCmd.RunCommand acCmdSaveRecord forces Access to attempt to Save the Record, and if a violation has occurred, will throw up a warning message, allowing correction to be made before Closing the Form.

You need to change your

DoCmd.Close

to

If Me.Dirty Then Me.Dirty = False

DoCmd.Close

or

DoCmd.RunCommand acCmdSaveRecord

DoCmd.Close

And, FYI

acSaveYes

has nothing to do with saving a Record, it refers to saving Design Changes that have been made to an Object, in this case, to the Form.

Linq ;0)>

 

[michaeljryan78]

Good point linq. Its always the little things. This may be the best sugesstion.

 

[Gaddy]

snip

Thanks, this worked for me.

I need to trap the run time error that occurs. Reference my OP, how do I get a message box to appear displaying something like "Payroll ID n already exists" where n is the 'PayrollID' of the duplicate record. (Is it possible to combine a value from a certain control and ones own custom text?)

 

[michaeljryan78]

msgbox "Payroll ID " & me.payrollID & "already exsits."

 

[Gaddy]

msgbox "Payroll ID " & me.payrollID & "already exsits."

That doesn't appear to work... Run-Time error 2467 The Expression you entered refers to an object that is closed or doesn't exist.

EDIT: Ignore that, it was because the 'docmd.close' was before the 'MsgBox' part.