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

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

"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/

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/

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/

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/

"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/

"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/

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

"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/