Help running VB Code

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

I need to know how to get a piece of code to run.

What i want to do is:-

from a raw data set pull out the top 5 items by company for each
company/entity/p&L combination, (there are 30 combinations) append these into
a table so that I can extract a table.

I can set up append queries but I dont want 30 append queries for each set
of items that I want to extract (there are 5 different data sets I need).

So, I came to the conclusion VB is the answer. I have some basic code that I
think will work converted from the SQL that the append query is written in. I
need to know where to set up the code (i.e. should it be a module or a class
module) and how to get it to run so that I can test if the code is correct
before writing much more.

I've tried pressing the play key in the VB viewer and I get a box that pops
up asking me to select a macro. If I type in a macro name and click create it
says "invalid procedure name"

Public Sub RecordSetFromSQL()

On Error GoTo cmdOK_Click_err

Dim strSql As String

DoCmd.SetWarnings False

Is the start of the code......

Can anyone help me and/or tell me what I'm doing wrong?
 
My current code is:

Sub PDI()
Dim strSql As String
DoCmd.SetWarnings False

'TOTALTOTALTOTAL
strSql = "INSERT INTO [Past Due In Table] ( PL, [P & L], CustName, [Value] )
" & vbCrLf & _
"SELECT TOP 5 ""TOTALTOTALTOTAL"" AS PL, " & vbCrLf & _
"[Past Due In Detail].[P&L], " & vbCrLf & _
" [Past Due In Detail].CustName, " & vbCrLf & _
"[Past Due In Detail].Value " & vbCrLf & _
"FROM [Past Due In Detail] " & vbCrLf & _
"GROUP BY ""TOTALTOTALTOTAL"", [Past Due In Detail].[P&L], [Past Due In
Detail].CustName, [Past Due In Detail].Value " & vbCrLf & _
"ORDER BY [Past Due In Detail].Value DESC;"

end sub

But it doesnt seem to do anything, it doesnt error and it feels as though
its just not running. There is definately data in the table that its looking
up to to pull in. I'm just baffled as to what to do next or what I'm doing
wrong.
 
Back
Top