Randym   1410 pts.  |   Feb 17 2009  7:23PM GMT

You say that you bind the form to the read only database. This is why you can’t make changes to the fields. If you don’t want a temp table, you will probably need to leave the form unbound and manully set the value of each field from the recordset. Then when you make a change to the field and update a new updatable recordset.

Darryn   370 pts.  |   Feb 18 2009  1:38PM GMT

Ok, I have passed the read-only recordset into an array, then created a new recordset, and added the same number of fields (and datatype etc) to the recordset, with the same field names. I have then opened the recordset, and passed the values from the array into the new recordset (I have double checked, and all the values are consistent between the 2).

I then bound the form to the new recordset, which has caused even stranger behaviour of the form. The number of rows displayed in the form is correct, but every field comes back as “#Error” (when I bind it to the original recordset it displays correctly). Now, what is even stranger, when I click in one of the list boxes, an error occurs, stating that the db engine could not find the object ‘~sq_fFORMNAME’ (not sure why it is looking for that form, but it is correct when it says it doesn’t exist). When I click on ‘Ok’ from the error message, it then lets me select a value from the list (Yes/No). Strangely, when I select one of them, the rest of the record then populates correctly, but the 2nd record doesn’t. I can then change the values of the first record as expected. However, when I click in the list box of the 2nd record, it immediately sets all the values in the form to ‘#Name?’, and says that the control can’t be edited as it is bound to an unknown field.

All very strange, and very frustrating………

Randym   1410 pts.  |   Feb 18 2009  5:35PM GMT

How are you binding the form to the recordset? What syntax are you using. Is it a linked table?

Darryn   370 pts.  |   Feb 19 2009  11:44AM GMT

No, no linked tables.
My form has 5 fields, with the field’s control source set to a, b, c, d and e respectively.

My code goes like something like this:
sub populate()
dim……

set rs = new adodb.recordset
rssql = “select a, b, c, ‘No’ as d, ‘No’ as e from table1″

with rs
.ActiveConnection = OracleConn
.Source = rssql
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Open
End with

countRs = rs.recordcount
if countRs > 0 then
rsArray = rs.GetRows(rs.recordcount)

set rs2 = new adodb.recordset
With rs2
.fields.append “a”, adBigInt,,AdFldUpdatable
.fields.append “b”, adBigInt,,AdFldUpdatable
.fields.append “c”, adChar,10,AdFldUpdatable
.fields.append “d”, adChar,3,AdFldUpdatable
.fields.append “e”, adChar,3,AdFldUpdatable
.index = “a”
.cursorlocation = adUseClient
.cursortype = adOpenStatic
.LockType = adLockOptimistic
.Open

For n = LBound(transA,2) to UBound(transA,2)

With rs2
.AddNew
For p = 0 to .Fields.Count - 1
.Fields(p).Value = transA(p,n)
Next p
.Update
End With
Next n

Set Me.Recordset = rs2

‘If I use
‘rs.ActiveConnection = Nothing
‘Set Me.Recordset = rs
‘it populates the form, but doesn’t allow any updates

End if

End sub

Darryn   370 pts.  |   Feb 19 2009  12:05PM GMT

Ok, got it working, but I don’t know why it wouldn’t work. Basically, I removed the form fields controlsource property, and then set the controlsources in the code, just before I set the me.recordset = rs2.

Thanks to all.