Check db for existing string

[penfold1992]

I have a form that allows me to insert a value into a table. the table holds names of people. If the user wishes to insert a value into the table, id there to be a check to see if this value is already present.
im not exactly sure what the syntax of the code is but Im assuming i would use something like the Dlookup function.

If Dlookup in "Table99,Names", NewSupplier.Value" <>0
Then
msgbox "This name is already present"

I just wish to look up through a table to see if it is the same and if it is, tell the users that it already has that name.

Many people wish to do this for like an ID code but i am not concerned about the number just whether something of the same name exists or not.

thanks.

 

[nanscombe]

Close. DCount rather than DLookup...

Private Sub NewSupplier_BeforeUpdate(Cancel as Integer)
  If D[COLOR="Red"]Count[/COLOR]("Names", "Table99", "Names = '" & Me.NewSupplier & "'") > 0 Then
    Msgbox "This name is already present
    Cancel = True ' stops the value being saved
  End If
End Sub

 

[pr2-eugin]

penfold1992, one way to achieve what you are trying to do would be to make the field in the table design view as Indexed as Yes (No duplicates) but that will not give you a pleasant error message.. You have the right idea to deal with this problem.. But use a DCount()

 

[penfold1992]

i have a few questions about how this works.
say i have the name "Penfold1992" already present.
why does it flag up "penfold1992" and "pENfOlD1992"
why does it ignore caps?

also instead of "Cancel = True" can i not just use...
"Exit Sub"
that will be fine in my case.

 

[nanscombe]

It would seem that DCount() is not case sensitive, so "Penfold1992" would be treated the same as "penfold1992" and "pENfOlD1992".

"Cancel = True" is used to tell the field NewSupplier NOT to allow the changes to be saved, so it is required.

 

[penfold1992]

If (IsNull(NewSupplier.Value) = False) And (NewSupplier <> "") Then
    NewSuppl = NewSupplier.Value
Else
    MsgBox "New Supplier Box cannot be blank.", vbExclamation
Exit Sub

In this instance its an either or, so I dont think I need to use cancel but....
if its not "else" it will continue on and go to...

cmdresponse = MsgBox("Do you wish to add """ & NewSupplier.Value & """ to the list of suppliers?", _
                vbYesNo + vbQuestion, "Confirmation")
If cmdresponse = vbNo Then
Exit Sub
End If
 
If the user selects "no" it doesnt add it to the table it just takes you back to before you pressed the "insert" button.
 
I still dont see why Cancel = true is necessary in this case =S