Runtime Error 3001 Arguments Are Of The Wrong Type, Are Out Of TheAcceptable Range, or are in confli

[R Tanner]

Hi,

I'm trying to run the following code whenever a user tries to create a
new record in the database. The purpose of this function is to check
for duplicates in the table. I am getting a runtime error 3001 with
the following description when I try to run the public function. Does
anyone have any idea why? I have done something similar to this in
the past, and didn't get any errors then.

Runtime Error 3001: Arguments Are Of The Wrong Type, Are Out Of The
Acceptable Range, or are in conflict with one another.

Public Function Duplicates() As Boolean

Dim RS As ADODB.Recordset
Dim DB As Database
Set DB = CurrentDb
Set RS = New ADODB.Recordset
Dim MyString As String
MyString = Forms![DocumentATicket]![Description]

With RS
.ActiveConnection = CurrentProject.Connection
.Open "SELECT Tickets.ID, Tickets.Description FROM Tickets", ,
adOpenDynamic, adLockOptimistic
.Find "Description = " & MyString
Select Case IsNull(.Fields(0))
Case True
Duplicates = False
Case False
Select Case .Fields(0)
Case Is = Forms![DocumentATicket]![ID]
Duplicates = False
Case Else
Duplicates = True
End Select
End Select
End With

DB.Close
RS.Close
Set DB = Nothing
Set RS = Nothing


End Function

 

[Tom van Stiphout]

On Tue, 30 Dec 2008 13:31:49 -0800 (PST), R Tanner

You need to wrap string arguments in single-quotes:
.... where Description = 'test'

-Tom.
Microsoft Access MVP

 

[R Tanner]

On Tue, 30 Dec 2008 13:31:49 -0800 (PST), R Tanner


You need to wrap string arguments in single-quotes:
... where Description = 'test'

-Tom.
Microsoft Access MVP

How would that work with a variable? I can't very well enclose a
variable or the form control reference in single-quotes...

 

[Douglas J. Steele]

If you know that there will never be an apostrophe in the string, you can
use

..Find "Description = '" & MyString & "'"

Exagerated for clarity, that's

..Find "Description = ' " & MyString & " ' "

If there might be apostrophes, but never double quotes, use

..Find "Description = """ & MyString & """"

(that's three double quotes in a row before, and four double quotes in a row
after.

If you're not sure, you'll need something like:

..Find "Description = '" & Replace(MyString, "'", "''") & "'"

Exagerated again for clarity, that's

..Find "Description = ' " & Replace(MyString, " ' ", " ' ' ") & " ' "

 

[David W. Fenton]

m:

I'm trying to run the following code

I see no reason whatsoever why you should be using ADO for this.
Indeed, I don't know why you need to do this in this fashion at all
-- a simple DLookup() ought to be able to get you the answer (or,
for that matter, a DCount(), depending on how you coded it).

Oh, also, your code is really nonsensical, as you have a database
variable which you set as CurrentDB, but you never use it, since
that's a DAO datatype and has nothing whatsoever to do with ADO.

I'd likely replace all of it with:

Public Function CheckForDuplicates() As Boolean
Dim strCriteria As String
Dim lngMatchID As Long

strCriteria = "Description = " & Chr(34)
strCriteria = strCriteria & Forms!DocumentATicket!Description
strCriteria = strCriteria & Chr(34)
lngMatchID = Nz(DLookup("ID", "Tickets", strCriteria)
If lngMatchID <> 0 then
CheckForDuplicates = (lngMatchID <> Forms!DocumentATicket!ID)
End If
End Function

There are a number of things I wouldn't do, such as hardcoding a
reference to a particular form. You could also make your DLookup
criteria include <>Forms!DocumentATicket!ID. In that case, the only
value you'd care about was <>0. That would look something like this:

Public Function CheckForDuplicates(varCheckDescription As Variant, _
lngCheckID As Long) As Boolean
Dim strCriteria As String
Dim lngMatchID As Long

If IsNull(varCheckDescription) Then Exit Sub

strCriteria = "Description = " & Chr(34)
strCriteria = strCriteria & varCheckDescription
strCriteria = strCriteria & Chr(34)
strCriteria = strCriteria & " AND ID <> " & lngMatchID
lngMatchID = Nz(DLookup("ID", "Tickets", strCriteria)
CheckForDuplicates = (lngMatchID <> 0)
End Function

This would allow you to check for a duplicate on this field in this
table from any form.