Filling data in a listform using temporary recordset 1

[tobsen69]

I have a listform in an access-adp. The data comes from an sqlserver database and is first loaded into a recordset. Then, I copy all the tabledefinition and data to an other recordset and add a field "selected" with a defaultvalue of 0 for a checkbox. After that, the manipulated recordset is bound to the Me.Recordset property of the listform.

The loading and creation of the recordset/data seems to work fine, but the listform shows empty rows. When I first click on a row, the data in that row is displayed correctly. But all of the following clicks show "#name?" in the clicked field.

Any ideas, why there is no data visible?

rsLocal.Open "select * from MyView", CurrentProject.Connection

For i = 0 To rsLocal.Fields.count - 1
  rsTemp.Fields.Append rsLocal.Fields(i).name, rsLocal.Fields(i).Type, rsLocal.Fields(i).DefinedSize, adFldIsNullable
Next
rsTemp.Fields.Append "selected", adTinyInt, 2, adFldUpdatable

rsTemp.Open

rsLocal.MoveFirst
While Not rsLocal.EOF
  rsTemp.AddNew
  For i = 0 To rsLocal.Fields.count - 1
    rsTemp.Fields(i) = rsLocal.Fields(i)
  Next
  rsTemp.Fields(rsLocal.Fields.count) = 0
  rsLocal.MoveNext
Wend

rsTemp.MoveFirst
Set Me.Recordset = rsTemp

 

[RoyVidar]

You could probably do this less convoluted, but I think the challenge might be the properties of the "temp" recordset. I think in particular the lock type and cursor location. When you create the temp recordset, try

[tt]set rsTemp = New ADODB.Recordset
With rsTemp
.CursorLocation = adUseClient
.LockType = adOpenOptimistic
End With[/tt]

Roy-Vidar

 

[tobsen69]

Hi Roy!

Thanks for the tip... Now everything displays correctly. "Locktype" is the solution.

Bye!