I am using Access 2010 with linked tables from SQL Server 2008.
I have one user-editable field on my form, which is the only field that is auto-saved by Access into the Result table, when navigating from record to record or when the form is closed.
Since I do need to save changes that the user makes, I have a Save button that inserts/updates the Result table. I would like to use the Auto-Save feature to my advantage by having Access save all the fields on the form into the Result table (which is what I am doing in my Save button event). However, Access only updates the Note box which is the field that the user edits. The other fields on the form, which are part of the Result table, show up as NULL in the table.
How do I auto-save the entire record in the Result table? Any settings in Access to do this? Thanks in advance.
Only bound controls get saved. If your controls contain expressions in their ControlSource properties, they are not bound. To be bound, the ControlSource must reference a column from the form's RecordSource.
This is the event I am using to populate the form, but when I try to type in the QCNote textbox, the status bar shows the message that "This record set is not updatable". The form's record source is set to blank.
Private Sub Form_Load()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
DocID = [Forms]![QCDocAttributes]![DocID]
DocumentType = [Forms]![QCDocAttributes]![Document Type]
AttributeDesc = [Forms]![QCDocAttributes]![AttributesDropdown]
AttributeID = DLookup("[QCAttributeID]", "QC_QCAttribute", "[Description] = " & "'" & AttributeDesc & "'")
AssignmentID = DLookup("[QCAssignmentID]", "[QC_QCAssignment]", "[QCID] =" & [Forms]![QCDocAttributes]![DocID])
strSql = "SELECT " & DocID & " AS DocID,'" & DocumentType & "' AS DocumentType, B.Description , B.QCDecisionPointID,C.QCNote, C.QCResultDecisionPointID FROM (QC_QCAttributeDecisionPointAsc A INNER JOIN QC_QCDecisionPoint B ON A.QCDecisionPointID = B.QCDecisionPointID) LEFT JOIN QC_QCResultDecisionPoint C ON (B.QCDecisionPointID=C.QCDecisionPointID AND C.QCAssignmentID= " & AssignmentID & " ) WHERE (A.QCAttributeID=" & AttributeID & " );"
.Source = strSql
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End Sub
The control osurce of the text box is set to QCNote value from the recordset. What is wrong here - using multiple joins in the source query?
I thought perhaps I should put the query in a SQL SP and call that stored procedure to populate the form, but I cannot get this code to work:
Private Sub Form_Load()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "Server"
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = "Dev"
.Open
End With
Set param1 = cmd.CreateParameter("@QCAssignmentID", adBigInt, adParamInput)
param1.Value = DPQCAssignmentID
cmd.Parameters.Append param1
AttributeDesc = [Forms]![QCDocAttributes]![AttributesDropdown]
AttributeID = DLookup("[QCAttributeID]", "QC_QCAttribute", "[Description] = " & "'" & AttributeDesc & "'")
Set param2 = cmd.CreateParameter("@QCAttributeID", adBigInt, adParamInput)
param2.Value = AttributeID
cmd.Parameters.Append param2
With cmd
.ActiveConnection = cn
.CommandText = "stp_GetDecisionPoints"
.CommandType = adCmdStoredProc
Set Me.Recordset = .Execute
End With
End Sub
