Notify User of Duplicate Field Value on After Update? 2

[beverlee]

I have a form which includes an Application # that cannot be duplicated. I've set it in the table to be indexed with no duplicates. If a duplicate gets entered on the form, however, it won't alert the user until he/she moves to the next record. Is there a way to alert the user immediately after updating the field so that they don't enter a whole record first? Maybe on the After Update event? Thanks!

 

[PHV]

Take a look at the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya beverlee . . . . .

[blue]PHV[/blue] is on target.

In the [blue]BeforeUpdate[/blue] event of [purple]Application#[/purple], try the following. [blue]You![/blue] substitute proper names in [purple]purple[/purple]. Also, if [purple]Application#[/purple] is numeric, [blue]remove the single quotes in the criteria line[/blue].

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, Criteria As String
   
   DL = vbNewLine & vbNewLine
   Criteria = "[[purple][b]AppFieldName[/b][/purple]]='" & Me![purple][b]AppControlName[/b][/purple] & "'"
   
   If IsNull(DLookup("[[purple][b]AppFieldName[/b][/purple]]", "[purple][b]TableName[/b][/purple]", Criteria)) Then
      Msg = "Application# Already Exists in Database!" & DL & _
            "Duplicates are not allowed . . ." & DL & _
            "Reconsider your data entry and try again . . ."
      Style = vbCritical + vbOKOnly
      Title = "Duplication Application# Error! . . ."
      MsgBox Msg, Style, Title
      Cancel = True
   End If[/blue]

See Ya! . . . . . .

 

[beverlee]

Thanks for your response. I can't get it to work, however. Here is the code I used:

Dim Msg As String, Style As Integer, Title As String
Dim DL As String, Criteria As String

DL = vbNewLine & vbNewLine
Criteria = "[App#]='" & Me!AppNum & "'"

If IsNull(DLookup("[App#]", "Applications", Criteria)) Then
Msg = "Application# Already Exists in Database!" & DL & _
"Duplicates are not allowed . . ." & DL & _
"Reconsider your data entry and try again . . ."
Style = vbCritical + vbOKOnly
Title = "Duplication Application# Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If


Did I goof?

 

[PHV]

1) If App# is defined as numeric in the Applications table then get rid of the single quotes in Criteria.
2) Invert the test:
If Not IsNull(...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beverlee]

It's not numberic, it's text. A sample Application number is S05-020. ?????

 

[PHV]

And what about point 2) ?

 

[TheAceMan1]

Also, I just abbreviated application in [purple]App#[/purple]. You should [blue]change it to the actual name[/blue] if its different!

See Ya! . . . . . .

 

[beverlee]

Inverting the test worked.....sorry I missed that the first time. You're awesome! Thanks a million! Have a star!

 

[TheAceMan1]

Thanks for the backup [blue]PHV . . .[/blue]

See Ya! . . . . . .