Form with Disconnected Recordset will not allow edits.

K

KevinWB

I have a form that I want to have use a disconnected recordset. The
disconnected recordset works fine, but I am unable to update or even type in
the textboxes. When I try I get a "Recordset is not updateable message"

My intention is to have the recordset reconnected and for it to do a batch
update when the client pushes the "Save" button.

I know I could have the text boxes unbound, load the values from the
disconnected recordset, then disconnect. And reconnect and load the values
back in to the recordset to save, but them seems to be a lot of extra work.

Below is the code I'm using to load my recordset. Does anyone have any ideas
on where I'm going wrong?
'*****************************
Private Sub UpdateRecordSource_New()

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

On Error GoTo ErrHandler:

cnn.Open GetStrConnect
rst.CursorLocation = adUseClient

rst.CursorType = adOpenStatic
rst.LockType = adLockBatchOptimistic
Set cmd.ActiveConnection = cnn

cmd.CommandText = "rs_Students_version_8"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters("@iIntakeID") = 3304
rst.Open cmd

Set Me.Recordset = rst

rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing
Set cmd = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume Next
End Sub
 
B

Brendan Reynolds

In Access 2000, assigning an ADO recordset to the Recordset property of a
form makes the form read only. This is no longer the case in Access 2003.
I'm not sure about Access 2002.

The following code works in Access 2003 ...

Option Compare Database
Option Explicit

Private mrst As ADODB.Recordset

Private Sub Command34_Click()

Set mrst.ActiveConnection = CurrentProject.Connection
mrst.UpdateBatch
Set mrst.ActiveConnection = Nothing

End Sub

Private Sub Form_Open(Cancel As Integer)

Set mrst = New ADODB.Recordset
With mrst
Set .ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.Source = "SELECT * FROM Employees"
.Open
Set .ActiveConnection = Nothing
End With
Set Me.Recordset = mrst

End Sub

Private Sub Form_Unload(Cancel As Integer)

If Not mrst Is Nothing Then
If mrst.State <> adStateClosed Then
mrst.Close
End If
End If

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top