A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dynamic recordset



 
 
Thread Tools Display Modes
  #21  
Old January 26th, 2009, 12:20 AM posted to microsoft.public.access.forms
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default 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  
Old January 26th, 2009, 12:24 AM posted to microsoft.public.access.forms
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default 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  
Old January 26th, 2009, 04:23 AM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default 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  
Old January 26th, 2009, 04:25 AM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default 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  
Old January 26th, 2009, 04:26 AM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default 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  
Old January 26th, 2009, 03:47 PM posted to microsoft.public.access.forms
Danny Lesandrini
external usenet poster
 
Posts: 109
Default 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  
Old January 26th, 2009, 03:52 PM posted to microsoft.public.access.forms
Danny Lesandrini
external usenet poster
 
Posts: 109
Default 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  
Old January 26th, 2009, 04:12 PM posted to microsoft.public.access.forms
RoyVidar
external usenet poster
 
Posts: 417
Default 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  
Old January 26th, 2009, 04:15 PM posted to microsoft.public.access.forms
RoyVidar
external usenet poster
 
Posts: 417
Default 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  
Old January 26th, 2009, 10:08 PM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.