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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.