Record Validation

[WillJ]

Hi All,

Hope every one is well and thank you muchly in advanced for your help.

I'm entering company information into my form and I want to write some validation that searches my company name field in my company table and does not all the user to enter the same name twice. The company name is a string value obviously so I'm not 100% on how to do it ... is it as simply as ... < = "CompanyName" cause that isn't working!

If the user attempts to a MsgBox is displayed that says' Duplicate company name, please check that this company has not been input before. If this is a new company please insert (RegionName&Date) i.e. (Liverpool.28) after the company and re-save.

 

[noboffinme]

Hi WillJ,

The below code checks the value entered against a table of values (tbl_programs), undoes the entry if it already exists, opens a messagebox to advise the value already exists and opens the original entry.

Private Sub prog_program_name_BeforeUpdate(Cancel As Integer)
 
'*********************************
'Code sample courtesy of srfreeman anti duplication code
'*********************************
Dim SID As String
Dim stLinkCriteria As String
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
SID = Me.prog_program_name.Value  ' program name
stLinkCriteria = "[prog_program_name]= '" & SID & "'AND [ORG_ID] =" & Me.org_id  ' unique organization id that has various programs
    'Check tbl_programs table for see if name already exists (duplicate prog_program_name)
    If DCount("prog_program_name", "tbl_programs", stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning! Project " _
        & SID & " has already been entered.", vbInformation _
        , "Duplicate Program name"
        'Go to record of original Org Program Number
        rst.FindFirst stLinkCriteria
        Me.Bookmark = rst.Bookmark
    End If
Set rst = Nothing
End Sub

Hth

 

[WillJ]

Hey Hth,

I started writing a long winded reply becuase there was an error, however it is now resovled!
Thank you very much for all your help.
Could you explain to me why you've used the OrgId in the string link criteria?

Thanks !

 

[WillJ]

Ok, I take that back.
I've tried placing the code on various events, enter, exit, before Up, after up etc ... but they all seem to allow me to input a name incorrectly and the error box only occurs when editing the now duplicate input record.
The code is below, I've adjusted it for my purposes;

Private Sub CompanyName_Exit(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
SID = Me.CompanyName ' company name
stLinkCriteria = "[CompanyName]= '" & SID & "'AND [CompanyID ] =" & Me.CompanyID ' unique organization id that has various programs
'Check tbl_programs table for see if name already exists (duplicate CompanyName)
If DCount("CompanyName", "tblCompany", stLinkCriteria) > 0 Then
MsgBox "Warning! Project " _
& SID & " has already been entered If company is not duplicate, please re-enter with Region and date of month proceeding e.g 'CompanyName(Liv.28)", vbInformation _
, "Duplicate Company Name. "
'Go to record of original Org Program Number
End If
Set rst = Nothing
End Sub

 

[noboffinme]

Hi WillJ,

I'm noboffinme, Hth = "Hope this helps".

The Org_Id was used as my Database had Organisations that had one or more Programs (Projects).

So your Project may not need it.

Glad I was able to help.