Dynamic recordset

[Orna]

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.ControlSource = "SbjNum"
Form_frmDoobloFileDetails.txtDate.ControlSource = "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSource = "Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource = "SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.ControlSource =
"IsImported"
End If

 

[Mark A. Sam]

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

 

[Ken Snell \(MVP\)]

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 \(MVP\)]

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/

 

[Ken Snell \(MVP\)]

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.

 

[Mark A. Sam]

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

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 Snell \(MVP\)]

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/

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

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]

Any examples anywhere?

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/

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

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 Snell \(MVP\)]

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/

 

[Orna]

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.ControlSource =
"SbjNum"
Form_frmDoobloFileDetails.txtDate.ControlSource = "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSource = "Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource = "SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.ControlSource =
"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

 

[Mark A. Sam]

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.

 

[David W. Fenton]

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]

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]

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]

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.

 

[RoyVidar]

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.aspx?scid=kb;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 ;-)

 

[David W. Fenton]

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.

 

[Orna]

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.

 

[RoyVidar]

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.ControlSource =
"SbjNum" Form_frmDoobloFileDetails.txtDate.ControlSource
= "vDate"
Form_frmDoobloFileDetails.txtSurviyer.ControlSource = "Srvyr"
Form_frmDoobloFileDetails.txtBranch.ControlSource = "SbjNam"
Form_frmDoobloFileDetails.chkRecordImported.ControlSource =
"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.

 

[Mark A. Sam]

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