Check if a value exists in a table (1 Viewer)

Hi everyone, I hope anyone can help me:

I have a textbox and a save button. When I write something into the textbox and I click the save button, what I wrote into the textbox is saved in a table. However, If I click the button again the data is saved again, and therefore, duplicated. What I want is to check if the data entered in the textbox is already in the table or not. The table has three columns, a ID column, a column with text, and a column with numbers (this last column is used to connect with other table). I tried using DLookup:

variable = DLookup("Idcity","City", "Idcity = '" & Text5 & "'")

Text5 is a textbox that shows a number from other table. I used that number to identify the idcity from the table City. I used an if to check if the number exists in City table:

If variable = 0 Then

CurrentDb.Execute (Insert into...)

Else

CurrentDb.Close

However I get an error in DLookup saying that data types in criteria don't match. I've been looking for a code doing something similar but I wasn't successful, so I hope someone can help me. Thank you in advance! you're great!!

See if this helps:

http://www.mvps.org/access/general/gen0018.htm

Also, the DLookup will return Null if there's no match. I'd use DCount instead.

Thank you very much!! I used Dcount and It works, you're great.

hello, could you pls help me to make a string to check if a 3 key fields are already in a table? I have two ID fields, CustomerID, productID and a date field, date of sale. I understand I have to create a temp field to keep the dcount result, then before update the table I can receive a msg and take me to the record.
I really do not know how to do that. Tks for any help.

Hi Neideb, mmm are you asking me or pbaldy? because I'm just a beginner with visual basic, I have solved the problem using Dcount but probably someone with more experience would do it in other way. I think you should create a post for your question, I'm really sorry for not being helpful but I assure you,I'm very limited with visual basic :$. Good luck!

Tks Lucas, In fact I am struggling with an aplication and each day I have another problem.

I will review the whole problem and start another post.

Here is the method I use and it works perfectly.

'Count records usind DCOUNT method
Dim CountOfINIDot1 As Integer
CountOfINIDot1 = 0

'This is the Workhorse line:
CountOfINIDot1 = DCount("[INIDot1]", "tblPropagationrequests", "[INIDot1] = '" & Me!INIDot1 & "'" & "AND [PropagationDate] = #" & Me!ubPropagationDate & "#")


'Do the analysis: Is there already a record?
If CountOfINIDot1 >= 1 Then
MsgBox ("A propagation request already exists for this record for " & Me.ubPropagationDate & "." & vbCrLf & "Please modify the existing record with your additional information rather than create a new request" & vbCrLf & "Press the ESC key to remove the record so as not to save it before proceeding."), vbOKOnly,
"Duplicate Propagation Request"
PUT YOUR ACTIONS HERE.
End If

FYI:
My analysis uses the table (TblPropagationRequests) fields of INIDOT1, PropagationDate

I have form fields of ubPropagationDate which is used in the routine.

I have a question on: How to see if value exisits in Access table then stop the search.

My routine above works well, but it looks through the entire table and counts the "hits." What I'd really like to do is search through the table and if it finds one, stop the search immediately and report back. Thus, it takes less time and resources. As my tables are getting larger with data, it's taking more and more time to evaluate. I really only need to know if an exact duplicate is already there, not how many.

Any suggestions by anyone? Thanks.

You can try DLookup() instead of DCount().

I just use

It uses less resources then running a query to see if it exits.. Basically it attempts to update and if it fails to update (recordsAffected = 0) it inserts it.