Solved Prevent duplicate entries in a form when a name and date are already stored in a table (1 Viewer)

[GeneErz]

Table name = ChildSupportDailyWork
Table name field = Probation Officer Name
Table date field = Date of Work
Form name = Child Support

When a user tries to enter a record with their name and date that already exists in the table ChildSupportDailyWork I want a message popup reading "You already entered data for that date" Then I want on clicking OK from the message box for the form to open to the record where that date already exists. My attempt below produces a message popup no matter if the record is duplicated or not. And the go to already existing record for that date produces a run-time error 94 Invalid us of Null.

Private Sub Date_of_Work_AfterUpdate()

Dim NewPO As String
Dim NewDate As String
Dim stLinkCriteria As String
Dim PONo As Integer

'Assign the entered PO Name and Date of Work to a variable
NewPO = Me.Probation_Officer_Name.Value
NewDate = Me.Date_of_Work.Value

stLinkCriteria = "[Probation_Officer_Name] = '" & NewPO & "' And [Date_of_Work] = #" & NewDate & "#"

If NewPO = DLookup("[Probation_Officer_Name]", "[ChildSupportDailyWork]", stLinkCriteria) Then

MsgBox "You have already entered data for this date" _
& vbCr & vbCr & "" & NewDate & "" _
& vbCr & vbCr & "Please Check Again", vbInformation, "Duplicate Information"

Me.Undo 'undo the process and clear all fields
End If
'show the record of matched customer PO and Date from the ChildSupportDailyWork table

PONo = DLookup("[ID]", "[ChildSupportDailyWork]", stLinkCriteria)
Me.DataEntry = False
DoCmd.FindRecord PONo, , , , , acCurrent

End Sub

 

[Ranman256]

Instead of : if NewPO=
Use:

NewPO = DLookup("[Probation_Officer_Name]", "[ChildSupportDailyWork]", stLinkCriteria)
If isNull( NewPO) then
   '  Add new rec.
Else 
  MsgBox "already exists"
End if

 

[jdraw]

See this article for ideas.
Good luck.

 

[arnelgp]

see if same functionality you want.

 

[Pat Hartman]

FYI, names are not good choices as unique identifiers. Even small sets of names encounter problems with duplication.

In addition to looking up the UserID and Date so you can give the user a meaningful warning, you should also add a two field unique index on the table to actually prevent the duplicate from being added.

And finally, i would reconsider the requirement entirely. Is it really necessary to prevent multiple entries on a given day? Really?

 

[GeneErz]

Instead of : if NewPO=
Use:

NewPO = DLookup("[Probation_Officer_Name]", "[ChildSupportDailyWork]", stLinkCriteria)
If isNull( NewPO) then
   '  Add new rec.
Else
  MsgBox "already exists"
End if

Thank you, I just change that line and received the same results.

 

[Pat Hartman]

NewPO is defined as a string so if the dLookup() doesn't find a match for the criteria, it will return Null which will throw an error since a string cannot be null. Try changing the definition of NewPO to Variant and see if that helps. Otherwise, please post the exact error message.

 

[Isaac]

Using DCount (test if > 0).....will A) eliminate this entire concern, and B) look more intuitive, when reading/understanding your code.

You can check if DLookup is null, or use DCount, if it were me I think I'd probably use Dcount, all else being equal.

 

[GeneErz]

[U]arnelgp[/U] solved my question. His example was exactly what I needed. Thank you all for all of your responses.