Validate data entry in a form through a query

[Guest]

I don't want to allow a user to enter a duplicate entry of a database field
in the form. Therefore I'm attempt to use a before update event to query the
database for the user's entry and return a message if it is a duplicate. I'm
new to access, but comfortable with ADO, so I'm trying to use ADO to return a
recodset object. My code looks something like:

strSQL = "Select Complaint_Nbr FROM Complaints where Complaint_nbr = '" &
Form_Data_Entry.[Primary_Complaint_Number] & "'"

boolDupId = False
Set RsC = New ADODB.Recordset
Set cn = New ADODB.Connection
DB_Name = CurrentProject.Path & "\" & CurrentProject.Name
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
DB_Name
cn.Open
RsC.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

I get an error message that the database has been placed in a state by the
user of machine that prevent the database from being opened.

Anybody know of another way to do this?

Thanks

 

[fredg]

I don't want to allow a user to enter a duplicate entry of a database field
in the form. Therefore I'm attempt to use a before update event to query the
database for the user's entry and return a message if it is a duplicate. I'm
new to access, but comfortable with ADO, so I'm trying to use ADO to return a
recodset object. My code looks something like:

strSQL = "Select Complaint_Nbr FROM Complaints where Complaint_nbr = '" &
Form_Data_Entry.[Primary_Complaint_Number] & "'"

boolDupId = False
Set RsC = New ADODB.Recordset
Set cn = New ADODB.Connection
DB_Name = CurrentProject.Path & "\" & CurrentProject.Name
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
DB_Name
cn.Open
RsC.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

I get an error message that the database has been placed in a state by the
user of machine that prevent the database from being opened.

Anybody know of another way to do this?

Thanks

Sure... use DCount instead of all of your code in the BeforeUpdate event:

If DCount("*","Complaints","[Complaint_nbr] = '" &
Me![Primary_Complaint_Number] & "'") > 0 Then
' The number is already in the table so cancel the update.
Cancel = True
End If

The above assumes (from your code sample) that [Complaint_nbr] is a text
datatype field.

However, if it is actually a number datatype field, then use:
If DCount("*","Complaints","[Complaint_nbr] = " &
Me![Primary_Complaint_Number]) Then
etc....
End If

Fred