Recordset of a form,,,PLEASE HELP

[Alex]

I am trying to set the recordset of a form with and get a
message runtime message 3251 - the operation is not
supported by this object. Can you help?
This is my codes:

Sub cmdsearch_click()
Dim Db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim qdfNew As DAO.QueryDef
Dim frm As Form
Set Db = CurrentDb
Set frm = Forms!ADDRESS
With Db
' Create temporary QueryDef.
Set qdfTemp = .CreateQueryDef("", "SELECT * FROM
tblADDRESS")
' Open Recordset and print report.
GetrstTemp qdfTemp
' Create permanent QueryDef.

End With
End Sub

Function GetrstTemp(qdfTemp As QueryDef)
Dim rstTemp As DAO.Recordset
Dim frm As Form
Set frm = Forms!ADDRESS
With frm
' Open Recordset from QueryDef.
.Recordset = qdfTemp.OpenRecordset
(DB_OPEN_SNAPSHOT)

With rstTemp
' Populate Recordset and print number of
records.
.MoveLast

MsgBox " Number of records = " & .RecordCount
End With

End With

End Function

 

[losmac]

I have'nt tested your code, but i see one problem.
Before You create query, You must be sure this query is
not exist. For creating/changing query try my function:

Function ChangeQuery(strQueryName As String, strSQL As
String) As Boolean
Dim dbs As database
Dim qwdf As QueryDef

ChangeQuery = True

Set dbs = CurrentDb
On Error Resume Next
Set qwdf = dbs.QueryDefs(strQueryName) 'czy istnieje w
kolekcji
If qwdf Is Nothing Then
Set qwdf = dbs.CreateQueryDef(strQueryName,
strSQL) 'utworz nowa
Exit Function
End If
On Error GoTo Err_ChangeQuery
With qwdf
.SQL = strSQL
.Close
End With
Set qwdf = Nothing
Set dbs = Nothing
Exit Function

Err_ChangeQuery:
ChangeQuery = False
Set qwdf = Nothing
Set dbs = Nothing
End Function

'======== http://losmac.republika.pl ========

 

[Dirk Goldgar]

I am trying to set the recordset of a form with and get a
message runtime message 3251 - the operation is not
supported by this object. Can you help?
This is my codes:

Sub cmdsearch_click()
Dim Db As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim qdfNew As DAO.QueryDef
Dim frm As Form
Set Db = CurrentDb
Set frm = Forms!ADDRESS
With Db
' Create temporary QueryDef.
Set qdfTemp = .CreateQueryDef("", "SELECT * FROM
tblADDRESS")
' Open Recordset and print report.
GetrstTemp qdfTemp
' Create permanent QueryDef.

End With
End Sub

Function GetrstTemp(qdfTemp As QueryDef)
Dim rstTemp As DAO.Recordset
Dim frm As Form
Set frm = Forms!ADDRESS
With frm
' Open Recordset from QueryDef.
.Recordset = qdfTemp.OpenRecordset
(DB_OPEN_SNAPSHOT)

With rstTemp
' Populate Recordset and print number of
records.
.MoveLast

MsgBox " Number of records = " & .RecordCount
End With

End With

End Function

I'm not sure that you can set the Recordset property of a form to a DAO
recordset -- you may need an ADODB recordset for that, but I can't
remember offhand. Even if you can, you'd need to use the Set statement:

Set .Recordset = qdfTemp.OpenRecordset(DB_OPEN_SNAPSHOT)

I have no idea what you're trying to do with rstTemp, since the code you
posted never sets it to anything.

*HOWEVER*
I don't see why you're going through all this rigmarole anyway. It
looks as though the same end could be achieved by the simple statement:

Forms!ADDRESS.RecordSource = "SELECT * FROM tblADDRESS"

There may be more to what you're trying to accomplish than you've shown,
but maybe you'd best explain what you're trying to do and how it can't
be done by simply setting the form's RecordSource property.