Check If Form Value Already Exists In Database

[Richard_Marx]

I need to check if a value exists in a database prior to allowing users to input. This is the syntax that I have but it gives me a type mismatch error

Private Sub UserID__Exit(Cancel As Integer)


	If CurrentDb.OpenRecordset("Select count(*) from [Table With Spaces Data] where [User ID]=" & CSTR(" & Me.[User ID] & ") > 0) Then
		MsgBox ("This userid has already been created!")
    End If


End Sub

User ID is a field on my form, and has an int value in my table which is why I CSTR()

[ranman256]

your sql is wrong, ID is not usually string, but rather numeric: "Select count(*) from [Table With Spaces Data] where [User ID]=" & Me.[User ID]

Either, open the query to see if it has records.
docmd.openquery "qsCheckID"

or count records in the query
if Dcount("*","qsCheckID")>0 then msgbox "Record exists"

[JoeM]

If the User ID field is string (which I am assuming since you are using the CSTR function), it needs to be enclosed in quotes. You are also a double quite at the end of your SQL statement,
Try updating the WHERE clause to:

where [User ID]='" & CSTR(" & Me.[User ID] & "')" > 0 Then

Note, you can also use the DCOUNT function to get the count.
http://www.techonthenet.com/access/f...ain/dcount.php

[Richard_Marx]

If the User ID field is string (which I am assuming since you are using the CSTR function), it needs to be enclosed in quotes. You are also a double quite at the end of your SQL statement,
Try updating the WHERE clause to:

where [User ID]='" & CSTR(" & Me.[User ID] & "')" > 0 Then

Note, you can also use the DCOUNT function to get the count.
http://www.techonthenet.com/access/f...ain/dcount.php

Trying the whwere clause above gives me a Compile Error - Syntax error when I debug.

[orange]

Check If Form Value Already Exists In Database

Please tell us how this relates to the code you posted.
It might be better if you tell us in simple English WHAT you are trying to do. There my be options, but we need to know WHAT before we can suggest HOW.

[ItsMe]

My vote is DCOUNT. Get the result and then evaluate that result.

[JoeM]

And let us know the Data Type of your User ID field.

[Richard_Marx]

your sql is wrong, ID is not usually string, but rather numeric: "Select count(*) from [Table With Spaces Data] where [User ID]=" & Me.[User ID]

Either, open the query to see if it has records.
docmd.openquery "qsCheckID"

or count records in the query
if Dcount("*","qsCheckID")>0 then msgbox "Record exists"

using the DCOUNT() option got it.

I did not use "*" as in a few fields duplicate values are allowed, just not for the userid field, so my syntax was

DCount("[User ID]"," [Table With Spaces]") > 0

[JoeM]

using the DCOUNT() option got it.

I did not use "*" as in a few fields duplicate values are allowed, just not for the userid field, so my syntax was

Note that if the [Table With Spaces] is the name of your table and not a query, the formula you wrote is counting the number of records in the table WITH NO CRITERA!
If you want to count the number of records equal to a specific User ID, you must use the third argument of the DCOUNT function, which is for criteria, or your second argument must be a query with your criteria already built in.

[Richard_Marx]

Note that if the [Table With Spaces] is the name of your table and not a query, the formula you wrote is counting the number of records in the table WITH NO CRITERA!
If you want to count the number of records equal to a specific User ID, you must use the third argument of the DCOUNT function, which is for criteria, or your second argument must be a query with your criteria already built in.

Yes that is my set-up, thank you for that info. User ID is my field, [Table With Spaces] is a table name that contains spaces so I will indeed need to use the 3rd argument.