updateable snapshot recordset in sub form.

[Chris]

Hi

I have createed a form with a sub form. I populate this subform with data
with the code below.

When I cursor into a cell in the tabular datasheet I can't edit the cell in
any way.

If i create a subform that uses a design time recordsource (i.e. a table)
without writing any VBA code I can edit it no problem but now that I am
loading it programmatically I can' t get a updateable recordset that
automatically updates my remote SQL database when ever I change values in the
table cells.

I'm using an Access data project.

How do I make it updateable snapshot? as found in the properties sheet at
design time?


here is my code to fill the subform.

Me.subReportMain.Form.RecordSource = ""

If storeprocedurename = "SearchByTransitGL" Then
strsql = "EXEC SearchByTransitGL '" & searchvalue1 & "'," & searchvalue2
Else
'strsql = "EXEC searchbygl '" & searchvalue1 & "'"
strsql = "EXEC " & storeprocedurename & "'" & searchvalue1 & "'"
End If

Set cnn = New ADODB.Connection
cnn.ConnectionString = Application.CurrentProject.BaseConnectionString
cnn.Open
Set Rst = New ADODB.Recordset
With Rst
.CursorLocation = adUseClient
.Open Source:=strsql, _
ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic

If .RecordCount = 0 Then
MsgBox "No Records Found"
Else

Set Forms!frmMainViewEdit.Form!subReportMain.Form.Recordset =
Rst

End If
.Close
End With
cnn.Close
Set Rst = Nothing
Set cnn = Nothing

thanks for looking at this.

Chris

 

[Rick Brandt]

Hi

I have createed a form with a sub form. I populate this subform with
data with the code below.

When I cursor into a cell in the tabular datasheet I can't edit the
cell in any way.

If i create a subform that uses a design time recordsource (i.e. a
table) without writing any VBA code I can edit it no problem but now
that I am loading it programmatically I can' t get a updateable
recordset that automatically updates my remote SQL database when ever
I change values in the table cells.

I'm using an Access data project.

How do I make it updateable snapshot? as found in the properties
sheet at design time?


here is my code to fill the subform.

Me.subReportMain.Form.RecordSource = ""

If storeprocedurename = "SearchByTransitGL" Then
strsql = "EXEC SearchByTransitGL '" & searchvalue1 & "'," &
searchvalue2 Else
'strsql = "EXEC searchbygl '" & searchvalue1 & "'"
strsql = "EXEC " & storeprocedurename & "'" & searchvalue1 & "'"
End If

Set cnn = New ADODB.Connection
cnn.ConnectionString =
Application.CurrentProject.BaseConnectionString cnn.Open
Set Rst = New ADODB.Recordset
With Rst
.CursorLocation = adUseClient
.Open Source:=strsql, _
ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic

If .RecordCount = 0 Then
MsgBox "No Records Found"
Else

Set
Forms!frmMainViewEdit.Form!subReportMain.Form.Recordset = Rst

End If
.Close
End With
cnn.Close
Set Rst = Nothing
Set cnn = Nothing

thanks for looking at this.

Chris

I don't think the result set from a Stored Procedure is ever editable.

 

[Chris]

Hi,

I forgot to mention that in my experiment without VBA I did use a stored
procedure
as my recordsource and it is updatebable.

just need to get it to be updateable from code.