Repeated query to VBA (1 Viewer)

Timoo

Registered User.
Local time
Today, 17:12
Joined
May 9, 2012
Messages
30
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

Smeghead
Local time
Today, 09:12
Joined
Jan 12, 2001
Messages
32,059
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

Registered User.
Local time
Today, 17:12
Joined
May 9, 2012
Messages
30
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

Smeghead
Local time
Today, 09:12
Joined
Jan 12, 2001
Messages
32,059
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

Registered User.
Local time
Today, 17:12
Joined
May 9, 2012
Messages
30
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
 

Timoo

Registered User.
Local time
Today, 17:12
Joined
May 9, 2012
Messages
30
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


 
Last edited:

boblarson

Smeghead
Local time
Today, 09:12
Joined
Jan 12, 2001
Messages
32,059
I believe since it is a number you would need to use brackets:

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

Timoo

Registered User.
Local time
Today, 17:12
Joined
May 9, 2012
Messages
30
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

Smeghead
Local time
Today, 09:12
Joined
Jan 12, 2001
Messages
32,059
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

Registered User.
Local time
Today, 17:12
Joined
May 9, 2012
Messages
30
cool, sounds it is not that difficult, after all :-D
Thanx!!
 

Users who are viewing this thread

Top Bottom