Appending Field to Disconnected Recordset

[MrMerlotti]

I am using a disconnected recordset bound to a DS form. I would like to append a new field to the recordset, bind that form to a text box on the form and allow the user to enter a value, which is reflected in the underlying disconnected recordset.

Everything else seems to work OK - the code runs without errors, but the control bound to the new field just displays #Name? and is not updatable.
A sample of the code below - in the Form_Open event

rsSELECT = "SELECT * FROM tblPO"

Dim cn as ADODB.Connection
Dim rsPO as ADODB.Recordset

Set cn = CurrentProject.Connection
Set rsPO = New ADODB.Recordset

With rsPO
     .Source = rsSELECT
     .LockType = adLockOptimistic
     .CursorType = adOpenKeyset
     .CursorLocation = adUseClient
     .Fields.Append "OrderWgt", adInteger
     .Open
End With

Set rsPO.ActiveConnection = Nothing
Set Me.Recordset = rsPO

Any help much appreciated!

PS My thanks to jordanking for pointing me in the direction of Disconnected Recordsets (thread705-1446576)

 

[JoeAtWork]

Try adding the new field after you have disconnected the recordset.

 

[MrMerlotti]

When I try that it gives the following error message: Operation is not allowed in this context

 

[PHV]

Have you tried something like this ?

rsSELECT = "SELECT *, 0 AS OrderWgt FROM tblPO"
Dim cn as ADODB.Connection
Dim rsPO as ADODB.Recordset
Set cn = CurrentProject.Connection
Set rsPO = New ADODB.Recordset
With rsPO
     .Source = rsSELECT
     .LockType = adLockOptimistic
     .CursorType = adOpenKeyset
     .CursorLocation = adUseClient
     .Open
End With
Set rsPO.ActiveConnection = Nothing
Set Me.Recordset = rsPO

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMerlotti]

That works - and gives a field to bind to the control but I can't get the control to update the underlying recordset.

 

[PHV]

What is supposed to be OrderWgt ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMerlotti]

OrderWgt is a field to hold a (new) value as an integer.

The product is sold by weight. OrderWgt is used to allocate the Sales Order order-weight across multiple Purchase Orders - until the order has been fulfilled. (At the same time other values with the Purchase Order can be modified).

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMerlotti]

Thanks PH

I have modified the original SELECT statement to include a join to a table where OrderWgt is held which does the trick.

Still be nice to know though...