Save Changes Per Row in Continous Form 1

[larrydavid]

Hello,

I'm updating 2 columns in my back-end SQL Server table based on the value in my dropdown list:

Private Sub ComboReturnSource_AfterUpdate()

Dim strSQL As String
Dim dbs As DATABASE

If Me.ComboChkReturnSource = "TEST1" Then
Set dbs = CurrentDb()
strSQL = "UPDATE dbo_TEST1 [Entity] = 0808 WHERE dbo_TEST1.ID = '" & Me.ID.Value & "'"
dbs.Execute strSQL, dbFailOnError + dbSeeChanges
Set dbs = Nothing

Set dbs = CurrentDb()
strSQL = "UPDATE dbo_TEST1 SET [ExpCode] = 20230 WHERE dbo_TEST1.ID = '" & Me.ID.Value & "'"
dbs.Execute strSQL, dbFailOnError + dbSeeChanges
Set dbs = Nothing
End If

The problem I'm having is when I tab to the next field in the form it keeps asking to Copy to Clipboard or Drop Changes. I've tried setting Me.Dirty = True after the update but that isn't working.

Any help with this would be greatly appreciated.

Thanks,
Larry

 

[dhookom]

Your code shouldn't work as presented. There is no "SET" in the SQL. Also, is there supposed to be an "Else" in there since you set dbs to nothing and then set it back to what is was.

Another is Entity text or numeric? Your value is "0808" which should have single quotes if text and the first 0 is not stored if the field is numeric.

Regarding your problem, consider using Me.Dirty prior to the update statement.


Duane
Hook'D on Access
MS Access MVP

 

[larrydavid]

Thank you Duane, much obliged. Thank you very much sir.