or******@hotmail.com wrote in message
<11**********************@g44g2000cwa.googlegroups .com> :
I only call them stored procedures because that's what the enumeration
adCmdStoredProc says. All they are, are normail standard queries in
Access. Queries which work perfectly but that I can't use to populate
a recordset in VBA.
Maybe my question is how can I use a access query to populate a
recordset when the query takes data from a form.
I reeeeeeeeeeeallly don't want to use SQL strings directly because they
would be very complex and difficult to maintain.
Thanks for terse help so far :o)
Let's say you have a query1:
select field1, field2, field3
from mytable
where field4 = forms!form1!test
I would probably start with "declaring" the parameter
parameters forms!form1!test long;
select <rest of query>
Then try something like this air code (maybee you need some
[brackets] here and there)
dim cmd as adodb.command
dim prm as adodb.parameter
Dim rs As ADODB.Recordset
set cmd = new adodb.command
with cmd
set .activeconnection = currentproject.connection
.commandtext = "query1"
.commandtype = adcmdstoredproc
set prm = createparameter("forms!form1!test", adInteger,
adparaminput)
.parameters.append prm
.prm.value = Forms!Form1!txtQuantity
' to get readonly forwardonly
set rs = .execute
end with
' to get updateable
set rs = new adodb.recordset
rs.Open cmd, , adOpenKeyset, adLockOptimistic
--
Roy-Vidar
