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 |
#1
|
|||
|
|||
Dynamic recordset
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 |
#2
|
|||
|
|||
Dynamic recordset
Orna,
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. Instead of populating the form from a recordset variable (rsNew), first populate a table from the data in rsNew then set the RecordSource property of the form like this: Form_frmDoobloFileDetails.RecordSource = "[SomeTable]" God Bless, Mark A. Sam "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 |
#3
|
|||
|
|||
Dynamic recordset
"Mark A. Sam" wrote in message
... Orna, 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. Mark, one can set the Recordset property of a form to a recordset object. What the poster is trying to do is a legitimate action in ACCESS. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ |
#4
|
|||
|
|||
Dynamic recordset
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 |
#5
|
|||
|
|||
Dynamic recordset
Additionally, is the rsNew recordset being declared as a global recordset
(i.e., do you Dim it in the Declarations section of the form's module)? If it's a local recordset, meaning you Dim it in the Sub or Function in the form's module, then it goes away after the sub or function finishes running. As a global recordset object, you'll need to put Close and Set to Nothing steps in the form's Unload event procedure to avoid any possible memory leaks. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Ken Snell (MVP)" wrote in message ... 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 |
#6
|
|||
|
|||
Dynamic recordset
Ken,
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. Mark "Ken Snell (MVP)" wrote in message ... "Mark A. Sam" wrote in message ... Orna, 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. Mark, one can set the Recordset property of a form to a recordset object. What the poster is trying to do is a legitimate action in ACCESS. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ |
#7
|
|||
|
|||
Dynamic recordset
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). -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Mark A. Sam" wrote in message ... Ken, 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. Mark "Ken Snell (MVP)" wrote in message ... "Mark A. Sam" wrote in message ... Orna, 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. Mark, one can set the Recordset property of a form to a recordset object. What the poster is trying to do is a legitimate action in ACCESS. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ |
#8
|
|||
|
|||
Dynamic recordset
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). -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "Mark A. Sam" wrote in message ... Ken, 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. Mark "Ken Snell (MVP)" wrote in message ... "Mark A. Sam" wrote in message ... Orna, 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. Mark, one can set the Recordset property of a form to a recordset object. What the poster is trying to do is a legitimate action in ACCESS. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ |
#9
|
|||
|
|||
Dynamic recordset
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). |
#10
|
|||
|
|||
Dynamic recordset
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 |
Thread Tools | |
Display Modes | |
|
|