|
|
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
|
|
On Tue, 30 Dec 2008 13:31:49 -0800 (PST), R Tanner
<tanner.robin[ at ]gmail.com> wrote:
You need to wrap string arguments in single-quotes:
.... where Description = 'test'
-Tom.
Microsoft Access MVP
[Quoted Text] >.Find "Description = " & MyString
|
|
On Dec 30, 9:34 pm, Tom van Stiphout <tom7744.no.s...[ at ]cox.net> wrote:
[Quoted Text] > On Tue, 30 Dec 2008 13:31:49 -0800 (PST), R Tanner
>
> <tanner.ro...[ at ]gmail.com> wrote:
>
> You need to wrap string arguments in single-quotes:
> ... where Description = 'test'
>
> -Tom.
> Microsoft Access MVP
>
> >.Find "Description = " & MyString
How would that work with a variable? I can't very well enclose a
variable or the form control reference in single-quotes...
|
|
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, " ' ", " ' ' ") & " ' "
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"R Tanner" <tanner.robin[ at ]gmail.com> wrote in message
news:525fcd00-10e0-4db3-b40c-0f2058be7eeb[ at ]n41g2000yqh.googlegroups.com...
[Quoted Text] > On Dec 30, 9:34 pm, Tom van Stiphout <tom7744.no.s...[ at ]cox.net> wrote:
>> On Tue, 30 Dec 2008 13:31:49 -0800 (PST), R Tanner
>>
>> <tanner.ro...[ at ]gmail.com> wrote:
>>
>> You need to wrap string arguments in single-quotes:
>> ... where Description = 'test'
>>
>> -Tom.
>> Microsoft Access MVP
>>
>> >.Find "Description = " & MyString
>
> How would that work with a variable? I can't very well enclose a
> variable or the form control reference in single-quotes...
|
|
R Tanner <tanner.robin[ at ]gmail.com> wrote in
news:34fbeb0c-db64-4342-9d50-7f12eed0aa17[ at ]e22g2000vbe.googlegroups.co
m:
[Quoted Text] > 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.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
|