VBA code 2 (1 Viewer)

[Valient]

Hi,
I have created the below code (similar code to my previous post but in different purpose/event).
The intention of the code is to have an automated value on the field "TotalValue" once the value is added to the field "Date3"

The sequence of adding value to the field is as below:

Add value to fields "Date1", "Date2", "TypeBrand" (likeA, B C..), then waited for sometimes to add the value for field "Date3"

When the value for the field "Date3" is available, Im expecting a generated value for the field "TotalValue" based on the below formula.

Private Sub TotalValue_Change()
If Me.TypeBrand = "A" Then
Me.TotalValue = Me.Date3 - Me.Date2
ElseIf Me.TypeBrand = "B" Then
Me.TotalValue = Me.Date3 - Me.Date1
Else
Me.Total = Null
End If
End Sub

I choose the event "change' but it did not work.
Can someone please help me to find the correct event and vba code?

 

[arnelgp]

move your code to textbox TypeBrand.
you have event on TotalValue and yet you are changing it inside its
own code?

 

[Valient]

move your code to textbox TypeBrand.
you have event on TotalValue and yet you are changing it inside its
own code?

Btw I'm still new in the coding environment.. thanks

I moved it to textbox TypeBrand under event change but it does not help

 

[Minty]

Try the after update event.
It may be worth you reading up on which events fire when, and their purpose - https://www.simply-access.com/Form-Events.html

 

[arnelgp]

sorry, you create a Public Function inside the form:

Public Function fnTotal()
    Me.TotalValue = Null
    If Me.TypeBrand = "A" Then
        If IsNull(Me.Date2) = False And IsNull(Me.Date3) = False Then
            Me.TotalValue = DateDiff("d", Me.Date2, Me.Date3)
        End If
    ElseIf Me.TypeBrand = "B" Then
        If IsNull(Me.Date1) = False And IsNull(Me.Date3) = False Then
            Me.TotalValue = DateDiff("d", Me.Date1, Me.Date3)
        End If
    End If
    Me.Dirty = False
End Function

now, on textbox TybeBrand, Date1, Date2, Date3
add this on Property sheet->Event->After Update:

=fnTotal()


also add code to the Form's Current Event:

private sub form_current
Call fnTotal()
end sub

 

[Valient]

Try the after update event.
It may be worth you reading up on which events fire when, and their purpose - https://www.simply-access.com/Form-Events.html

currently this link is blocked by the connection I'm in right now, but will check the link.

By putting TypeBrand under "after update" event (which I already tried before), the TotalValue will have a generated value if I will add the value by the sequest..Date1, Date2,Date3 and last is the TypeBrand.

But the sequence I need to is as mentioned in my first post is...Date1, Date2,TypeBrand and the last is Date3.

 

[arnelgp]

see form1 in design view click on Typebrand, date1, date2, date3 and see
the code on its After Update (on Property Sheet->Event).

see also th form's current event.

 

[Valient]

asee form1 in design view click on Typebrand, date1, date2, date3 and see
the code on its After Update (on Property Sheet->Event).

see also th form's current event.

Wow, .Thank you very much for your help..solved

 

[Valient]

Wow, .Thank you very much for your help..solved

Hi arnelgp,

my apology for opening this post again because I need to add more code based on the sample database you provided but cant make it work.
May I request your help again to add the below on the codes.

Attaching back the sample database with additional field "Date4".
The idea for adding it is when the value of "typeBrand" is A, the "Date4" should have an automatic value of Date1+8. And when the value of "typeBrand" is B, the "Date4" should be empty which means I have to add the value manually.

still with the same sequence.."Date1", "Date2", "TypeBrand", "Date3"

 

[arnelgp]

check and test

 

[Valient]

Hi arnelgp,

my apology for opening this post again because I need to add more code based on the sample database you provided but cant make it work.
May I request your help again to add the below on the codes.

Attaching back the sample database with additional field "Date4".
The idea for adding it is when the value of "typeBrand" is A, the "Date4" should have an automatic value of Date1+8. And when the value of "typeBrand" is B, the "Date4" should be empty which means I have to add the value manually.

still with the same sequence.."Date1", "Date2", "TypeBrand", "Date3"

check and test

Hi,

By sequence:

1. After adding value to Date1 this error pop up

2. When I click OK, the pop up dis appear and I can add value to Date2.
3. When I move to typeBrand or to any field the error pop up again then when click OK it disappear.
4. Add value to typeBrand = A, the Date4 has automatic value (when =B, Date 4 is empty) which is OK
5. Now when adding value to Date3, the field "TotalValue" is empty. (before it was working as per the first code design)

 

[arnelgp]

ok, check and test again.

 

[Valient]

ok, check and test again.

hi arnelgp, it works well.
im just finalizing something on my database then get back to you, thanks

 

[Valient]

Thank you arnelgp.