Using two parameters in a query with ADO 3

[polnichek]

I am trying to get a query working that has two parameters. I can find a record in a table by using the ado recordset object with one parameter but I am unable to get it to work with two. I have a bridge table that I need to use two parameters to locate a unique row. The statement is as follows:

strSQL = "WHERE IncidentID_fk = " & Parm1 & " AND
GroupID_fk = " & Parm2


When I execute the code the compiler trips on the statement:

rst.Find strSql

the error message is:
Runtime error 3001

Arguments are of the wrong type,are out of acceptable range or are in conflict with one another.

If I shorten the query to the first parameter it executes fine.

The code:

Private Sub FindRecord(ByVal lngIncidentID As Long, ByVal lngGroupID As Long)
Dim lngParm1 As Long
Dim lngParm2 As Long
Dim intLoopctrl As Integer

lngParm1 = lngIncidentID
lngParm2 = lngGroupID

Dim rst As ADODB.Recordset
'lngIncidentID = 201

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "Select * from
tblInc_bridge_ThreatLevBridge_Contact"

strSQL = "WHERE IncidentID_fk = " & Parm1 & " AND
GroupID_fk = " & Parm2

rst.Find strSQL
lngFoundRecord = rst!GroupID_fk

Debug.Print "Found Record May 17", lngFoundRecord




End Sub

Any help with this would be greatly appreciated.

Polnichek

 

[jsteph]

Find only works on a single parm for ADO. This is a shortcoming of ADO, in my opinion.
--Jim

 

[PHV]

Why not simply this ?
rst.Open "SELECT * FROM tblInc_bridge_ThreatLevBridge_Contact WHERE IncidentID_fk=" & lngParm1 & " AND GroupID_fk=" & lngParm2
lngFoundRecord = rst!GroupID_fk

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[polnichek]

Thank you both for your helpful replies. I was able to get the thing working with a combination of the two answers. I used the suggestion to utilize the rst.Open method as PH suggested, and I dumped the rst.Find statement as jsteph mentioned. He is right a single parameter for the find method is a real shortcoming for ADO. I am beginning to think DAO is better?

Polnichek

 

[PHV]

a single parameter for the find method is a real shortcoming for ADO
The Filter property admits combined criterias.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[abaldwin]

I think, depending on what the rest of the application does, that you may want to totally revisit PVH's suggestions. As for processing time and "transaction cost" go. Executing a single open statement returning the one record you need is more efficient than opening a RS with all the records in an ever-growing table, keeping it open for the entire procedure, and then re-using it to find another record.

Just my $.02


Andy Baldwin

"Testing is the most overlooked programming language on the books!