A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dynamic recordset



 
 
Thread Tools Display Modes
  #11  
Old January 25th, 2009, 10:24 PM posted to microsoft.public.access.forms
Mark A. Sam[_3_]
external usenet poster
 
Posts: 468
Default Dynamic recordset

Actually I meant articles. A readonly recordset doesn't interest me. You
said you can do this with DAO recordsets? That is what I'd be interested
in, but only if the recordsets underlying table is updateable with the form.




"Ken Snell (MVP)" wrote in message
...
Hmmmm.... No, I am not aware of any examples or samples on internet.

See these articles for more details:

How to bind Microsoft Access forms to ADO recordsets
http://support.microsoft.com/kb/281998

ACC2000: Forms Based on ADO Recordsets Are Read-Only
http://support.microsoft.com/kb/227053


--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/




"Mark A. Sam" wrote in message
...
Any examples anywhere?

"Ken Snell (MVP)" wrote in message
...
The form will use the Recordset's data as the data for the form. It
works best for DAO recordsets. ADODB recordsets can be used, but the
form will be a read-only form (no editing of data allowed).





  #12  
Old January 25th, 2009, 10:38 PM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Dynamic recordset

"Mark A. Sam" wrote in
:

I may be misreading what you are attempting to do, but it seems
like you are trying to set the Roecordsource property of the form
[Form_frmDoobloFileDetails] from a recordset variable:

Set Form_frmDoobloFileDetails.Recordset = rsNew

I believe this is wrong, however I may be misunderstanding or not
familiar with your method.


What you quote there is not the .Recordsource being set, but the
form's .Recordset. The Recordset property of a form was introduced
in Access 2000 and is settable to a predefined DAO recordset (never
an ADO recordset, as form recordsets are always DAO).

So, there is nothing wrong per se with the quoted line of code.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #13  
Old January 25th, 2009, 10:42 PM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Dynamic recordset

=?Utf-8?B?T3JuYQ==?= wrote in
:

I have a form with a listbox control, the listbox displays access
db file name in a specific directory, each database has only one
table with the same name and exact schema but with different data.
each time the user double clicks on one of the rows in the
listbox, a dialog form opens and displays the data in the table.
when I use an ADODB recordset object to view the data in the
remote database, it works ok. but if I use a recordset I generated
in my code (with additional fields) , I get the correct number of
records but the data displayed in each one of the text boxs is
"#Error".


Why are you choosing an ADO recordset? You might want to review this
article, which was written after Access 2000 came out (and perhaps
the noneditable nature of and ADO form Recordset has changed):

http://trigeminal.com/usenet/usenet022.asp?1033

Also, why not just set the form's recordsource to an appropriate SQL
string (which can include a connect string if you need to use
different back ends)? Why bother with all the trouble of setting up
a recordset in memory when assigning a SQL string to the form's
Recordsource will do all that for you?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #14  
Old January 25th, 2009, 10:43 PM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Dynamic recordset

"Mark A. Sam" wrote in
:

Are you saying that the form will take on the records of the
recordset variable or the other way around. I have never had
experience setting the recordsource of a form from a variable. I
wish I had.


You don't set the recordsource to a variable -- you set it to a SQL
string, which can include a connect string (allowing you to change
the back end on the fly).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #15  
Old January 25th, 2009, 10:45 PM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Dynamic recordset

"Mark A. Sam" wrote in
:

A readonly recordset doesn't interest me. You
said you can do this with DAO recordsets? That is what I'd be
interested in, but only if the recordsets underlying table is
updateable with the form.


You don't need to muck around with the .Recordset property of the
form at all -- all you need to do is set the form's Recordsource to
a valid SQL string, which can include a connect string to allow you
to change the back end you're drawing the data from.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #16  
Old January 25th, 2009, 10:53 PM posted to microsoft.public.access.forms
RoyVidar
external usenet poster
 
Posts: 417
Default Dynamic recordset

David W. Fenton wrote:
The Recordset property of a form was introduced
in Access 2000 and is settable to a predefined DAO recordset (never
an ADO recordset, as form recordsets are always DAO).


The form recordset can also be set to ADO recordsets. However, if
it's based on Jet data, it isn't updateable in the 2000 version,
but for later versions it is.

ACC2000: Forms Based on ADO Recordsets Are Read-Only
http://support.microsoft.com/default...b;EN-US;227053

How to bind Microsoft Access forms to ADO recordsets
http://support.microsoft.com/kb/281998/EN-US/

Why one should wish to do so, is another question ;-)

--
Roy-Vidar


  #17  
Old January 25th, 2009, 10:54 PM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Dynamic recordset

In a previous reply (which for some reason my news server is not
showing me), I stated that you can't set a form's .Recordset
property to an ADO recordset. That was an error -- you *can* set it
to an ADO recordset, but so far as I know, that recordset will be
read-only.

To be frank, I don't see much value in setting the .Recordset
property of a form. A form's Recordsource will create the relevant
recordset for the form, but setting the .Recordset property seems
like an awful lot of work for very little benefit.

The only scenario I can think of where it would make sense is one
I've contemplate using but never actually tried, and that's using a
non-editable continuous form as a listview and using a single form
to display the detail of the record selected in the listview. If you
assigned the same recordset to both forms, you'd be saving
resources, whereas if you used the typical method of setting the
Recordsource of each form, you'd be loading two different
recordsets. Then an update to the detail would have to refresh the
list view. If you were editing the same recordset, that refresh
shouldn't be necessary.

I've never tried this, and don't know if one would have to
synchronize bookmarks in the two forms or not, but it always seemed
an intriguing possibility. In the app where I seriously contemplated
it, I ended up having the detail form be completely unbound,
instead, which was certainly a lot more difficult than defining two
bound recordsources, and possibly more complicated than using the
same recordset in two forms.

But I just can't see any justification for setting a form's
recordset when that recordset is being used only by the single form.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #18  
Old January 25th, 2009, 10:54 PM posted to microsoft.public.access.forms
Orna
external usenet poster
 
Posts: 18
Default Dynamic recordset

Hi,

As I mentioned in my previuos post, the reason why I don't use an sql
statement is because I need to join data from a local table and a remote
table. the remote table is not a linked table, for each row in the list box I
regenerate the connection string.

Orna.

"David W. Fenton" wrote:

=?Utf-8?B?T3JuYQ==?= wrote in
:

I have a form with a listbox control, the listbox displays access
db file name in a specific directory, each database has only one
table with the same name and exact schema but with different data.
each time the user double clicks on one of the rows in the
listbox, a dialog form opens and displays the data in the table.
when I use an ADODB recordset object to view the data in the
remote database, it works ok. but if I use a recordset I generated
in my code (with additional fields) , I get the correct number of
records but the data displayed in each one of the text boxs is
"#Error".


Why are you choosing an ADO recordset? You might want to review this
article, which was written after Access 2000 came out (and perhaps
the noneditable nature of and ADO form Recordset has changed):

http://trigeminal.com/usenet/usenet022.asp?1033

Also, why not just set the form's recordsource to an appropriate SQL
string (which can include a connect string if you need to use
different back ends)? Why bother with all the trouble of setting up
a recordset in memory when assigning a SQL string to the form's
Recordsource will do all that for you?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

  #19  
Old January 25th, 2009, 11:09 PM posted to microsoft.public.access.forms
RoyVidar
external usenet poster
 
Posts: 417
Default Dynamic recordset

Orna wrote:
Hi,

I have a form with a listbox control, the listbox displays access db
file name in a specific directory, each database has only one table
with the same name and exact schema but with different data. each
time the user double clicks on one of the rows in the listbox, a
dialog form opens and displays the data in the table.
when I use an ADODB recordset object to view the data in the remote
database, it works ok. but if I use a recordset I generated in my
code (with additional fields) , I get the correct number of records
but the data displayed in each one of the text boxs is "#Error".
Below is my code, rsNew is the recordset I am trying to attach to my
dialog. I am trying to solve this for few days and I will appreciate
any help. Thanks,
Orna.

ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& sFilePath & "\" & sFileName & ";Persist Security Info=False"
con.Open ConStr
rs.Open "SELECT * FROM Export1", con, adOpenKeyset,
adLockReadOnly, adCmdText
Set rsVisits = dbs.OpenRecordset(sTableName,
dbOpenDynaset, dbReadOnly)

rsNew.Fields.Append "SbjNum", rs.Fields("SbjNum").Type,
50 rsNew.Fields.Append "vDate", rs.Fields("vDate").Type,
50 rsNew.Fields.Append "Srvyr", rs.Fields("Srvyr").Type,
50 rsNew.Fields.Append "SbjNam",
rs.Fields("SbjNam").Type, 50 rsNew.Fields.Append
"IsImported", adBoolean

rsNew.Open

If rs.RecordCount 0 Then
rs.MoveFirst
Do While Not rs.EOF
With rsNew
rsNew.AddNew
!SbjNum = rs!SbjNum
!vDate = rs!vDate
!Srvyr = rs!Srvyr
!SbjNam = rs!SbjNam
End With
rsVisits.Filter = "DoobloId=" & rs!SbjNum
If rsVisits.RecordCount = 0 Then
rsNew!IsImported = False
Else
rsNew!IsImported = True
End If
rsNew.Update
rs.MoveNext
Loop
End If

DoCmd.OpenForm "frmDoobloFileDetails", acNormal
Set Form_frmDoobloFileDetails.Recordset = rsNew

If rsNew.RecordCount 0 Then

Form_frmDoobloFileDetails.txtDoobloCode.ControlSou rce =
"SbjNum" Form_frmDoobloFileDetails.txtDate.ControlSource
= "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSourc e = "Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource = "SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.Contro lSource =
"IsImported" End If



The exact reasons escape me, but I think it might have to do with
meta data that is available when opening a recordset through ADO or
DAO vs when creating disconnected recordsets, or creating new fields
on existing recordsets - it wont in continuous forms when assigned to
the .recordset of a form.

But I have more than once experimented with assigning disconnected ADO
recordsets to forms, and made it work, at least in test.

What properties have you set on rsNew? Try with say adOpenStatic and
adLockOptimistic, and to be specific, cursorlocation = adUseClient.

--
Roy-Vidar


  #20  
Old January 25th, 2009, 11:11 PM posted to microsoft.public.access.forms
Mark A. Sam[_3_]
external usenet poster
 
Posts: 468
Default Dynamic recordset

David,

I know how to work with recordsets and thinking about it, I don't see any
utility in populating a form from a variable unless it is coming from
another database.

God Bless,

Mark


"David W. Fenton" wrote in message
36.94...
"Mark A. Sam" wrote in
:

A readonly recordset doesn't interest me. You
said you can do this with DAO recordsets? That is what I'd be
interested in, but only if the recordsets underlying table is
updateable with the form.


You don't need to muck around with the .Recordset property of the
form at all -- all you need to do is set the form's Recordsource to
a valid SQL string, which can include a connect string to allow you
to change the back end you're drawing the data from.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.