Repeated query to VBA

Timoo · Oct 31, 2012

Hi

I'm totally new to VBA.
And I think I will be needing it a lot, the next upcoming half a year.
I know that I'm the kind of person only learning from experience.
Therefore I turn myself to you, experts.

So, first problem I walked into and I would like to solve through VBA:
A query I repeat 12 times, once for every month: I've got a table (CalcFirstYear) with 12 columns, named 1 to 12. And I've got a table with a lot of info per month per client (tbl_Retail_CM_2004)

Now I want to update info in the CalcFirstYear table with info from the specific/related tbl_Retail_CM_2004.Month column.

This is the query

Code:

UPDATE CalcFirstYear INNER JOIN tbl_Retail_CM_2004 
ON CalcFirstYear.[Cust No] = tbl_xx_Retail_CM_2004.[Dealer] 
SET CalcFirstYear.1 = [tbl_Retail_CM_2004]![Total Retails]
WHERE (((tbl_Retail_CM_2004.Month)=1));

Does anyone have any suggestion of how to resolve this in VBA?

With kind regards and thanks up front for any help,
Timoo

boblarson · Oct 31, 2012

I hope this table is just for reporting purposes.

But, that being said, you could use:

Code:

Function AddReportData()
Dim i As Integer
Dim strSQL As String
 
For i = 1 To 12
  strSQL = "UPDATE CalcFirstYear INNER JOIN tbl_Retail_CM_2004 
ON CalcFirstYear.[Cust No] = tbl_xx_Retail_CM_2004.[Dealer] 
SET CalcFirstYear.[B][COLOR=red]" & i & "[/COLOR][/B]= [tbl_Retail_CM_2004]![Total Retails]
WHERE (((tbl_Retail_CM_2004.Month)=[B][COLOR=red]" & i & "[/COLOR][/B]));   
 
CurrentDb.Execute strSQL, dbFailOnError
 
Next
 
End Function

And if you put that function into a standard module you can call when necessary. And, you can do more with it too.

Timoo · Oct 31, 2012

Hi Boblarson,

Thank you for your reply.
To be honest, it is not for reporting.
It is part of a series of queries, applied to data.

As soon as I've got a chance, I will apply this.
See what happens..

How is your Halloween?
& Thanx!

Timoo

boblarson · Oct 31, 2012

Timoo said:
Hi Boblarson,

Thank you for your reply.
To be honest, it is not for reporting.
It is part of a series of queries, applied to data.

Then I would suggest a more proper and normalized design would be a good path to follow instead of down this path which only perpetuates bad design and that will be a pain to get meaningful data out where in a proper design it will be a breeze.

Timoo · Oct 31, 2012

boblarson said:
Then I would suggest a more proper and normalized design would be a good path to follow instead of down this path which only perpetuates bad design and that will be a pain to get meaningful data out where in a proper design it will be a breeze.

Hi BoB Larson,

Ok, I follow.
And I have to start somewhere, don't I?
How do I set up a proper design?
And is VBA needed for 'proper design'?
Because then people like you will have to help me getting kickstarted, because I do not like theoretics. AT all...

Thanks for a reply,
Timoo

John Big Booty · Nov 1, 2012

The basis of good DB design is Data Normalisation, this tutorial might also help you get your head around the concept

and the data models here should also give you a good starting point.

jdraw · Nov 1, 2012

In addition to the suggestions by Bob and John, I recommend you read this article on Relational Design. It's relatively short and condenses a lot of the theoretics into a few meaningful paragraphs.

http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

Good luck with your project.

Timoo · Nov 5, 2012

Guys, thank you for helping me out in this.
:fun:, I feel the sense of learning again :-D

@Bob Larson: actually it is for reporting purposes, indeed.
Monthly. For now I do not get the VB module to work, though.

I get a syntax error, when I run it from a macro.
What do I do wrong?

Code I implemented:

Function AddReportData()
Dim i As Integer
Dim strSQL As String

For i = 1 To 12
strSQL = "UPDATE CalcFirstYear INNER JOIN tbl_Retail_CM_2004 ON CalcFirstYear.[Cust No] = tbl_Retail_CM_2004.[Dealer] SET CalcFirstYear." & i & "= [tbl_Retail_CM_2004]![Total Retails] WHERE (((tbl_Retail_CM_2004.Month)=" & i & "));"
CurrentDb.Execute strSQL, dbFailOnError
Next
End Function

boblarson · Nov 5, 2012

I believe since it is a number you would need to use brackets:

SET CalcFirstYear.[" & i & "]= [tbl_Retail_CM_2004]![Total Retails] WHERE

Timoo · Nov 5, 2012

thanx!
Works. [shows my complete ignorance/incompetence towards VBA coding already] :banghead: And I am learning; this is interesting.

Step next; since it's a "runcode" in a macro, I can pass a year onto it, don't I?
So, how to replace "2004" by 'the year filled in in the macro'?

Original:

Function AddReportData()
Dim i As Integer
Dim strSQL As String

For i = 1 To 12
strSQL = "UPDATE CalcFirstYear INNER JOIN tbl_Retail_CM_2004 ON CalcFirstYear.[Cust No] = tbl_Retail_CM_2004.[Dealer] SET CalcFirstYear.[" & i & "]= [tbl_Retail_CM_2004]![Total Retails] WHERE (((tbl_Retail_CM_2004.Month)=" & i & "));"
CurrentDb.Execute strSQL, dbFailOnError
Next
End Function

New version:

Function AddReportData()
Dim i As Integer
Dim strSQL As String

For i = 1 To 12
strSQL = "UPDATE CalcFirstYear INNER JOIN tbl_Retail_CM_" & y & " ON CalcFirstYear.[Cust No] = tbl_Retail_CM_" & y & ".[Dealer] SET CalcFirstYear.[" & i & "]= [tbl_Retail_CM_" & y & "]![Total Retails] WHERE (((tbl_Retail_CM_" & y & ".Month)=" & i & "));"
CurrentDb.Execute strSQL, dbFailOnError
Next
End Function

If my first year (CalcFirstYear table) is 2004, I want it to regenerate 2004 data, if it is 2005, I want it to regenerate based upon 2005 and so on.

I thought something like this:
RunCode = AddReportData(2004)

but how do I tap this information into the VBA script?
And if that is possible, how do I retrieve this year from a Access Form?

thanx guys, you're giving me some basic lessons into the VBA world (never too late to learn, is it?)

boblarson · Nov 5, 2012

The change would be:

Code:

Function AddReportData(iYear As Integer)
Dim i As Integer
Dim strSQL As String


For i = 1 To 12
strSQL = "UPDATE CalcFirstYear INNER JOIN tbl_Retail_CM[B][COLOR=red]_" & iYear & " O[/COLOR][/B]N CalcFirstYear.[Cust No] = tbl_Retail_CM_[B][COLOR=red]" & iYear & "[/COLOR][/B].[Dealer] SET CalcFirstYear[B][COLOR=blue].[" & i & "[/COLOR][/B]= [tbl_Retail_CM_[B][COLOR=red]" & iYear & "[/COLOR][/B]![Total Retails] WHERE (((tbl_Retail_CM_[B][COLOR=red]" & iYear & "[/COLOR][/B].Month)=" & i & "));"
CurrentDb.Execute strSQL, dbFailOnError
Next
End Function

And then you would call it from your macro
AddReportData(2010)

Timoo · Nov 5, 2012

cool, sounds it is not that difficult, after all :-D
Thanx!!

Repeated query to VBA

Timoo

Registered User.

boblarson

Smeghead

Timoo

Registered User.

boblarson

Smeghead

Timoo

Registered User.

John Big Booty

AWF VIP

jdraw

Super Moderator

Timoo

Registered User.

boblarson

Smeghead

Timoo

Registered User.

boblarson

Smeghead

Timoo

Registered User.

Similar threads

Users who are viewing this thread