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 |
#21
|
|||
|
|||
Dynamic recordset
The Help files contain some information on this. Search on Form Object and
select Recordset from the Properties list. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Mark A. Sam" wrote in message ... 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). |
#22
|
|||
|
|||
Dynamic recordset
Here's a link to a Microsoft article about error values (such as #Error and
#Name?): ACC2000: Troubleshooting Tips for Error Values http://support.microsoft.com/default...b;en-us;209132 I don't have enough time right now to study what you've posted about the code. I will do that as soon as I can and post back. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Orna" wrote in message ... Hi Ken, below is the complete code of the sub procedure. My application is importing records from another application and I would like to give an indication to the user whether the record was imported or not. as you can see I have another recordset called rsVisits and if I find the record there the field "IsImported" set to True. I didn't have any other idea how to implement it. When I moved the line that sets the forms' recordset to rsNew I still get an "#Error" in the text boxs When I added a requery to the form after setting the recordset I get "#Name?" in the fields. I also tried to move the dim of rsNew to one of my modules as public recordset, this also didn't change the error I get. Do you know what is the meaning of the "#Error" text in a textbox, I couldn't find any reference to it in the internet? Thanks, Orna. --------------------------- Private Sub lstFilesList_DblClick(Cancel As Integer) Dim VarItem As Variant Dim rs As New ADODB.Recordset Dim ConStr As String Dim con As New ADODB.Connection Dim rsDooblo As DAO.Recordset Dim dbs As DAO.Database Dim rsVisits As DAO.Recordset Dim i As Integer Dim sFilePath As String Dim sFileName As String Dim sTableName As String Set rsNew = New ADODB.Recordset Set dbs = CurrentDb sFilePath = DLookup("ImportPath", "tblProjects", "ProjectId=" & cmbSekerType.Value) sTableName = DLookup("tblVisitsName", "tblProjects", "ProjectId=" & cmbSekerType.Value) For Each VarItem In lstFilesList.ItemsSelected If VarItem 0 Then sFileName = lstFilesList.ItemData(VarItem) 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" Form_frmDoobloFileDetails.lblFileName.Caption = "רשימת הבקרות בקובץ: " & sFileName Else Form_frmDoobloFileDetails.lblFileName.Caption = "אין רשומות בקובץ: " & sFileName Form_frmDoobloFileDetails.lblFileName.ForeColor = 255 End If End If Next VarItem End Sub "Ken Snell (MVP)" wrote: I believe you've not posted the complete code? I don't see anything in this code that establishes the rsNew recordset object? Is it supposed to be a subset of the rs recordset's data? If yes, why not just open it as a filtered or unfiltered recordset from the rs recordset, instead of writing data into it by a loop through rs data? Also, I would move your setting of the ControlSource properties to BEFORE you set the form to that rsNew recordset. Or, do a requery of the form after you set those properties. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Orna" wrote in message ... 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 |
#23
|
|||
|
|||
Dynamic recordset
"David W. Fenton" wrote in
36.94: 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). As I said in another post, this is incorrect. The form's Recordset property can be set to an ADO recordset, but it will be read-only. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#24
|
|||
|
|||
Dynamic recordset
=?Utf-8?B?T3JuYQ==?= wrote in
: 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. I really don't see the issue. You can specify a connect string in the FROM clause of the SQL. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#25
|
|||
|
|||
Dynamic recordset
"Mark A. Sam" wrote in
: 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. You don't need it even then. There are at least two ways to do it: 1. specify the connect string with the table name in the FROM clause, OR 2. define a derived table using its own connect string. The latter is probably somewhat neater to implement, but I doubt there'd be any difference between the two in terms of performance. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#26
|
|||
|
|||
Dynamic recordset
Orna:
I've done what you are doing, and while I agree with others that there might be an easier way to accomplish it, your problem seems much simpler than that. If the text boxes show #Error then you need to verify that their ControlSource property values appear exactly as they do in the recordset you're creating. I could be mistaken, but I believe your issue is here, in these lines. You can't open a form and then load its recordset and control source from another place. This code to set the recordset should be in the form's Open() event. Once the form is open, it's too late. Set Form_frmDoobloFileDetails.Recordset = rsNew 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" Run the code in the On Open event of the target form and then do this ... Me!txtDoobloCode.ControlSource = "SbjNum" Me!txtDate.ControlSource = "vDate" Me!txtSurviyer.ControlSource = "Srvyr" Me!txtBranch.ControlSource = "SbjNam" Me!chkRecordImported.ControlSource = "IsImported" Set Me.Recordset = rsNew -- Danny J Lesandrini www.amazecreations.com "Orna" wrote in message ... 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 |
#27
|
|||
|
|||
Dynamic recordset
David, I don't know if such an ADO recordset is read only when pointing to
an Access MDB file, but I built an app that was entirely driven on ADO recordsets, dynamically pointing to SQL Server databases. I know that probably seems odd, but there was one HUB database that had login and task info, and n number of client databases that were referenced in the Task database. A user would load their task list and double clicking a task would need to load an editable recored from the appropriate client database. Like I said, this was an odd application, but it taught me how to leverage the .Recordset property of forms and controls. Seems like the .Recordset property of reports was not as simple or flexible, but I forget the details of the issues I faced. Anyhow, those recordsets were editable and after setting it, the rest was seamless from VBA point of view. -- Danny J Lesandrini www.amazecreations.com "David W. Fenton" wrote in message 36.94... 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/ |
#28
|
|||
|
|||
Dynamic recordset
Danny Lesandrini wrote:
Seems like the .Recordset property of reports was not as simple or flexible, but I forget the details of the issues I faced. Allthough the report exposes a recordset property, it can only be set in an ADP, as far as I know. -- Roy-Vidar |
#29
|
|||
|
|||
Dynamic recordset
David W. Fenton wrote:
"David W. Fenton" wrote in 36.94: 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). As I said in another post, this is incorrect. The form's Recordset property can be set to an ADO recordset, but it will be read-only. No, you can very well have updateable forms that are based on ADO recordsets, except as I stated elsewhere if you're using Access 2000 AND it's based on Jet data With Access 2000, you can have updateable forms based on ADO recordset if you're using MSDataShape and SQL Server OLEDB providers. For later versions of Access, you can have updateable forms also when the ADO recordset is based on Jet data. Here's a repost of the links 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/ -- Roy-Vidar |
#30
|
|||
|
|||
Dynamic recordset
RoyVidar wrote in
: David W. Fenton wrote: "David W. Fenton" wrote in 36.94: 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). As I said in another post, this is incorrect. The form's Recordset property can be set to an ADO recordset, but it will be read-only. No, you can very well have updateable forms that are based on ADO recordsets, except as I stated elsewhere if you're using Access 2000 AND it's based on Jet data That's not what MichKa's article says. Did it change after he posted that? And is this specific to A2K? -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|