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. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|