View Single Post
  #19  
Old January 25th, 2009, 11:09 PM posted to microsoft.public.access.forms
RoyVidar
external usenet poster
 
Posts: 417
Default Dynamic recordset

Orna wrote:
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



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.

--
Roy-Vidar