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
seamus99Asked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
You need to requery your form after running an update query that affects the table behind the form:

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)
       Me.requery
End Sub 

Open in new window

0
 
mbizupCommented:
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

0
 
mbizupCommented:
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

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
Gustav BrockCIOCommented:
Right you are Joe.
And then you don't need to requery the form.

/gustav
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.