[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

optimizing code as procedure to large

Posted on 2012-09-09
15
Medium Priority
?
529 Views
Last Modified: 2012-09-13
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
0
Comment
Question by:Kongta
  • 7
  • 7
15 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 38381113
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
 

Author Comment

by:Kongta
ID: 38381252
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
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38384862
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Kongta
ID: 38386263
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38386450
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
 

Author Comment

by:Kongta
ID: 38387516
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38389228
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
 

Author Comment

by:Kongta
ID: 38395936
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38397193
You really should open another question to avoid confusing answers since your latest question unrelated to your original question about optimizing your code..
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38397262
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
 

Author Comment

by:Kongta
ID: 38397582
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38397683
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
 

Author Comment

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

In the inital code
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38397723
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
 

Author Comment

by:Kongta
ID: 38397771
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question