query for duplicates

[Cynthia]

I am trying to get a recordset (list) and I get the following error
Arguments are of the wrong type, are out of acceptable range or are in
conflict with one another.
Any Ideas? The error is on the Rs.open. I have used the same cn and rs
values
on other parts of the form with no problem. I am trying to pull a list
duplicate circuit numbers?
HERE IS THE CODE:

Private Sub CmdShowDup_Click()
On Error GoTo Err_CmdShowDup_Click

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
Dim rdup As String
rdup = ""
Dim strQry As String

strQry = IsNull(DLookup("[Circuit]", "[CircuitDuplicate]"))
If strQry = "True" Then
MsgBox ("No Duplicates found")
Else
'--I tried the remarked out line below using a query called
circuitduplicate
'--that only has the duplicate numbers.
'--strQry = "Select CD.circuit from circuitduplicate As CD"
'-- here tried getting the data straight from the table either way
'--I get the error above
strQry = "SELECT Count(EleCircuitList.Circuit) AS CountOfCircuit,
EleCircuitList.Circuit" & _
" FROM EleCircuitList GROUP BY EleCircuitList.Circuit
Where (((Count(EleCircuitList.Circuit)) > 1))"
Debug.Print strQry

rs.Open stsrQry, cn, 1
Do While rs.EOF = False
rdup = rdup & rs!Circuit & ","
rs.MoveNext
Loop

Me.Filter = "([qryCircuit].[Circuit] = '" & rdup & "')"
Me.FilterOn = True
End If

 

[Dirk Goldgar]

I am trying to get a recordset (list) and I get the following error
Arguments are of the wrong type, are out of acceptable range or are in
conflict with one another.
Any Ideas? The error is on the Rs.open. I have used the same cn and
rs values
on other parts of the form with no problem. I am trying to pull a
list duplicate circuit numbers?
HERE IS THE CODE:

Private Sub CmdShowDup_Click()
On Error GoTo Err_CmdShowDup_Click

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
Dim rdup As String
rdup = ""
Dim strQry As String

strQry = IsNull(DLookup("[Circuit]", "[CircuitDuplicate]"))
If strQry = "True" Then
MsgBox ("No Duplicates found")
Else
'--I tried the remarked out line below using a query called
circuitduplicate
'--that only has the duplicate numbers.
'--strQry = "Select CD.circuit from circuitduplicate As CD"
'-- here tried getting the data straight from the table either way
'--I get the error above
strQry = "SELECT Count(EleCircuitList.Circuit) AS
CountOfCircuit, EleCircuitList.Circuit" & _
" FROM EleCircuitList GROUP BY
EleCircuitList.Circuit Where (((Count(EleCircuitList.Circuit)) > 1))"
Debug.Print strQry

rs.Open stsrQry, cn, 1
Do While rs.EOF = False
rdup = rdup & rs!Circuit & ","
rs.MoveNext
Loop

Me.Filter = "([qryCircuit].[Circuit] = '" & rdup & "')"
Me.FilterOn = True
End If

If this is a copy/paste of tyour actual code, then you have a typo in
this line:

rs.Open stsrQry, cn, 1

That should presumably be:

rs.Open strQry, cn, 1

If you had Option Explicit specified for the module, the compiler would
have caught the error.

 

[Cynthia]

Thank you so much for taking the time to look at this. Sorry I missed the typo
I had heard of the option explicit, but never used it. Sure will from now on.
Thank you again.
Cynthia

I am trying to get a recordset (list) and I get the following error
Arguments are of the wrong type, are out of acceptable range or are in
conflict with one another.
Any Ideas? The error is on the Rs.open. I have used the same cn and
rs values
on other parts of the form with no problem. I am trying to pull a
list duplicate circuit numbers?
HERE IS THE CODE:

Private Sub CmdShowDup_Click()
On Error GoTo Err_CmdShowDup_Click

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
Dim rdup As String
rdup = ""
Dim strQry As String

strQry = IsNull(DLookup("[Circuit]", "[CircuitDuplicate]"))
If strQry = "True" Then
MsgBox ("No Duplicates found")
Else
'--I tried the remarked out line below using a query called
circuitduplicate
'--that only has the duplicate numbers.
'--strQry = "Select CD.circuit from circuitduplicate As CD"
'-- here tried getting the data straight from the table either way
'--I get the error above
strQry = "SELECT Count(EleCircuitList.Circuit) AS
CountOfCircuit, EleCircuitList.Circuit" & _
" FROM EleCircuitList GROUP BY
EleCircuitList.Circuit Where (((Count(EleCircuitList.Circuit)) > 1))"
Debug.Print strQry

rs.Open stsrQry, cn, 1
Do While rs.EOF = False
rdup = rdup & rs!Circuit & ","
rs.MoveNext
Loop

Me.Filter = "([qryCircuit].[Circuit] = '" & rdup & "')"
Me.FilterOn = True
End If

If this is a copy/paste of tyour actual code, then you have a typo in
this line:

rs.Open stsrQry, cn, 1

That should presumably be:

rs.Open strQry, cn, 1

If you had Option Explicit specified for the module, the compiler would
have caught the error.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thank you so much for taking the time to look at this. Sorry I
missed the typo I had heard of the option explicit, but never used
it. Sure will from now on. Thank you again.

You're welcome. If you set the VB option "Require Variable Declaration"
(via the VB Editor's Tools -> Options... dialog), then all new modules
you create will have Option Explicit specified automatically.