How can I change the value of a combo box based on value of an unbound text box?

seanlhall
seanlhall used Ask the Experts™
on
I have a form that creates invoices. What I need to do is have the invoice staus change depending on the value of the total due text box. The combo box (status) has the following set values, Outstanding, Paid, Partial. The (totaldue) text box is an unbound text box the calculates the tax - payments.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
How does this textbox calculate the payment? If it's done through code, then you'd do something in the AfterUpdate event of the control (see below, perhaps).

How do you determine the difference between "Outstanding" and Partial"?

Sub YourTextbox_AfterUpdate
  If Me.YourTextbox > 0 Then
    Me.YourCombo = "Outstanding"
  ElseIf Me.YourTextbox = 0 Then
    Me.YourCombo = "Paid"
  End If
End Sub
You did not mention when you want the status combo box to change and how it should change so I shall assume you want to change it after you change the totaldue text box and if totaldue is zero, status should be Paid otherwise status should be Partial. If this is the case, in the totaldue textbox after update event place:

Private Sub totaldue_AfterUpdate ()
If totaldue then
   status = "Paid"
Else
   status = "Partial"
End if
End Sub

The after update event only fires if the user makes a change to the control.

Author

Commented:
Partial is where the text box (discount1) has a value >0, but total due still has a value >0. Example if the invoice is 1000.00 and they only make a payment of 700.00 leaving 300.00 due.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

If discount1 > 0 and totaldue > 0 Then
   status = "Partial"
End If

Author

Commented:
I put the code in the After Update event. The status does not change, when the total due is is > 0 or < 0. The totaldue is a unbound text box that calculates, could that be a problem?

Author

Commented:
Ok I place the code on a command button and it works, so it must be the event After Update. Any ideas?
As I stated, The after update event only fires if the user makes a change to the control. It unio not fire if it is a calculated control or if you change it in VBA. So the question is, when do you want the status combo box to show the new status? What event or events triggers that?

Author

Commented:
Is see. There is a sub form called invdetail. It should fire after I  make any changes to the sub form. How would I do that?
You can put the code in the sub form's after update event. That event will fire when changes are made to the sub form and the changes are saved to the underlying table by moving to a new record, closing the form, the pressing shift Enter (save record), clicking on save record or any other method that saves the record. If you want the status to change immediately after changing a control in the sub form, you would need to call the code from the after update event of each control.

Author

Commented:
I am having trouble refering to the main form. My subform is called invdetail and the main form is called frminvoicetable subform. The frminvoicetable subform is located on an unbound subform object called objSubform. I use this to help the form load quicker becuase it has 5 or 6 subforms. For some reason I have never been able to refer to subform loaded on the unbound subform object. I have included the code on the unbound object. The object is on a tab control and loads teh correct subform on the chage event of the tab control. All the subforms are part of the parent form frmsubjects.
Private Sub TabCtl300_Change()

   Dim strSubformName As String
   Select Case Me.TabCtl300
      Case 1: strSubformName = ""
      Case 2: strSubformName = "frmupdatetable subform"
      Case 3: strSubformName = "frmletters"
      Case 4: strSubformName = "frminvoicetable subform"
      Case 5: strSubformName = "frmlog"
      Case 6: strSubformName = "frmtimeline"
      Case 7: strSubformName = "frmprocess"
      Case 8: strSubformName = "frmadditionalsubject"
      Case Else: strSubformName = ""
   End Select
   If strSubformName = "" Then
      Me.Form.objSubform.visible = False
   Else
      Me.objSubform.SourceObject = strSubformName
      Me.objSubform.LinkMasterFields = "subjectid"
      Me.objSubform.LinkChildFields = "subjectid"
      Me.objSubform.visible = True
     
   End If
End Sub

Open in new window

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
From a subform, you can refer to the "parent" form by doing this:

Me.parent.SomeControl

From the Parent, you can refer to your subform like this:

Me.objSubform.Form.SomeControl

I'm not clear which way you're going, so I cannot really comment further.

Author

Commented:
Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial