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
KongtaAsked:
Who is Participating?
 
IrogSintaCommented:
Here you go.  I put your code inside a loop.  What I don't understand though is why you position each country by code but you position their IQs manually.  Is there an advantage in positioning the countries by code?

I read your post in the other thread of your intent to add animation.  This could be done in Access but you cannot do it in a report's Print Preview.  You would have to do it in either a form or a report's Report View using the Timer event.  

The problem with using the Report View in a report is that you would need to use the OnPaint event but you cannot position each control by code just like you can in the OnFormat event.
WorldMap.accdb
0
 
NorieVBA ExpertCommented:
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?
0
 
KongtaAuthor Commented:
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

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27722763.html
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
KongtaAuthor Commented:
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
0
 
IrogSintaCommented:
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.
0
 
KongtaAuthor Commented:
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?
0
 
IrogSintaCommented:
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))
0
 
KongtaAuthor Commented:
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
0
 
IrogSintaCommented:
You really should open another question to avoid confusing answers since your latest question unrelated to your original question about optimizing your code..
0
 
IrogSintaCommented:
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?
0
 
KongtaAuthor Commented:
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.
0
 
IrogSintaCommented:
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% ----------   ????????
0
 
KongtaAuthor Commented:
100% should be 'zehn' as well, this I fixed with
ElseIf Me.BZ = 1 Then
strval = StrsBZ(10)

In the inital code
0
 
IrogSintaCommented:
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

0
 
KongtaAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.