By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,087 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Calculated table field looping with VBA on multi-level structure (Access 2007)

P: 6
Hi all,
I'm new of VBA and I'm trying to include some code to an existing DB. I,ve a table with a simple field (Stato semplice) with a numeric value. Records listed define a structure with the field Lev. - levels from 1 to "n". Another field (STD) gives the information about the type of record ("std" or empty). I want 1)to define if a row has no lower levels - so an additional field P/N Semplice is filled with "s" by a first Loop; 2)a second loop calculate an additional field (Stato Composto) with a simple algorithm - "Stato Composto" is equal to "Stato Semplice" for "s" rows and equal to 0,4*"Stato Semplice"+0,5(sum of lower details/number of details)+0,1(sum of the STD rows/number of STD rows) for the others
I have an Excel macro (attached) that do the complete job in the Excel worksheet, but I want to convert to some code that can be driven by an Access macro.
Thanks for any help
Attached Files
File Type: zip TestMacro.zip (313.2 KB, 90 views)
Dec 5 '11 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,494
I've tried to rewrite this a bit to help understanding, but it still doesn't make a lot of sense. Certainly not anything I can work to. See if it makes sense to you and tell me where it's wrong.

Marmo:
Hi all,
I'm new to VBA and I'm trying to add some code to an existing DB. I have a table with a simple field (Stato semplice) with a numeric value. Records listed define a structure with the field Lev. - levels from 1 to "n". Another field (STD) gives the information about the type of record ("std" or empty).

I want :
  1. To define if a row has no lower levels - so an additional field P/N Semplice is filled with "s" by a first Loop.
  2. A second loop calculate an additional field (Stato Composto) with a simple algorithm :
    [Stato Composto] is equal to [Stato Semplice] for "s" rows and equal to 0.4 * [Stato Semplice] + 0.5 * (sum of lower details / number of details) + 0.1 * (sum of the STD rows / number of STD rows) for the others.

I have an Excel macro (attached) that do the complete job in the Excel worksheet, but I want to convert to some code that can be driven by an Access macro.
Thanks for any help

The layout of the table is as follows :
TableName = [???]
Expand|Select|Wrap|Line Numbers
  1. Field Name      Type
  2. Stato Semplice  Numeric
  3. Lev.            Numeric
  4. STD             String
  5. P/N Semplice    String
  6. Stato Composto  Numeric
Also, I notice that all records are set to 's' in [P/N Semplice], yet the next step tests this - implying some of these are reset somehow. There's nothing in your explanation that explains that.

Also, you include an attachment in a format that many won't even be able to open. Is the attachment in VBA? If so then just post it in [ CODE ] tags. If not then many will have to rely on your explanation.
Dec 5 '11 #2

P: 6
Hi NeoPa,
the zip file contains the excel file with macro (xlsm) that perform the action in a worksheet. It works with no problem using the excel rows and columns. The problem is to migrate this approach to a VBA Access module inside a DB. The layout of the table is the one that You show. I have a jerarchical structure of items (Lev. 1 is the higher level, than Lev. 2 etc.)each having 3 basic information ("Level", "Stato Semplice" and "STD"): a first loop should define wether or not the item ia a lower level (if it's a detail - of course resetting this field); a second loop assign a value depending on the values of "P/N Semplice" and "STD".
I hope not to have added entropy ... (it's a little complex). I report the Excel macro and maybe is more clear
Expand|Select|Wrap|Line Numbers
  1. Public numrighe, lvmax, lv, lrif, col, rigapadre, finecicloint, liv, livsucc As Variant
  2. Public i, j, k, w, n, m As Integer
  3. Public p, stato, sd, std As Double
  4.  
  5. Sub KPI()
  6. Call partisemplici
  7. lvmax = 1
  8. i = 2
  9. Do While i <= numrighe
  10.     If Cells(i, 1).Value > lvmax Then
  11.     lvmax = Cells(i, 1).Value
  12.     End If
  13. i = i + 1
  14. Loop
  15. For lv = lvmax To 2 Step -1
  16. lrif = lv - 1
  17. i = 2
  18. Do While i <= numrighe
  19.     If Cells(i, 1).Value = lrif Then
  20.         If Cells(i + 1, 1).Value = lv Then
  21.         rigapadre = i
  22.         j = rigapadre + 1
  23.         Do While j <= numrighe And Cells(j, 1).Value > lrif
  24.         j = j + 1
  25.         Loop
  26.         fineciclointerno = j - 1
  27.         stato = 0   'stato composto
  28.         sd = 0      'sommatoria dettagli
  29.         std = 0     'sommatoria std
  30.         m = 0       'numero dettagli
  31.         n = 0       'numero std
  32.         For k = rigapadre + 1 To fineciclointerno
  33.          If Cells(k, 1).Value = lv Then
  34.             If Cells(k, 4) = "s" Then 'ciclo che sceglie lo stato da conteggiare,semplice se  un dettaglio,composto se  un assieme
  35.             col = 3
  36.             Else
  37.             col = 2
  38.             End If
  39.             If Cells(k, 5) = "std" Then
  40.             n = n + 1
  41.             std = std + Cells(k, col).Value
  42.             Else
  43.             m = m + 1
  44.             sd = sd + Cells(k, col).Value
  45.             End If
  46.         End If
  47.         Next k
  48.         If n = 0 Then   'evita la divisione per 0
  49.         n = 1
  50.         ElseIf m = 0 Then
  51.         m = 1
  52.         End If
  53.         stato = 0.4 * Cells(rigapadre, 3).Value + 0.5 * (sd / m) + 0.1 * (std / n)
  54.         Cells(rigapadre, 2).Value = stato
  55.         End If
  56.         End If
  57. i = i + 1
  58. Loop
  59. Next lv
  60. End Sub
  61.  
  62. Sub partisemplici()
  63. 'assegna una s alle parti semplici
  64. Set rigtab = Range("A1").CurrentRegion
  65. numrighe = rigtab.Rows.Count
  66. w = 2
  67. Do While w <= numrighe
  68.     Cells(w, 4).Value = ""
  69.     w = w + 1
  70. Loop
  71. w = 2
  72. Do While w <= numrighe
  73.     liv = Cells(w, 1).Value
  74.     livsucc = Cells(w + 1, 1).Value
  75.     Cells(w, 1).Select
  76.     If livsucc <= liv Then
  77.         Cells(w, 4).Value = "s"
  78.     End If
  79.     w = w + 1
  80. Loop
  81. End Sub
Dec 5 '11 #3

NeoPa
Expert Mod 15k+
P: 31,494
It is certainly clearer with the code visible Marmo, but not so easy to understand what is going on. Normally I'd insist you explain it in words, but I think you're doing a good job getting as far as you have with that so I'll have to see what I can do with the code. Do something for me though if you would. Please post the whole of the Excel file that you have as I don't have the data to work with. If there are very many lines then you can delete some if necessary, but I need to see which order the data appears in for Excel code (The layout is fine for Access, but Excel doesn't use names for its columns). If you can save it in XLS format instead of XLSX then that would be better, but I think I can probably read XLSX with 2003 (I can for DOCX so I hope I can for XLSX too).

Another point :

Expand|Select|Wrap|Line Numbers
  1. Public i, j, k, w, n, m As Integer
This declares i, j, k, w & n as Variant type variables and only m as Integer. I suspect you intended the following :
Expand|Select|Wrap|Line Numbers
  1. Public i As Integer, j As Integer, k As Integer
  2. Public w As Integer, n As Integer, m As Integer
The same is also true for line #3 :
Expand|Select|Wrap|Line Numbers
  1. Public p, stato, sd, std As Double
Only std is a Double type variable.
Dec 5 '11 #4

P: 6
Updated Attachment with xls file @marmo68
Attached Files
File Type: zip TestMacro3.zip (155.4 KB, 60 views)
Dec 6 '11 #5

P: 6
Hi NeoPa,
also the declaration for the Double type ha sto be extended to all the group variables. For the integer I confirm Your clarification.
I transalted in english the previous version of macro in the xls. If You run should work (it works on my PC)
Thanks for Your support
Attached Files
File Type: zip TestMacroEng.zip (152.4 KB, 67 views)
Dec 6 '11 #6

NeoPa
Expert Mod 15k+
P: 31,494
The file attached has no Option Explicit set (See When Posting (VBA or SQL) Code) nor have the declaration lines been fixed to ensure the variables are specified correctly. I'm disappointed you left this extra work for me to do. It also makes looking into it take longer so you may need to be patient. Even then I cannot promise anything as the code is not written tidily or logically and trying to work out what it does is not straightforward (Even if it were written fully in English it wouldn't be).

Anyway, I'll see what I can come up with.
Dec 8 '11 #7

NeoPa
Expert Mod 15k+
P: 31,494
The first and most obvious point is that the procedure partisemplici() cannot work in Access without the data changing. Access does not recognise positional order for records (which this procedure relies on to set the values in column D - Detail).

I looked through the rest of it, but I'm afraid I found the code so messy it was a struggle even to read it. Reverse-engineering such code is fairly pointless as the mind that created this code doesn't seem to have much of an understanding of what they were doing anyway. There is no obvious structure to it. I don't doubt it manages to execute the required process, but I expect the same job could be done by someone who fully understands the process in half the lines of code (or fewer) and that code could be used to recognise the process. I wasn't able to make much progress with this, but whatever it's doing, is specific to a spreadsheet rather than a database as (as indicated earlier) it relies on relative positioning which, while possible in Access, is not database work at all.

I'm sorry I couldn't take this any further for you. If you need more help then I'm afraid you'll need to make the question make clear sense before posting. i understand that's difficult when you're not working in English, but this isn't a question we can determine the meaning of just by looking at the code (at least not without more work and time involved than I'm prepared to contribute)
Dec 8 '11 #8

P: 6
Hi NeoPa,
as I told You I'm new in VBA (and this includes Excel Macros), so the code was not "clean" as made by a professional. Thanks anyway for the effort and for the moment I'll continue using the first Excel step before the Access publishing on SharePoint. If I make some improvement in the Access code I'll let You Know
Dec 12 '11 #9

NeoPa
Expert Mod 15k+
P: 31,494
Please understand that I do appreciate the situation you're in. I am merely trying to explain why I can go no further at this stage.

Furthermore, the most important part was in paragraph #1. This is not something that can work easily in Access, as Access doesn't deal naturally with relative positions (as Excel can).
Dec 12 '11 #10

P: 6
I'll try to convert in something draft but "edible" for Access and open a new question after Christmas. I agree that code must be completely revised. Take care and Marry Christams
Dec 13 '11 #11

Post your reply

Sign in to post your reply or Sign up for a free account.