asked on

optimizing code as procedure to large

I do produce a world map in a report where each country is specialy coloured depending on "IQ". Having done 2/3 of all, I am now not able to run more as said that the procedure is to big. Does anybody see a potential to optimize? Otherwise I have to execute part in SQL, but I first would like to know if my code can be shorted, easier way to me.

'Brasilien-----------------------------------------------------------------------------------------------------------

            
With Me.Brasilien
        .Top = 4025
        .Left = 3752
End With
DoEvents

strSQL = "SELECT IQ " & _
             "FROM dbo_tblStatistikTmp_sb_Land WHERE ((Land)='BZ') "

If DCount("*", "dbo_tblStatistikTmp_sb_Land", "Land='BZ'") = 0 Then
             With .Item("BZ")
                  .Value = 99
             End With
Else
             With .Item("BZ")
                  .Value = CurrentProject.Connection.Execute(strSQL)(0)
             End With
End If

             

Dim StrsBZ
StrsBZ = Array("Neutral", "Eins", "Zwei", "Drei", "Vier", "Fuenf", "Sechs", "Sieben", "Acht", "Neun", "Zehn")
If Me.BZ = 99 Then
strval = StrsBZ(0)
ElseIf Me.BZ = 1 Then
strval = StrsBZ(10)
Else
strval = StrsBZ(Int(Me.BZ * 10) + 0.99)
End If

strSQL = "SELECT " & strval & _
             " FROM tblLaenderPic WHERE ((Land)='Brasilien') "

             With .Item("Brasilien")
                .Value = CurrentProject.Connection.Execute(strSQL)(0)
             End With
'Bulgarien-----------------------------------------------------------------------------------------------------------

            
With Me.Bulgarien
        .Top = 2453
        .Left = 7417
End With
DoEvents

strSQL = "SELECT IQ " & _
             "FROM dbo_tblStatistikTmp_sb_Land WHERE ((Land)='BU') "

If DCount("*", "dbo_tblStatistikTmp_sb_Land", "Land='BU'") = 0 Then
             With .Item("BU")
                  .Value = 99
             End With
Else
             With .Item("BU")
                  .Value = CurrentProject.Connection.Execute(strSQL)(0)
             End With
End If

             

Dim StrsBU
StrsBU = Array("Neutral", "Eins", "Zwei", "Drei", "Vier", "Fuenf", "Sechs", "Sieben", "Acht", "Neun", "Zehn")
If Me.BU = 99 Then
strval = StrsBU(0)
ElseIf Me.BU = 1 Then
strval = StrsBU(10)
Else
strval = StrsBU(Int(Me.BU * 10) + 0.99)
End If

strSQL = "SELECT " & strval & _
             " FROM tblLaenderPic WHERE ((Land)='Bulgarien') "

             With .Item("Bulgarien")
                .Value = CurrentProject.Connection.Execute(strSQL)(0)
             End With

Open in new window

thx
rgds
Kongta

Norie

Do you have this same 40 lines of code repeated, with minor changes, for each country in the world?

If you do then you should look into using loops, arrays etc.

Also, what data do you have stored for each country?

Kongta

ASKER

Yes, I posted two countries and have this for the whole world. I have a lot of data stored on my SQL back end but only need IQ on each and based on this I choose the land-pic in different colours.

Here is a former Q related to the same with a db attached

https://www.experts-exchange.com/questions/27722763/ReportView-PrintView.html

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kongta

ASKER

wow, perfect, much shorter, have many thx. I don't use code to position IQ because they only have to be in the country pic as such, while the country pics need to be fine tuned in position so I get a proper world map. Later I'll make the IQ invisible as I need 'em for the pic-choose. right now, visible, it helps me to check if the colour of the country pic is correct.

Animating would be fun, but I think I will show the User a small map with data 3 month/1mth and 1 week ago and therefore he can imagine how the big picture is changing.

rgds
Kongta

IrogSinta

You could also fIne tune the positions by holding down the CTRL key while using the arrow keys. Holding down SHIFT, in the other hand, fine tunes the sizes.

Kongta

ASKER

Sorry to come back, just noted that you missed the part that if no entry is in the table, that it picks the 'Neutral' pic as well if I have an IQ of 1, I have to ignore my calc '* 10) + 0.99)'

If Me.BZ = 99 Then
strval = StrsBZ(0)
ElseIf Me.BZ = 1 Then
strval = StrsBZ(10)

can this be included or do I have to go for a procedure on SQL?

IrogSinta

I just noticed that your use of the INT formula to get the array item number is off a bit.
Change the following line in the loop and you should be okay with 0's and 1's.
strVal = StrsAR(Int(rst!IQ * 10 + 0.99))

Kongta

ASKER

May I bother you with one more issue. I have now the problem that if I have 'IQ' 0, it should take the country pic 'Eins', deep red but now it take 'Neutral', grey. It should take 'Neutral / grey' when IQ is NULL. Do you see a solution on this? Let me know when I shall open a new Q, is okay for me.

Rgds
Kongta

IrogSinta

You really should open another question to avoid confusing answers since your latest question unrelated to your original question about optimizing your code..

IrogSinta

Okay, I'm pondering your last question. Can you clear something up? The way I understand it is that you want any IQ above 0 but less than .1 to be Neutral; then any IQ from .1 to less than .2 to be Eins, right? Finally you want an IQ of 0 to be Eins as well?

Kongta

ASKER

The basic is that IQ shows the relation of how many stocks are in positive territory. If there are no stocks at all like Irak, it shows NULL as no calculation can be made and therefore the pic should be 'Neutral' (grey). If from 100 stocks none is positive, I get 0 which means I stay in the range of 0-10% and should get 'Eins' (deep red), same pic when 5 of hundred are positive = IQ 0.05. Pic 'Zwei' is for the range 10%-20% and so on to the level where all are positive and I get 1. This is why I made the calc *10 + 0.99 to get in the correct catagory but have to place the 99 to come to grey.

I'm stunned by your code as it's so compact relative to mine, fabulous. But I assume this issue now is hard to fix.

IrogSinta

It's not difficult at all but the problem is you don't have enough colors. What color for 100%?

Null ----------- Neutral
0% - 9.99% Eins
10% - 19.99% Zwei
20% - 29.99% Drei
30% - 39.99% Vier
40% - 49.99% Fuenf
50% - 59.99% Sechs
60% - 69.99% Sieben
70% - 79.99% Acht
80% - 89.99% Neun
90% - 99.99% Zehn
100% ---------- ????????

Kongta

ASKER

100% should be 'zehn' as well, this I fixed with
ElseIf Me.BZ = 1 Then
strval = StrsBZ(10)

In the inital code

IrogSinta

Just change the 1 line of code in your loop to this;

strVal = StrsAR(IIf(rst!IQ = 1, 10, Int(Nz(rst!IQ, -0.1) * 10) + 1))

Open in new window

Kongta

ASKER

Wow, it all seems so easy when you code it. I write tons of lines! I think I could improve my program to warp-drive with your knowledge. Anyway, have many thanks, appreciate very much you gave me this inputs. have a nice weekend. rgds. Kongta