Check for Dups on unbound form 1

[jlg5454]

How can you check for Dups with an unbound form. For example, I have a field called File#. I do have the field in the table set as a primary field, but I want the form to alert the user that this entry contains a dup right off the bat. I read on here that Dlookup is the way to go, but I can't find an example. Can anyone point me to the right link?

Thanks,

JG

 

[TheAceMan1]

How are ya jlg5454 . . .

[ol][li]Open any module/code window.[/li]
[li]Type [blue] Dlookup[/blue][/li]
[li]Put the cursor on the word and hit [blue]F1[/blue][/li][/ol]
[blue]Its called context sensitive help! . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[jlg5454]

Thanks,

I tried this:

Private Sub txtCallFileNum_BeforeUpdate(Cancel As Integer)
Dim RetVal As Variant
RetVal = DLookup("Call_File_No", "tbloutbound", "Call_File_No" = " & Me!txtCallFileNum & ")
If Me!txtCallFileNum = RetVal Then
MsgBox txtCallFileNum.Value & " Is a Duplicate! Please enter a new one."
setDefaults
Else
txtCallFileNum.SetFocus


End If

End Sub


And I receive a message telling me that I must save field before I execute SetFocus ect.

Any Thoughts. I learning VB on the fly

Thanks,

JG

 

[TheAceMan1]

jlg5454 . . .

Although you have syntax errors you do have the concept. Not a bad try at all! Consider the following extract from help on DLookup:

[blue]If no record satisfies criteria or if domain contains no records, the DLookup function returns a [purple]Null[/purple].[/blue]

So if your search is found the return from DLookup will be a value (not null). Using the [blue]IsNull[/blue] function makes for easy detection. Compare the following code. Note: if [blue]txtCallFileNum[/blue] is a numeric data type (not text), remove the two single quotes in [red]red[/red] in the criteria:

[blue]Private Sub txtCallFileNum_BeforeUpdate(Cancel As Integer)
   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, Criteria As String

   DL = vbNewLine & vbNewLine
   Criteria = "[Call_File_No] = [red][b]'[/b][/red]" & Me!txtCallFileNum & "[red][b]'[/b][/red]"
   
   If Not IsNull(DLookup("Call_File_No", "tbloutbound", Criteria)) Then
      Msg = "'" & Me!txtCallFileNum & "' Is a Duplicate!" & DL & _
            "Please enter a new one! . . ."
      Style = vbCritical + vbOKOnly
      Title = "Duplicate File# Error! . . ."
      MsgBox Msg, Style, Title
      Cancel = True [green]'Abort Updating and Keep Focus![/green]
   End If

End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[jlg5454]

AceMan,

Perfect Thanks for the tips on using Dlookup. It was extremely helpful. I hope I can someday give good tips just as you have.

Thanks again,

JG