asked on

Inserting into Record from check box on form

When I run this module from a checkbox on a form. Two things happen that confuse me.

1. The date that is being put into [Complete Date] on the form is 12/30/1899
2. When I move to the next record a message appears stating that "the record has been edited by another user since I starting editting it'???

Private Sub Check54_Click()
Dim strSql As String

Dim strupdateSQL As String
strupdateSQL = "UPDATE tblClaims set [Complete]= -1 WHERE (((tblClaims.InvoiceID)=" & Me.InvoiceID & "));"
strupdateSQL = "UPDATE tblClaims set [Complete Date]= Date() WHERE (((tblClaims.InvoiceID)=" & Me.InvoiceID & "));"
CurrentDb.Execute (strupdateSQL)
End Sub

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

Also, your code is currently only running one of the updates. Revise your update statements to do both simultaneously:

Private Sub Check54_Click()
Dim strSql As String

Dim strupdateSQL As String
            strupdateSQL = "UPDATE tblClaims set [Complete Date]= Date(),  [Complete]= -1 WHERE tblClaims.InvoiceID =" & Me.InvoiceID 
        CurrentDb.Execute (strupdateSQL), dbFailOnError
        Me.requery
End Sub

Open in new window

mbizup

Correction:

Private Sub Check54_Click()
Dim strSql As String

Dim strupdateSQL As String
            strupdateSQL = "UPDATE tblClaims set [Complete Date]= Date(),  [Complete]= -1 WHERE tblClaims.InvoiceID =" & Me.InvoiceID 
        CurrentDb.Execute strupdateSQL, dbFailOnError
        Me.requery
End Sub

Open in new window

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

seamus99
Why don't you just bind (as in a bound form) Complete check field and the Complete date directly to the controls on the form, and then you could skip all of this code and the related issues ...?

And if for some reason you want to immediately update/save this information when the Check54 is set to true, you can do this:

Private Sub Check54_AfterUpdate()
If Me.Check54 = True Me.Dirty = False ' save record
End Sub

mx

Gustav Brock

Right you are Joe.
And then you don't need to requery the form.

/gustav

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

I guess I left out setting Complete Date: (and left of Then)

Private Sub Check54_AfterUpdate()
If Me.Check54 = True Then
Me![Complete Date]= Date()
Me.Dirty = False ' save record
End If
End Sub

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

And even with the other way, I think you only would need a Me.Refresh, although Requery does the job - a subtle difference to some extent

mx