Solved Prevent duplicate entries in a form when a name and date are already stored in a table

[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.

 

[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.

 

[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.