Access Avançado
Alta Interação com Consultas de
Referências Cruzadas
Paulo Sergio Sarraino *
Artigo para Access 97, 2000, XP, 2003 e Excel 97, 2000, XP, 2003
Pré-requisitos: Conhecimentos de Access e VBA
QUANDO TRABALHEI NUM BANCO PRIVADO HÁ ALGUNS
ANOS, A EQUIPE DE CONTROLE FINANCEIRO DE TI TINHA A
NECESSIDADE DE GERENCIAR O CONSUMO DE RECURSOS
DE TI PELAS VÁRIAS ÁREAS DA DIRETORIA, COMO CUSTOS
DE IMPRESSÃO E ALOCAÇÃO DE SERVIDORES. EIS ALGUMAS CARACTERÍSTICAS DO PROJETO QUE LEVANTEI:
- consultas com múltiplos critérios definidos on-line;
- acompanhamento evolutivo durante períodos definidos on-line;
- extração desses dados para relatórios e planilhas;
- tempo de desenvolvimento rápido e eficiente;
- ambiente utilizado: Access 97.
Como no projeto da equipe de TI há variáveis específicas desse
ambiente, construí um exemplo mais simples para facilitar e ilustrar esta matéria. Acompanhe.
NOTA: Cabe ressaltar que, a partir da versão XP (2002), o Access
suporta a construção de Tabelas e Gráficos Dinâmicos de forma
muito parecida com o Excel. Mas isso é assunto para outra matéria.
CONSULTA E RELACIONAMENTOS
Precisamos identificar no mínimo 3 campos para a estrutura:
Campo de Linha: identifica o cabeçalho de linha (quantos campos
desejar)
Campo de Coluna: identifica o cabeçalho de coluna (apenas 1 campo)
Campo de Valor: o cálculo da consulta (apenas 1 campo)
Juntando essas informações ao meu conhecimento da época,
concluí que as consultas de referência cruzada ajudariam muito
no desenvolvimento do projeto e, utilizando o
Access, teria ainda diversas opções para o ambiente do aplicativo que essa ferramenta proporciona.
Um dos recursos mais importantes do Access (em
todas as versões) são as consultas de referência
cruzada. A visualização de dados de forma agrupada em 2 dimensões possibilita uma experiência mais rica do usuário com o sistema. Comparativamente, o Excel, com seus recursos de tabela dinâmica, oferece bem mais opções de controle e formatação do que o Access, mas precisávamos ir mais longe, padronizando o tratamento das
informações, otimizando e protegendo o banco
de dados e possibilitando uma série de interfaces
para os usuários. Nesta matéria, a idéia é mostrar
como utilizá-las com grande interação usando parâmetros dinâmicos. Pode-se usar o Access 97 ou superior, porém o
aplicativo disponível para essa matéria está na versão 2000.
Veja a estrutura de uma consulta de referência cruzada:
Figura 1 – Criando uma consulta de referência cruzada
Veja o comando SQL da mesma consulta:
cálculo do valor
TRANSFORM Sum(Nz([dblPreco],0)*Nz([dblQuantidade],0))
AS Total
32
cabeçalhos de
SELECT tbl_Grupo.nomGrupo AS Grupo, 2 AS TipoRegistro,
DISPONÍVEL PARA DOWNLOAD
linha
Space(4) & tbl_Produto.nomProduto AS Produto
PIVOTTABLES.EXE (ACCESS 2000)
relacionamentos FROM tbl_Grupo INNER JOIN (((tbl_Produto INNER JOIN
Disponível para download em www.forumaccess.com.br
entre as tabelas
MAIO/JUNHO 2006 - REVISTA Nº 72
WWW.FORUMACCESS.COM.BR
qry_Periodos ON tbl_Produto.codProduto =
Access Avançado
qry_Periodos.codProduto) LEFT JOIN tbl_Consumo ON
(qry_Periodos.codProduto = tbl_Consumo.codProduto) AND
(qry_Periodos.datPeriodo = tbl_Consumo.datPeriodo)) LEFT
JOIN tbl_Preco ON (qry_Periodos.codProduto =
tbl_Preco.codProduto) AND (qry_Periodos.datPeriodo =
próprios dados. Não tratarei aqui as interfaces para entrada desses dados.
Para otimizar a performance das tabelas, acesse a janela de Relacionamentos e defina as ligações entre as tabelas conforme o
desenho a seguir:
tbl_Preco.datPeriodo)) ON tbl_Grupo.codGrupo =
tbl_Produto.codGrupo
filtro
WHERE tbl_Grupo.codGrupo In (1)
agrupamentos
GROUP BY tbl_Grupo.nomGrupo, 2, Space(4) &
tbl_Produto.nomProduto
classificação
ORDER BY tbl_Grupo.nomGrupo, Space(4) &
tbl_Produto.nomProduto
cabeçalho de
PIVOT Format([qry_Periodos].[datPeriodo],'mm/yyyy') In
coluna
(03/2005);
Para nosso projeto precisaremos das tabelas abaixo:
Figura 2 – Definindo o relacionamento
tbl_Grupo
Campo
Tipo
Descrição
codGrupo
AutoNumeração [chave] Identifica o Grupo de Produtos
nomGrupo
Texto (30)
Nome do Grupo de Produtos
Campo
Tipo
Descrição
codProduto
AutoNumeração [chave] Identifica o Produto
tbl_Produto
nomProduto
Texto (30)
Nome do Produto
codGrupo
Número (Inteiro)
Identifica o Grupo do Produto
Campo
Tipo
Descrição
codProduto
Número (Inteiro) [chave] Identifica o Produto
datPeriodo
Data [chave]
Período do Preço
dblPreco
Número (Duplo)
Preço do Produto
Campo
Tipo
Descrição
codProduto
Número (Inteiro) [chave] Identifica o Produto
datPeriodo
Data [chave]
Período do Consumo
dblQuantidade
Número (Duplo)
Quantidade consumida do
tbl_Preço
SOBRE FORMULÁRIOS E SEUS CONTROLES
Os dados do sistema referem-se a consumo de produtos e preços praticados. Com eles poderemos consultar:
• A quantidade de produtos vendidos pelo tempo
• Os preços praticados para os produtos vendidos pelo tempo
• O volume de vendas dos produtos (Preço x Quantidade) pelo
tempo
• Sub-totais por Grupo de Produtos
• Um gráfico de barras mostrando a relação entre os Produtos
• Um gráfico tipo pizza mostrando a relação de um Produto com
toda a base de dados
Vamos ver a estrutura do formulário principal de consulta aos
dados:
tbl_Consumo
Produto
Também será necessária uma consulta especial: qun_Periodos.
Ela une os períodos registrados nas tabelas de Preços e Consumo para termos todos os itens disponíveis para a pesquisa de
dados. Nesse projeto, esse tipo de consulta previne pesquisas
de consumo sem preço e vice-versa. A ausência desses dados é
tratada com zeros nas consultas finais.
Precisaremos também carregar os dados. Você pode usar os
dados do exemplo disponível para essa matéria ou gerar seus
Figura 3 – Formulário principal
Para cada parametrização das consultas, criamos controles
com tratamentos e validações.
FÓRUMACCESS
MAIO/JUNHO 2006 - REVISTA Nº 72
33
Access
Avançado
Access
Avançado
O grupo de opção grpOrient (identificado pela
legenda “Por...”) permite definir o Valor da nossa pesquisa, pode-se selecionar Preços ou Consumo para uma pesquisa simples na base de dados ou Vendas para realizar o cálculo Preços x
Consumo e assim termos uma evolução de Vendas dos nossos dados.
A caixa de listagem lstGrupo (identificado pela
legenda “Grupos:”) exibe os grupos de produtos cadastrados na base e permite selecioná-los
para filtrar os dados pesquisados, pode-se selecionar múltiplos itens para a pesquisa.
A caixa de listagem lstPeriodo (identificado pela
legenda “Período:”) exibe os períodos cadastrados na base através da consulta
qun_Periodos e permite selecioná-los para os
cabeçalho de coluna da consulta, pode-se selecionar múltiplos itens para a pesquisa. Essa
seleção múltipla das caixas de listagem é tratada pelas caixas de texto ocultas strGrupo e
strPeriodo que, usando a função GetListItems
(controle, delimitador), retorna os itens selecionados preparados para uso, por exemplo, em cláusulas In() de strings
do SQL.Como a quantidade de períodos disponíveis tende
a aumentar e esse critério será “casado” com as caixas de
texto dos subformulários, limitou-se a leitura desses dados na geração das consultas da seguinte forma: para
visualização em tela pode-se selecionar até 49 períodos e
para visualização em relatórios apenas 12 períodos. Porém,
esses limites podem ser redefinidos conforme a necessidade do seu aplicativo.
O botão Pesquisar realiza a validação dos parâmetros e chama a rotina “AlteraRefCz” (detalhada adiante) que está em
cada subformulário da tela. A alteração de qualquer dos controles citados solicita o recálculo dos dados.
Os subformulários interagem também com os botões
disponibilizados no rodapé do formulário.
O controle frm_Evolucaod_sub exibe os dados e subtotais
por Grupo ou um dos gráficos solicitados.
O controle frm_Evolucaot_sub exibe os totais gerais dos
dados (para os gráficos ele fica invisível).
Os subformulários de dados e de totais têm a mesma estrutura, diferenciando apenas a geração dos dados, veja abaixo. Existem 50 campos com suas respectivas legendas
identificadas como “Legenda1...Legenda50” e “Campo1...
Campo50” que serão exibidos ou não conforme o resultado da consulta gerada. Para o layout ser ajustável a essa
estrutura é necessário definir sua propriedade “Modo Padrão” como “folha de dados”. Apesar da disposição dos
campos na estrutura do formulário parecer irrelevante, ela
facilita a montagem da ordem de tabulação dos controles
que define o posicionamento dos campos na visualização
“folha de dados” do subformulário.
34
MAIO/JUNHO 2006 - REVISTA Nº 72
Figura 4 - Formulário que mostra os dados
Dentro do formulário, como dito antes, há uma única função,
“AlteraRefCz”. Ela realiza todo o trabalho de formatação dos
campos, consulta aos dados e totalizações.
Option Compare Database
Option Explicit
Function AlteraRefCz()
On Error GoTo AlteraRefCz_Err
'analisa, define e formata o formulário com os campos da consulta de ref.
cruzada
Dim i
As Integer
'contador para campos
Dim nmCampo As String
'variável para nome de campo
Dim strSQL As String
Dim vlTotal As String
'string SQL
'identifica o valor desejado para a consulta
Dim totPrd As String
'string para acumulado dos períodos
'esconde colunas
For i = 1 To 50
With Me("Campo" & i)
.ControlSource = ""
.ColumnHidden = True
End With
Me("Legenda" & i).Caption = ""
Next
'esconde/exibe formulário
If (Me.Parent!strPeriodo = "") Then
Me.Visible = False
WWW.FORUMACCESS.COM.BR
Access Avançado
"ORDER BY tbl_Grupo.nomGrupo, Space(4) &
Application.Echo True
tbl_Produto.nomProduto " & _
Exit Function
"PIVOT
Else
Me.Visible = True
Format([qun_Periodos].[datPeriodo],'mm/yyyy') In (" &
Me.Parent!strPeriodo & ");"
End If
With CurrentDb.QueryDefs("qrc_Evolucao_dados")
Me.RecordSource = ""
.SQL = strSQL
.Close
Select Case Me.Parent!grpOrient
End With
Case 1: vlTotal = "Nz([dblPreco],0)"
Case 2: vlTotal = "Nz([dblQuantidade],0)"
Me.RecordSource = "qun_Evolucao"
Case 3: vlTotal = "Nz([dblPreco],0)*Nz([dblQuantidade],0)"
End Select
'atribui todos os campos da consulta ao formulário (campo e legenda)
vlTotal = "Sum(" & vlTotal & ")"
For i = 1 To Me.RecordsetClone.Fields.Count
With Me("Campo" & i)
nmCampo = Me.RecordsetClone.Fields(i - 1).Name
strSQL = "TRANSFORM " & IIf(Me.Parent!grpOrient = 3, vlTotal, 0) & " AS
.ColumnHidden = False
Total " & _
"SELECT
.ColumnOrder = i
tbl_Grupo.nomGrupo AS Grupo, 1 AS TipoRegistro,
Select Case i
tbl_Grupo.nomGrupo AS Produto " & _
"FROM
Case 1, 2
tbl_Grupo INNER JOIN (((tbl_Produto INNER JOIN
.ColumnHidden = True
qun_Periodos ON tbl_Produto.codProduto = qun_Periodos.codProduto) LEFT
.ControlSource = nmCampo
JOIN tbl_Consumo ON (qun_Periodos.datPeriodo = tbl_Consumo.datPeriodo)
Case 3
AND (qun_Periodos.codProduto = tbl_Consumo.codProduto)) LEFT JOIN
tbl_Preco ON (qun_Periodos.datPeriodo = tbl_Preco.datPeriodo) AND
.TextAlign = 1 'esquerda
(qun_Periodos.codProduto = tbl_Preco.codProduto)) ON tbl_Grupo.codGrupo
.ColumnWidth = 2000
.ControlSource = nmCampo
= tbl_Produto.codGrupo " & _
"WHERE
Case Is >= 4
tbl_Grupo.codGrupo In (" & Me.Parent!strGrupo & ") " & _
"GROUP BY tbl_Grupo.nomGrupo, 1, tbl_Grupo.nomGrupo " & _
.TextAlign = 3 'direita
"ORDER BY tbl_Grupo.nomGrupo, tbl_Grupo.nomGrupo " & _
.ColumnWidth = 1500
"PIVOT
.ControlSource = "=IIf((Campo1=Campo3) And (Parent!grpOrient
Format([qun_Periodos].[datPeriodo],'mm/yyyy') In (" &
Me.Parent!strPeriodo & ");"
<> 3), Null, Format([" & nmCampo & "],'Standard'))"
totPrd = totPrd & "CDbl(Nz([" & nmCampo & "],0))+"
End Select
With CurrentDb.QueryDefs("qrc_Evolucao_grupos")
.FormatConditions.Delete
.SQL = strSQL
.FormatConditions.Add acExpression, , "Campo1=Campo3"
.Close
.FormatConditions(0).FontBold = True
End With
End With
strSQL = "TRANSFORM " & vlTotal & " AS Total " & _
"SELECT
tbl_Grupo.nomGrupo AS Grupo, 2 AS TipoRegistro,
Me("Legenda" & i).Caption = nmCampo
Next
Space(4) & tbl_Produto.nomProduto AS Produto " & _
"FROM
tbl_Grupo INNER JOIN (((tbl_Produto INNER JOIN
qun_Periodos ON tbl_Produto.codProduto = qun_Periodos.codProduto) LEFT
'monta coluna de acumulado de produto
totPrd = Mid(totPrd, 1, Len(totPrd) - 1)
JOIN tbl_Consumo ON (qun_Periodos.datPeriodo = tbl_Consumo.datPeriodo)
AND (qun_Periodos.codProduto = tbl_Consumo.codProduto)) LEFT JOIN
tbl_Preco ON (qun_Periodos.datPeriodo = tbl_Preco.datPeriodo) AND
(qun_Periodos.codProduto = tbl_Preco.codProduto)) ON tbl_Grupo.codGrupo
= tbl_Produto.codGrupo " & _
"WHERE
tbl_Grupo.codGrupo In (" & Me.Parent!strGrupo & ") " & _
"GROUP BY tbl_Grupo.nomGrupo, 2, Space(4) &
tbl_Produto.nomProduto " & _
With Me("Campo" & i)
.ControlSource = "=IIf((Campo1=Campo3) And (Parent!grpOrient <> 3),
Null, Format(" & totPrd & ",'Standard'))"
.ColumnHidden = False
.ColumnOrder = i + 50
.ColumnWidth = 1500
.TextAlign = 3 'direita
FÓRUMACCESS
MAIO/JUNHO 2006 - REVISTA Nº 72
35
Access
Avançado
Access
Avançado
.FormatConditions.Delete
.SetFocus
.FormatConditions.Add acExpression, , "Campo1=Campo3"
.FormatConditions(0).FontBold = True
DoCmd.RunCommand acCmdUnfreezeAllColumns
End With
End With
If Me.grpOrient > 2 Then
Me("Legenda" & i).Caption = "Acumulado"
With Me.frm_Evolucaot_sub
Exit Function
.SetFocus
DoCmd.RunCommand acCmdUnfreezeAllColumns
AlteraRefCz_Err:
End With
End If
Application.Echo True
MsgBox "Ocorreu um erro:" & vbCrLf & _
End If
Err.Number & " - " & Err.Description, vbCritical, "Atenção!"
End Sub
End Function
Sub CongelaColuna(qtcol As Byte)
No rodapé temos também o botão “Visualizar Relatórios”, que
chama o relatório de dados ou de gráfico conforme o item
visualizado no formulário e o botão “Congelar Colunas” que
realiza a ação de congelar a primeira coluna visualizada na pesquisa de dados a fim de facilitar a rolagem horizontal da tela.
Private Sub cmdCongelar_AfterUpdate()
'congela a quantidade de colunas informada para _
facilitar rolagem horizontal por vários períodos
On Error Resume Next
Select Case qtcol
Case Is <= 0:
Dim ncol As Integer
'quantidade de colunas a congelar
Exit Sub
Case 1:
ncol = 1
DoCmd.RunCommand acCmdUnfreezeAllColumns
SendKeys "{TAB}", True
If Me.frm_Evolucaod_sub.SourceObject <> "frm_Evolucao_dados" Then
Me.cmdCongelar = 0
SendKeys "{HOME}", True
SendKeys "^{ }", True
Exit Sub
DoCmd.RunCommand acCmdFreezeColumn
End If
SendKeys "{RIGHT}", True
Case Else
If Me.cmdCongelar = True Then
DoCmd.RunCommand acCmdUnfreezeAllColumns
SendKeys "{TAB}", True
With Me.frm_Evolucaod_sub
.SetFocus
SendKeys "{HOME}", True
SendKeys "^{ }", True
CongelaColuna (ncol)
SendKeys "+{RIGHT}", True
End With
DoCmd.RunCommand acCmdFreezeColumn
With Me.frm_Evolucaot_sub
If Me.grpOrient <= 2 Then
SendKeys "{RIGHT 2}", True
End Select
.Visible = False
Else
End Sub
.SetFocus
CongelaColuna (ncol)
End If
End With
Else
With Me.frm_Evolucaod_sub
36
MAIO/JUNHO 2006 - REVISTA Nº 72
SOBRE RELATÓRIOS E SEUS CONTROLES
A estrutura do relatório segue o mesmo princípio dos formulários de dados. Ele é todo desvinculado, e no evento
Open ocorre toda a carga e formatação de campos. Perceba
que também aplicamos os recursos de Formatação Condicional para ressalvar as linhas com os dados de Grupos de
Produtos.
WWW.FORUMACCESS.COM.BR
Access Avançado
nmCampo = .Fields(i + 2).Name
Me("txtMes" & i).Caption = nmCampo
With Me("valMes" & i)
.ControlSource = "=IIf((Grupo=Produto) And
(Forms!frm_Evolucao!grpOrient <> 3), Null, Format([" & nmCampo &
"],'Standard'))"
.FormatConditions.Delete
.FormatConditions.Add acExpression, , "Grupo=Produto"
.FormatConditions(0).FontBold = True
End With
totPrd = totPrd & "CDbl(Nz([" & nmCampo & "],0))+"
Next
End With
'monta Acumulado
Figura 5 – Construindo o relatório
Private Sub Report_Open(Cancel As Integer)
totPrd = Mid(totPrd, 1, Len(totPrd) - 1)
Me.txtAcum.Caption = "Acumulado"
With Me.valAcum
Dim rstD As Recordset, rstT As Recordset
Dim i
As Integer
Dim lim
'contador
As Integer
'limite de períodos pra exibição
Dim nmCampo As String
Dim totPrd As String
'variável para nome de campo
'string para acumulado do produto
.ControlSource = "=IIf((Grupo=Produto) And
(Forms!frm_Evolucao!grpOrient <> 3), Null, Format(" & totPrd & ",'Standard'))"
.FormatConditions.Delete
.FormatConditions.Add acExpression, , "Grupo=Produto"
.FormatConditions(0).FontBold = True
End With
If Forms!frm_Evolucao!strPeriodo = "" Then
Cancel = True
'monta colunas de totais
Exit Sub
If Forms!frm_Evolucao!grpOrient = 3 Then
End If
totPrd = ""
With rstT
Me.RecordSource =
Forms!frm_Evolucao!frm_Evolucaod_sub.Form.RecordSource
Set rstD = Forms!frm_Evolucao!frm_Evolucaod_sub.Form.RecordsetClone
lim = .Fields.Count - 3
If lim > 12 Then lim = 12
For i = 1 To lim
nmCampo = .Fields(i + 2).Value
If Forms!frm_Evolucao!grpOrient = 3 Then
Me.ReportFooter.Visible = True
Me("totMes" & i).ControlSource = "=Format(" & nmCampo &
",'Standard')"
Set rstT = Forms!frm_Evolucao!frm_Evolucaot_sub.Form.RecordsetClone
Else
Me.ReportFooter.Visible = False
totPrd = totPrd & "CDbl(Nz(" & nmCampo & ",0))+"
Next
End With
End If
'monta Acumulado
With Me.Produto
.FormatConditions.Delete
.FormatConditions.Add acExpression, , "Grupo=Produto"
totPrd = Mid(totPrd, 1, Len(totPrd) - 1)
Me.totAcum.ControlSource = "=Format(" & totPrd & ",'Standard')"
End If
.FormatConditions(0).FontBold = True
End With
'monta colunas de dados
With rstD
lim = .Fields.Count - 3
If lim > 12 Then lim = 12
For i = 1 To lim
End Sub
Se algum dos gráficos estiver sendo visualizado, o sistema chamará o relatório do gráfico correspondente, porém, para eles, a
definição dos dados para o gráfico não pode ser feita da mesma
forma (via evento Open), por isso a rotina do botão “Visualizar
Relatórios” do formulário tem mais ações além do OpenReport
que imaginamos. Veja em seguida:
FÓRUMACCESS
MAIO/JUNHO 2006 - REVISTA Nº 72
37
Access
Avançado
Access
Avançado
Private Sub cmdVisualizar_Click()
.Close acReport, "rpt_Evolucao_grafPizza"
.SetWarnings True
Dim i
As Integer
'contador para campos
Dim matPeriodos As Variant
Dim strPeriodos As String
.OpenReport "rpt_Evolucao_grafPizza", acViewPreview
'matriz dos períodos
End With
'string dos períodos
Application.Echo True
Select Case Me.frm_Evolucaod_sub.SourceObject
End Select
Case "frm_Evolucao_dados"
End Sub
DoCmd.OpenReport "rpt_Evolucao", acViewPreview
EXPORTANDO OS DADOS
Case "frm_Evolucao_grafBarra"
'abro a estrutura do relatório e altero a consulta do gráfico antes de
visualizar os dados
Application.Echo False
DoCmd.OpenReport "rpt_Evolucao_grafBarra", acViewDesign
matPeriodos = Split(Forms!frm_Evolucao!strPeriodo, ",")
For i = 0 To UBound(matPeriodos)
strPeriodos = strPeriodos & "[" & matPeriodos(i) & "],"
Next
strPeriodos = Left(strPeriodos, Len(strPeriodos) - 1)
Reports!rpt_Evolucao_grafBarra!grfProdutos.RowSource = "SELECT
Através da propriedade “Barra de menus” dos relatórios,
chamamos um menu personalizado construído através de
macros de menu. Geralmente utilizo esse recurso para
disponibilizar diversas opções para qualquer relatório de
um sistema.
Aqui temos comandos como: Configurar Impressora, a impressão propriamente dita, envio do relatório para o Excel
e o Word (através dos métodos TransferText e Transfer
SpreadSheet), visualização do relatório com 1 ou 2 páginas e o comando mais interessante desse menu chamado
“Interface Excel”.
Trim(qrc_Evolucao_dados.Produto) AS Produto, " & strPeriodos & _
"FROM
qrc_Evolucao_dados;"
With DoCmd
.SetWarnings False
.Close acReport, "rpt_Evolucao_grafBarra"
.SetWarnings True
.OpenReport "rpt_Evolucao_grafBarra", acViewPreview
End With
Application.Echo True
Case "frm_Evolucao_grafPizza"
'abro a estrutura do relatório e altero a consulta do gráfico antes de
visualizar os dados
Application.Echo False
DoCmd.OpenReport "rpt_Evolucao_grafPizza", acViewDesign
matPeriodos = Split(Forms!frm_Evolucao!strPeriodo, ",")
For i = 0 To UBound(matPeriodos)
strPeriodos = strPeriodos & "[" & matPeriodos(i) & "]+"
Next
strPeriodos = Left(strPeriodos, Len(strPeriodos) - 1)
Figura 6 - Configurando a macro para o relatório
Reports!rpt_Evolucao_grafPizza!grfProdutos.RowSource =
"SELECT Trim(qrc_Evolucao_dados.Produto) AS Produto, " & strPeriodos & "
AS Período " & _
"FROM qrc_Evolucao_dados;"
With DoCmd
.SetWarnings False
38
MAIO/JUNHO 2006 - REVISTA Nº 72
Esse comando chama uma rotina “rstExcel”, onde, através do
método CopyFromRecordset do Excel, passo o RecordSource
do Relatório aberto para o Excel e realizo diversas formatações
automatizadas via VBA. Mas para utilizar esses recursos é necessário adicionar a referência para os objetos do Excel, como
mostro na tela seguinte:
WWW.FORUMACCESS.COM.BR
Access Avançado
.Cells(1, 1).Resize(1, rst.Fields.Count) = vaTmp
.Range("A2").Select
.Range(.Cells(2, 1), .Cells(intMaxRow,
intMaxCol)).CopyFromRecordset rst
'formatação conforme conteúdo do campo
For i = 1 To rst.Fields.Count - 1
Select Case rst.Fields(i).Type
Case dbDate: .Columns(i + 1).NumberFormat = "dd/mm/
yyyy"
Case dbByte: .Columns(i + 1).NumberFormat = "General"
Case dbDouble: .Columns(i + 1).NumberFormat =
"#,##0.00"
End Select
Next
'exclusão de colunas e campos zerados
.Columns("A:B").Delete
.Range("A1").Select
Figura 7 - Adicionando a referência para os objetos do Excel
.Range(.Selection, .Selection.End(xlToRight)).Select
A utilização do método CopyFromRecordset é um exemplo das
opções disponíveis para tratamento de dados no Excel e pode
ser vista na função listada a seguir:
.Range(.Selection, .Selection.End(xlDown)).Select
.DisplayAlerts = False
.Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False,
Function rstExcel()
SearchFormat:=False, ReplaceFormat:=False
.DisplayAlerts = True
'transfere o recordset do relatório ativo para o Excel _
utilizando o método CopyFromRecordset
'formatação da planilha
.Range("B1").Select
Dim rst As Recordset
.Range(.Selection, .Selection.End(xlToRight)).Select
Dim intMaxCol As Integer
.Selection.NumberFormat = "mm/yyyy"
Dim intMaxRow As Integer
.Rows(1).Font.Bold = True
Dim appExcel As Excel.Application
.Range("A2").Select
Dim i As Integer
.ActiveWindow.FreezePanes = True
.Cells.Select
Set rst = CurrentDb.OpenRecordset(Reports(0).RecordSource,
dbOpenSnapshot)
.Cells.EntireColumn.AutoFit
.Range("A2").Select
End With
intMaxCol = rst.Fields.Count
End If
If rst.RecordCount > 0 Then
rst.MoveLast: rst.MoveFirst
End Function
intMaxRow = rst.RecordCount + 1
Set appExcel = New Excel.Application
GRÁFICOS
With appExcel
Como dito antes, os botões de gráficos do sistema permitem chamar os subformulários preparados para essa estrutura.
Os dados também são atualizados pela seleção de
parâmetros do formulário, acessando a função “AlteraRefCz”
do subformulário de gráfico. Com poucos ajustes, a rotina
segue as mesmas estruturas de geração de consultas e atualização de dados. A formatação do gráfico foi feita diretamente no objeto.
.Visible = True
ReDim vaTmp(1 To rst.Fields.Count)
For i = 1 To rst.Fields.Count
vaTmp(i) = rst.Fields(i - 1).Name
Next
.Workbooks.Add
FÓRUMACCESS
MAIO/JUNHO 2006 - REVISTA Nº 72
39
Access
Avançado
Access
Avançado
Figura 8 - Gráfico de barras
Figura 9 - Gráficos do tipo pizza
CONCLUSÃO
O aplicativo original, que deu a idéia para essa matéria, está
em uso desde 2002 com os usuários muito satisfeitos com
as possibilidades geradas e os resultados desejados superados. As consultas e gráficos disponíveis já auxiliaram diversas decisões da diretoria e o acompanhamento evolutivo
dos dados permitiu alertar os usuários sobre quaisquer oscilações da utilização de recursos da área de TI.
Com uma estrutura simples, rápida e eficiente, o aplicativo
gerencia atualmente uma base de dados de aproximadamente 60.000 registros por mês.
Espero que essa matéria ilumine o caminho de diversos pro-
40
MAIO/JUNHO 2006 - REVISTA Nº 72
jetos semelhantes para serem aprimorados com as técnicas
aqui apresentadas. Fiquem à vontade para mandar idéias,
sugestões e críticas.
Bom estudo a todos!
* Paulo Sergio Sarraino ([email protected]) é analista é sócio da Sarraino
Systems. Possui graduação de Tecnologia em Processamento de Dados pela
FASP (1997) e pós-graduação em Análise de Sistemas c/ Ênfase em Gerência
de Projetos pela FIAP (2000). Atua como consultor em Análise e Desenvolvimento de Sistemas em ambiente MS Office e Internet (especialista em Access,
VBA e ASP) há 10 anos.
WWW.FORUMACCESS.COM.BR