tommyboy115
asked on
Trouble Editing an ADO Recordset bound to an Microsoft Access Form
I'm new to using ADO to open recordsets and use them with Access forms. I typically use queries as the recordsets. I'm trying to make the move away from that and this is my first attempt. I'm able to bind the recordset, but can't edit any of the data once available on the form. It's all locked. What am I doing wrong?
Thanks!
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "sqloledb"
.ConnectionString = "DRIVER=ODBC DRIVER 11 FOR SQL SERVER;SERVER=**;DATABASE= **;UID=**; PWD=**"
.CursorLocation = adUseServer
.Open
End With
Set rs = New ADODB.Recordset
With rs
.Source = "SELECT * FROM GLOBAL_CONTACTS WHERE CONTACTID = " & [Forms]![DD_GLOBAL]![GLOBA L_CONTACTI D]
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With
Set Me.Recordset = rs
Form_Open_Exit:
Exit Sub
Form_Open_Err:
MsgBox ""
Resume Form_Open_Exit
Thanks!
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "sqloledb"
.ConnectionString = "DRIVER=ODBC DRIVER 11 FOR SQL SERVER;SERVER=**;DATABASE=
.CursorLocation = adUseServer
.Open
End With
Set rs = New ADODB.Recordset
With rs
.Source = "SELECT * FROM GLOBAL_CONTACTS WHERE CONTACTID = " & [Forms]![DD_GLOBAL]![GLOBA
.ActiveConnection = cn
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With
Set Me.Recordset = rs
Form_Open_Exit:
Exit Sub
Form_Open_Err:
MsgBox ""
Resume Form_Open_Exit
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Jim! That reference worked! If I want to create a new record instead of looking up an existing one, how should I change my code? Scold me if I should be asking that in a new question. Thanks!
<< If I want to create a new record instead of looking up an existing one, how should I change my code? >>
You'd create an empty recordset and let the form add the record. best way to do that is include a WHERE clause of:
WHERE 1 = 0
Jim.
You'd create an empty recordset and let the form add the record. best way to do that is include a WHERE clause of:
WHERE 1 = 0
Jim.
ASKER