VBA code

[Valient]

Hi
Please help me to correct the vba code below:
I have 4 field: Date1,Date2,Date3,Type and a 1 text box called "Total" and below is the vba code which is not working as per the target output:

Private Sub Total_AfterUpdate()
If Me.Type = A Then Me.Total = Me.Date3 - Me.Date2
Else
Me.Total = Me.Date3 - Me.Date1
End If
End Sub

The target to the vba code is that:
- when the value of field "type" is A, the text box "Total" should compute field "Date3-Date2"
- when the value of field "type" is B, the text box "Total" should compute field "Date3-Date1"

Having hard time how to add the value B in the equation.

 

[NauticalGent]

What kind of error/output are you getting?

 

[Valient]

Im not getting error. But the formula is not working

 

[NauticalGent]

I noticed that your AfterUpdate is on the wrong control as well. How/when is the Type field populated as well as the Date fields?

 

[Valient]

Thanks Nautica

Date1 to Date 3 to be filled , then the type field...

 

[bob fitz]

If Me.Type = A

What is A
A variable or the text value of the control called Type

 

[Gasman]

Pay attention to post 4

 

[Minty]

Be aware that Type is a reserved word in Access.
I would change it to something more descriptive CarType or BombType etc.

 

[Valient]

The value A or B, is a value that I manually put in the field "type"

 

[Minty]

So as per @NauticalGent you have put your code in the after update event of the total.
It should be in the after update event of the [type] field surely?

Also, this looks like it could always be calculated, so in theory, you shouldn't store it?

 

[Valient]

So as per @NauticalGent you have put your code in the after update event of the total.
It should be in the after update event of the [type] field surely?

Also, this looks like it could always be calculated, so in theory, you shouldn't store it?

Taking guys advise, I change the type filename to TypeBrand and change the AfterUpdateLocation
Private Sub TypeBrand_AfterUpdate()

If Me.TypeBrand = A Then Me.Total = Me.Date3 - Me.Date2
Else
Me.Total = Me.Date3 - Me.Date1
End If
End Sub

And below is the error code:

 

[Isaac]

Use the normal layout:

If Something Then
    Somethingelse
Else
    Somethingelse
End if

It's not typical to use the one-liner, although some people enjoy using it, and you can do that if the whole statement is on one line and you might need a colon too, I can't remember b/c I never use it that way

 

[Valient]

Hi,

Thanks for several tips.

It works as below.

Private Sub TypeBrand_AfterUpdate()
If Me.TypeBrand = A Then
Me.Total = Me.Date3 - Me.Date2
Else
Me.Total = Me.Date3 - Me.Date1
End If
End Sub

For now the code is OK as there are only two brand which is A and B.
In the future if there is additional brand let say C, then I need to adjust it again which I don't know how to code it.

 

[NauticalGent]

Hi,

Thanks for several tips.

It works as below.


For now the code is OK as there are only two brand which is A and B.
In the future if there is additional brand let say C, then I need to adjust it again which I don't know how to code it.

Have a look at the Select Case feature.

Edit: You should also take Minty's advice about not storing a calculated value in a table.

 

[Isaac]

Yeah, what @NauticalGent said, and also (just so you're aware), there's always this:

if something then
   some result
elseif something then
   some result
elseif ...then........infinitely
   some result
end if

 

[missinglinq]

Paying close attention to everything else you've been told...if you're entering an A or B into the Type control:

If Me.TypeBrand = A

then it has to be Text and the code needs to be

If Me.TypeBrand = "A"

Linq ;0)>

 

[Valient]

Thanks Guys.. Good day to all

 

[missinglinq]

So...did that resolve the problem?

Linq ;0)>

 

[Valient]

So...did that resolve the problem?

Linq ;0)>

Yes, it works this way..Thanks

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

Resolved!