Continuous Form + VBA

[isapple]

Hello,

I was just wondering why my vba code wouldn't work.
I have a scorecard form, and what I wanted to do on a form was that if a user enters a number in a bound box (to a table), it would automatically update an unbound box. So I coded it as this.

Private Sub SupplierCorrectiveActionRequests_Enter()

If [Request] = 0 Then [Text30] = 100
Elseif [Request] = 1 Then [Text30] = 75
Elseif [Request] = 2 Then [Text30] = 50
Elseif [Request] = 3 Then [Text30] = 25
Elseif [Request] >= 4 Then [Text30] = 0

End Sub

But it throws an error that there cannot be Then if there are no ifs, weird.

Another problem I had is that I'm using a continuous form, and whenever I scroll down there seems to be a visual bug, any way to fix it? I want to take out the alternating pattern, is there any way to do that without changing the theme?

 

[CJ_London]

you need an end if after the last elseif, and your first line needs to be modified

If [Request] = 0 Then 
    [Text30] = 100
elseif....

Alternatively you can use a case statement

Select Case [Request]
    Case 0
        [Text30] = 100
    Case 1
        [Text30] = 75
    Case 2 
        [Text30] = 50
    Case 3 
        [Text30] = 25
    Case 4 
        [Text30] = 0
end Select

Please note the use of code tags, it makes you post much easier to read (it preserves indents for example), so please use in the future

 

[isapple]

Thanks, that helped a lot.
A problem I ran into is that since it is a continuous form, there are more instances of Text30 within the form. Is there any way for me to limit the textbox from being changed to the particular data?

For example,

Request was 1 for RatingID 1, hence Text30 should be 75.
and then
Request was 0 for RatingID 2, and Text30 should be showing 100, but it shows 75 because I entered 1 for the Request of RatingID 1.

Would you rather have a screenshot?

 

[CJ_London]

Another problem I had is that I'm using a continuous form, and whenever I scroll down there seems to be a visual bug, any way to fix it? I want to take out the alternating pattern, is there any way to do that without changing the theme?

In the form detail section set the alternate back colour to the same as the backcolour

If text30 is in your continuous form then it will be the same for each row. What you need to do is put you calculation in the form recordsource in whioch case you would use the choose function

text30:choose([Request]+1,100,75,50,25,0)

the reason for adding 1 to request is that choose starts from 1 and must be consecutive.

see this link about the choose function

http://office.microsoft.com/en-gb/access-help/choose-function-HA001228797.aspx

 

[isapple]

Wow that worked wonderfully.
Thank you very much.

Now the last mission is... to convert the 100 75 50 25 0 to 100% 75% 50% 25% etc. if I do the [Text30] & "%", an error pops up. If I change it on the recordsource query, another error shows up. How can I make it so that it shows the percentage sign?

 

[isapple]

Nvm I figured it out I used the CDbl function in a hidden textbox