Record check

[John liem]

How to do I check if an input data (ex. 123) in record already exist or not?. If the input (123) is existing, give a message "existing" and if not ... just record the new number.
The check should be "like *123*", as 123 could be in the middle of a long record.

 

[Howlsta]

Look up DCount in help or search for it here.

if dcount (etc...) >0 then
msgbox "existing"

Rich

 

[John liem]

I have been using this statement:

Dim SO As String
Dim rs As Recordset
SO = "select * from tbl where [SO]= '" + Me.[SO] + "'"
Set rs = CurrentDb.OpenRecordset(SO, dbOpenSnapshot)
If Not (rs.BOF) Then
MsgBox "existing"

But this statement only look for the string (in my example "123") and not *123*.
How do I do that, it looks for a string "*123*".

 

[Howlsta]

Try this (if you are dealing with a text field), it is simpler than using a recordset

If DCount("[YourField]", "YourTable", "[YourField] like " & "'" & "*" & yourTxtbox & "*" & "'") > 0 Then
MsgBox "Existing"
End If

I'm not sure how to get it to work for a number field type though (I think it just requires slightly different syntax removing the "'" bits)hopefully yours is a text field, but maybe someone else can help if not.

HTH

Rich

 

[John liem]

Hi Rich,

This will work .... thanks a lot!

John.