Passing a recordset to a form

[Matthew DeAngelis]

Hello,

I am coding a button that will filter a recordset and pass that
recordset to the form. I have tested the filter and it works fine but,
when I try to pass the recordset to the form, I get the following error:
"Runtime error '3251': Operation is not supported for this type of
object." The help file says this means that this action is normally
supported, but is not in this specific instance. Here is the code
snippet I am using:

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim stDocName As String
stDocName = "frm: AllCompanies"
Set cnn = CurrentProject.Connection
rst.Open "[qry: frmPrincipalSearch]", cnn, adOpenDynamic
rst.Filter = "Principal Like *" & Forms!frmSearch!Principal & "*"
DoCmd.OpenForm stDocName
With Forms![frm: AllCompanies]
.Recordset = rst
End With

The debugger highlights the .Recordset = rst line. Does anyone know
what I am doing wrong? Please note that I am very new to working with
recordsets!


Thanks,
Matt

 

[Jonathan Parminter]

-----Original Message-----
Hello,

I am coding a button that will filter a recordset and pass that
recordset to the form. I have tested the filter and it works fine but,
when I try to pass the recordset to the form, I get the following error:
"Runtime error '3251': Operation is not supported for this type of
object." The help file says this means that this action is normally
supported, but is not in this specific instance. Here is the code
snippet I am using:

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim stDocName As String
stDocName = "frm: AllCompanies"
Set cnn = CurrentProject.Connection
rst.Open "[qry: frmPrincipalSearch]", cnn, adOpenDynamic
rst.Filter = "Principal Like *" & Forms!frmSearch! Principal & "*"
DoCmd.OpenForm stDocName
With Forms![frm: AllCompanies]
.Recordset = rst
End With

The debugger highlights the .Recordset = rst line. Does anyone know
what I am doing wrong? Please note that I am very new to working with
recordsets!


Thanks,
Matt
.

Hi Matt,

the problem is likely to be that you assign a SQL to a
form's recordsource property to create the form's
recordset.

Just a suggestion... how about creating a SQL string,
instead of a filter. Then assign this to the form. The
advantage is the the form is not first openning an entire
recordset and the filtering it. Thus a time saver,
particularly over a network.

for example in snippet (assumes that frmPrincipalSearch is
actually a query):
dim strSQL as string

strSQL="select frmPrincipalSearch.* from
frmPrincipalSearch where ((Principal) Like '*" & Forms!
frmSearch!Principal & "*');"

DoCmd.OpenForm FormName:=stDocName, OpenArgs:=strSQL

....
to make this more efficient save the form without a
recordsource, then in form open event (stDocName_Open)

if len(me.openargs)>0 then
me.recordsource=me.openargs
else
me.recordsource="frmPrincipalSearch"
end if

Hope this all makes sense and that it helps
Luck
Jonathan

 

[Brendan Reynolds]

It's not an area I'm familiar with myself, I'd usually set the form's
RecordSource property instead. Looking at the example in the help file,
though, I notice that a) the Recordset variable is declared as Public*
outside the procedure (otherwise it would go out of scope as soon as the
code exited the procedure) and b) the CursorLocation property of the
recordset is set to adUseClient. The example is also using adOpenKeyset
rather than adOpenDynamic, as well as specifying the lock type as
adLockOptimistic. My guess is that the CursorLocation may be the important
property here, but I can't say for sure - ADO help is not functioning on
this PC.

*Actually 'Global' in the example, but I can't imagine why - that's obsolete
syntax only maintained for backward compatibility.

Global rstSuppliers As ADODB.Recordset

Sub MakeRW()
DoCmd.OpenForm "Suppliers"
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set Forms("Suppliers").Recordset = rstSuppliers
End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.