How to set a RecordSet as Form RecordSource

[Paul D. Parisi]

How do you have a form browse on a recordset in VBA code.

for example

Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String

Set dbs = CurrentDb()
strSQL = "SELECT * FROM Table;"

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
rst.MoveFirst

------> this is where I want to open a form with the RST recordset. I have
tried several ways with no luck

rst.Close
dbs.Close

Thanks in advance!

Paul.

Par...@microdata.com

[Alan Vouk]

Ok What I would do is one of two things. You can place the SQL statement in
the RecordSource property of the form. You can then clone that recordset and
move as you wish using VBA code. To equate the two recordsets equate the
bookmark properties of the recordset and the form. It should look like
something like this:

strSQL = "Select " blah blah blah
DoCmd.OPenForm "frm_SomeForm"
forms![frm_SomeForm".Recordsource = strSQL

To clone that recordset then you would
Set rst = forms![frm_SomeForm].RecordsetClone
rst.MoveLast - move to whatever place you want
forms![frm_SomeForm].Bookmark = rst.Bookmark - this will move the
forms place to the same record as in your recordset rst

Note:
Make surte that you close and then destroy your rst object and destroy
your db object/You can do this by setting them to nothing. Picky I know -
sorry

Hope it helps,

Alan
av...@bellsouth.net

Paul D. Parisi wrote in message ...

[Terry Kreft]

Hi Paul,
You don't assign recordsets to forms, you change the rowsource property and
the filter property (both of which are string properties) so you could do
one of the following

Either
Forms![MyForm].Recordsource = strSQL

or
Forms![MyForm].Recordsource = RST.Name

You can also apply the filter from the recordset to the form

Forms![MyForm].Filter = RST.Filter
Forms![MyForm].FilterOn = (Len(RST.Filter)>0)

[Tom Felix Jensen]

Hallo Paul
Download Pizza2 from my site and check out how the search function is
working. The trick is to use a clone of the recordset.

--
Med venlig hilsen
Tom Jensen
SITE: http://www.danbbs.dk/~ff-soft
E-MAIL: ff-...@danbbs.dk

" Download database examples
from my site and check out Access FAQ's at
http://home.att.net/~dashish or
http://www.hammerdata.com/Newsgrp/
http://members.forfree.at/~larsm in Europe
http://support.microsoft.com

Paul D. Parisi skrev i meddelelsen ...

[Paul D. Parisi]

Hi Alan,

Thanks for the input... I am almost there... I need to be able to update the
RecordSet, (Clone) in this case.

Here are some more details.

The SQL statement I am using includes two sythetic fields (I'm not sure if
that is the proper name for the fields) is as follows:

SELECT Students.StudentFirstName, Students.StudentLastName, SSEC.STUDENT_NU,
SSEC.SEMESTER, SSEC.COURSE_NUM, SSEC.SECTION_NU, 0 AS Condition, 0 AS
Excused, #5/28/98# AS [Date]
FROM SSEC INNER JOIN Students ON SSEC.STUDENT_NU = Students.StudentNumber
WHERE (((SSEC.SEMESTER)='1') AND ((SSEC.COURSE_NUM)="CS91") AND
((SSEC.SECTION_NU)=1));

note the "0 as Condition, 0 as Excused, #5/28/98# as Date"... I can not
update the Recordset because it is based on a SQL statement and the
Condition field is not in any table, per se.

Does that make sense? After I update the fields I need to I am planning on
append the modified records to another table via code.

Help?

Thanks,
Paul.

Alan Vouk wrote in message ...

>Ok What I would do is one of two things. You can place the SQL statement in
>the RecordSource property of the form. You can then clone that recordset
and
>move as you wish using VBA code. To equate the two recordsets equate the
>bookmark properties of the recordset and the form. It should look like
>something like this:
>
> strSQL = "Select " blah blah blah
> DoCmd.OPenForm "frm_SomeForm"
> forms![frm_SomeForm".Recordsource = strSQL
>
>To clone that recordset then you would
> Set rst = forms![frm_SomeForm].RecordsetClone
> rst.MoveLast - move to whatever place you want
> forms![frm_SomeForm].Bookmark = rst.Bookmark - this will move the
>forms place to the same record as in your recordset rst
>
>Note:
> Make surte that you close and then destroy your rst object and destroy
>your db object/You can do this by setting them to nothing. Picky I know -
>sorry
>
>Hope it helps,
>
>Alan
>av...@bellsouth.net
>

>Paul D. Parisi wrote in message ...