Help for VBA Beginner - updating tables by code

[DazedandConfused]

Hi, I have a fair knowledge of access but only "code" so far via total plagiarisation, and can't find an example to show me how to do what is probably a simple task using VBA, and I don't really understand the process yet (self-taught). At the moment I'm at the stage of writing 'OnClick' event procedures which basically run a lot of DoCmd.runSql queries to update my database but I need to get a bit more serious in order to do more complex tasks.

I have a table "a" (Example.xls attached) which I want to update regularly using details from another table (table "b") which is frequently updated. What I want to do is put the count of Table B - Location = "Gcc" AND status = "complete", into the cell Line 1, Column "Gcc" in Table "A". Once I can work out how to do that, I dare say it will be easy enough to work out how to put in all the other variables. Can anyone help??

Much obliged!

[HansV]

It's not clear to me how the rows in Tbl A (except for the "Total # Completed" row) relate to the data in Tbl B.
But you can provide a count without any programming by using a crosstab query. See the attached sample database.

[attachment=86574:Crosstab.zip]

[DazedandConfused]

Hi thanks for your reply - yes I've already done the crosstabs, however, as you note, this particular table takes data from multiple sources (Table B as I mention relates to Line 1) - basically each line of the table uses a different table or query, which is counted in crosstabs according to location and status. However, the difficulty arises when the crosstab does not return a value of "0" for locations not included in that data run - in the example this would be cr bops and fin, and this in turn affects reports I have written which will return an error if there is a null value in a field. I can't see a way round updating information in one table based on counts etc held in another crosstab query either without writing serveral queries basically to feed each cell individually. That is why I am looking to try and do things in code. Does that make sense??

I think if I can find a way in code to update a single cell, say Line 1 Gcc using code, then I can work out how to do the rest - I've heard of loops and arrays and think this may be the way, but don't have a scooby how to start.... so any help you give would be most appreciated.

[HansV]

The code module in the attached version shows a way to do this. The code requires that you have a reference to the Microsoft DAO 3.6 Object Library in Tools | References... in the Visual Basic Editor.

[attachment=86575:Crosstab.zip]

[DazedandConfused]

Thanks v much Hans, will give it a shot.

[DazedandConfused]

The code module in the attached version shows a way to do this. The code requires that you have a reference to the Microsoft DAO 3.6 Object Library in Tools | References... in the Visual Basic Editor.

[attachment=86575:Crosstab.zip]

Hi thanks for this - have changed it for slightly different table and field names, and have checked its referenced, however am trying to call it as part of an "on click" event - probably doing it incorrectly. I renamed the module "Mod1", and am trying DoCmd.OpenModule "Mod1", "AddData" - its not working - what am I doing wrong?? I do have a couple of books on VBA but am finding it hard to tie together all the "bits"....

[HansV]

It's simpler than that. The name of the procedure to be executed is AddTest. You can simply use a line

AddTest

or

Call AddTest

in your On Click event procedure. For example:

Private Sub Command1_Click()
  Call AddTest
End Sub

[DazedandConfused]

Thats fab - works great!

[HansV]

Did you want to ask an additional question?

[DazedandConfused]

Hi Hans - I have now used your code to fill in about 10 similar tables - still not very elegant as I'm sure I should be able to do everything from one module, but I've essentially created a module for every line - still a lot quicker than writing the queries, so thanks for that - its going to make a huge difference to how quickly I can provide my statistics, so its been really helpful. I am understanding your code, however, what I'm now trying to do for all the tables is provide a line of percentages - basically what I need to code for the same table I provided earlier is Line 5 = the value in Line 4/the value in Line 2 * 100. I understand what to do but not how to ask for it in the module format. If you could help me with that, basically it will also help me understand how to multiply, add etc between the lines in all these tables using a subprocedure. Can you help again??

[DazedandConfused]

Did you want to ask an additional question?

never mind VBA - I can't work out how to post - boxes keep appearing and disappearing and I can't see where the submit button is when they open so seem to have hit the wrong thing a couple of times. Sorry about that!

[HansV]

You don't have to create a new module each time - you can include several procedures (subs) in a single module. And if you have several tables with the same layout, you only need to add a single line to AddTest for each table. The line

AddData "tblData", 1

in AddTest tells VBA to process the table tblData and to add the info to the line in tblCount with LineID=1.

But I think the approach you're trying to follow is not ideal. You're trying to use Access as if it were Excel - perhaps that would be a better tool for your purpose. The current setup in Access will be very difficult to maintain if categories are added, removed or changed.

[DazedandConfused]

You don't have to create a new module each time - you can include several procedures (subs) in a single module. And if you have several tables with the same layout, you only need to add a single line to AddTest for each table. The line

AddData "tblData", 1

in AddTest tells VBA to process the table tblData and to add the info to the line in tblCount with LineID=1.

But I think the approach you're trying to follow is not ideal. You're trying to use Access as if it were Excel - perhaps that would be a better tool for your purpose. The current setup in Access will be very difficult to maintain if categories are added, removed or changed.

I can understand totally why you think that, however in practice what I am actually attempting to do is consolidate and update data from dozens of extremely large and complex spreadsheets which are coming down in a flat format from another source, and which are difficult to get around. The database I've done is working pretty well, and this new bit is to avoid having to import data from multiple excel sources, manipulate it, spit out multiple tables, then manipulate it again in Excel - too fiddly. I really need to use Access simply because of the number of lines we're looking at and consolidating in some of these reports, but understand if you feel you can't help.


Cheers and thanks anyway.

[HansV]

If you prefer Access, I propose a slightly different approach, using multiple queries.

See the attached version. The examples are ridiculous - I merely copied and pasted the table, and modified a few values at random. But it shows how you can combine Totals queries to perform calculations, and how to ensure that there are no blanks.

The result will be a mirrored and rotated version of the table you envisioned, but it is much easier to maintain.

[attachment=86577:Crosstab.zip]

[DazedandConfused]

If you prefer Access, I propose a slightly different approach, using multiple queries.

See the attached version. The examples are ridiculous - I merely copied and pasted the table, and modified a few values at random. But it shows how you can combine Totals queries to perform calculations, and how to ensure that there are no blanks.

The result will be a mirrored and rotated version of the table you envisioned, but it is much easier to maintain.

[attachment=86577:Crosstab.zip]

Ok thanks for your help - much obliged.