Update Checkbox 2

[dmkennard2]

Hi,
I have a subform that has customer payment details. ie:
Amount Owed, Amount Paid, Amount Outstanding.
The subform updates the amount outstanding automatically when a payment is made.
I have a checkbox that marks the record paid in full but this is manual, is it possible to have the checkbox change automatically?

The form is using a select statement within the form.

Thanks in advance.
Dazz

 

[kjv1611]

This checkbox, I am assuming, is linked to a YES/NO field in a table of yours. Either way, the answer is yes.

How you want to go about that is up to you.

You can run an Update Query that will update the records for those with a current balance of <=0 or whatever best fits your situation.

So, you could have a query with the SQL sort of like this:

UPDATE tblYourTableName SET tblYourTableName .YesNoField = "Yes"
WHERE (((tblYourTableName .YourBalanceField)<=0))

So, you could set up your Query in access that way, and have it run in a certain event, or just put the SQL code in your VBA module to run within a certain event.

So, let's assume you make a query. You could do something like this:

Private Sub cmdUpdatePaidAccts_Click()
  DoCmd.SetWarnings False
  DoCmd.RunQuery "qryYourUpdateQueryName"
  DoCmd.SetWarnings True
End Sub

That's at least the simplest/shortest way, I think.

If you wanted to just put it in your VBA code, then it'd be something like this:

Private Sub cmdUpdatePaidAccts_Click()
  Dim strSQL As String
  strSQL = "UPDATE tblYourTableName " & _
           "SET tblYourTableName .YesNoField = 'Yes'" & _
           "WHERE (((tblYourTableName .YourBalanceField)<=0))"

  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True
End Sub

Something like that.

Post back to let us know if that makes sense, and if you need any more assistance getting that setup, or if you run into any errors, etc.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dmkennard2]

Excellent thanks for these i will give them a go.
Is it possible to update the checkbox after i have entered a value in the Amount Paid field?

Dazz

 

[dmkennard2]

I have just thought. The Amount Outstanding field is not in a table but is a textbox with a calculation on the form, so its not as easy as i thought.

Dazz

 

[kjv1611]

It's not as difficult as you might think. If your text box will hold the value, then you could do it with an Update Query or a recordset update. The query may still be the fastest.

Something like this:

Private Sub txtAmountPaid_AfterUpdate()
  Dim strSQL As String
  If txtAmountOutstanding <=0 Then
    strSQL = "UPDATE tblYourTableName " & _
             "SET tblYourTableName .YesNoField = 'Yes'" & _
             "WHERE (((tblYourTableName .ItemID) = '" &txtItemID & "'"))"
  End If
End Sub

The context may need a little editing, as I've not tested it.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dmkennard2]

Hi,
Thanks for this. I have inserted my table names in the code below but its still showing red and saying there are to many brackets.
I dont understand the ItemID.

I have put the code in the Amount Paid textbox so it will run the code after its updated, is this correct?

Sorry to be a pain, but its good to learn.

Private Sub Acc_Amt_Paid_AfterUpdate()
Dim strSQL As String
  If txtOutstanding <= 0 Then
    strSQL = "UPDATE tblAcc_Orders" & _
             "SET tblAcc_Orders.Acc_Paid = 'Yes'" & _
             "WHERE (((tblAcc_Orders.ItemID) = '" &txtItemID & "'"))"
  End If
End Sub

Dazz

 

[kjv1611]

What do you mean by this?

I have put the code in the Amount Paid textbox so it will run the code after its updated, is this correct?

Are you saying that you put the code actually in your text box, or that you put it in the "After Update" event of your text box (in the VBA window)?

--

"If to err is human, then I must be some kind of human!" -Me

 

[dmkennard2]

I clicked on AfterUpdate Property of the textbox and chose Code Builder.

Dazz

 

[kjv1611]

Ok..

I did see 2 typos in that code that may explain at least part of the error:

Private Sub Acc_Amt_Paid_AfterUpdate()
Dim strSQL As String
  If txtOutstanding <= 0 Then
    strSQL = "UPDATE tblAcc_Orders" & _
             "SET tblAcc_Orders.Acc_Paid = 'Yes'" & _
             "WHERE (((tblAcc_Orders.ItemID) = '" [highlight]&txtItemID[/highlight] & [highlight]"'"))"[/highlight]
  End If
End Sub

should be...

Private Sub Acc_Amt_Paid_AfterUpdate()
Dim strSQL As String
  If txtOutstanding <= 0 Then
    strSQL = "UPDATE tblAcc_Orders" & _
             "SET tblAcc_Orders.Acc_Paid = 'Yes'" & _
             "WHERE (((tblAcc_Orders.ItemID) = '" [highlight]& txtItemID[/highlight] & [highlight]"'))"[/highlight]
  End If
End Sub

--

"If to err is human, then I must be some kind of human!" -Me

 

[dmkennard2]

Hi,
I think we are nearly there. This is the code i have:

Private Sub Acc_Amt_Paid_AfterUpdate()
Dim strSQL As String
  If txtOutstanding <= 0 Then
    strSQL = "UPDATE tblAcc_Orders " & _
             "SET tblAcc_Orders.Acc_Paid = 'Yes' " & _
             "WHERE (((tblAcc_Orders.Acc_Amt_Paid) = '" & txtTotal & "'))"
   
  Else: End
    
  End If

DoCmd.RunSQL strSQL

End Sub

When i enter an Amount Paid that is the same as txtTotal i get an error saying "Data yype mismatch in crieria expression"

Dazz

 

[kjv1611]

Ok, it's a simple fix:

Change This:

Private Sub Acc_Amt_Paid_AfterUpdate()
Dim strSQL As String
  If txtOutstanding <= 0 Then
    strSQL = "UPDATE tblAcc_Orders " & _
             "SET tblAcc_Orders.Acc_Paid = 'Yes' " & _
             "WHERE (((tblAcc_Orders.Acc_Amt_Paid) [highlight]= '" & txtTotal & "'))"[/highlight]
   
  Else: End
    
  End If

DoCmd.RunSQL strSQL

End Sub

To this:

Private Sub Acc_Amt_Paid_AfterUpdate()
Dim strSQL As String
  If txtOutstanding <= 0 Then
    strSQL = "UPDATE tblAcc_Orders " & _
             "SET tblAcc_Orders.Acc_Paid = 'Yes' " & _
             "WHERE (((tblAcc_Orders.Acc_Amt_Paid) [highlight]= " & txtTotal & "))"[/highlight]
   
  Else: End
    
  End If

DoCmd.RunSQL strSQL

End Sub

The reason is that the single quotes (') in the code is telling Access that it is looking to compare to a string/text value. But if the value is numeric, it will throw this error at you. By taking out the single quotes, it's looking for a numeric value. I should've thought about that in my original example.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dmkennard2]

Ok thats working But....
It only updates after i click out of the record and then it comes up with a warning saying that the record has been changed by another user, "Discard, Copy to Clipboard, Drop" is i click Copy to Clipboard the tickbox updates, any other options then nothing is changed.
I am the only user using the DB.
I have put the SetWarnings code back in.

Dazz

 

[dmkennard2]

Edit to above -
I have also noticed that i have to enter the amount twice for it to register??

Dazz

 

[lars7]

Could sothing like this help:

Private Sub Text1_AfterUpdate()

If (Me.Text1) <= 0 Then
Me.Check1 = 0
Else
Me.Check1 = -1
End If

End Sub

 

[kjv1611]

Hmm, is the Acc_Amt_Paid_AfterUpdate control the last control on the form in your "tab order", where after that is updated, you go to the next record, and the current record is saved?

--

"If to err is human, then I must be some kind of human!" -Me

 

[dmkennard2]

Private Sub Text1_AfterUpdate()

If (Me.Text1) <= 0 Then
    Me.Check1 = 0
Else
    Me.Check1 = -1
End If

End Sub

If i use this then the 3 text boxes i have with calculations come up with "?Name

 

[dmkennard2]

Hmm, is the Acc_Amt_Paid_AfterUpdate control the last control on the form in your "tab order", where after that is updated, you go to the next record, and the current record is saved?

It wasn't but it is now and its doing the same.

Dazz

 

[kjv1611]

dmkennard2,

First, make sure that you are using your actual field names, and not those in the example. The ones in the example were just generic names, and not the ones you supplied earlier.

If you are using the correct names, then I'd stick with the "Yes" and "No" notation.

Then we can get back to your other issue.

What I'm thinking you could do in order to fix that problem is in using a different event than "After Update"... maybe "On Key Press" or "Before Update"

Hopefully, we won't have to go this route, though.

--

"If to err is human, then I must be some kind of human!" -Me

 

[lars7]

Where did you put it?

It is ment to go in the after update of the text box you are entering the value into and change the names to suit your text box and your check box.

 

[dmkennard2]

Hi,
Thanks for this its excellent.
Here is my code as its stands and i have made sure that all the references are my own.

Private Sub Acc_Amt_Paid_AfterUpdate()
Dim strSQL As String
  If txtOutstanding <= 0 Then
    strSQL = "UPDATE tblAcc_Orders " & _
             "SET tblAcc_Orders.Acc_Paid = '-1' " & _
             "WHERE (((tblAcc_Orders.Acc_Amt_Paid) = " & txtTotal & "))"
   
  Else
  
    If txtOutstanding > 0 Then
    strSQL = "UPDATE tblAcc_Orders " & _
             "SET tblAcc_Orders.Acc_Paid = '0' " & _
             "WHERE (((tblAcc_Orders.Acc_Amt_Paid) <> " & txtTotal & "))"

  End If
    End If
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub

Dazz