Pessoal,
Amigo Balem consulta strsql ainda não carregada no listbox somente após o Do Until rs.EOF
... mas o seu post me trouxe a luz de uma tentantiva que era gerar a consulta e carregar direto pelo RowSource o que tentei e deu certo....obrigado...
------------------------------------------------*--------------------*-----------------------------*--------------
Public Function ATUALIZAR()
Dim strSql As String
Dim strvl As String
Dim reg As Variant
If IsNull(Me.cbo_filial) Then
MsgBox "Não foi selecionado uma Filial", vbExclamation, "Opção"
Call INATIVAR
Exit Function
End If
If Me.cbo_tipo = "" Then
MsgBox "Não foi selecionado um Tipo de Nota", vbExclamation, "Opção"
Call INATIVAR
Exit Function
End If
'Relação das Notas Pendentes
'Verificar a opção do Tipo de Nota
If Me.cbo_filial = "TODAS" And Me.cbo_tipo = "TODAS" Then
strSql = "SELECT TBL_PEND.FILIAL AS FILIAL, TBL_PEND.FILIAL_DEST AS FL_DESTINO, TBL_PEND.NOTA_FISCAL AS NOTA, TBL_PEND.SERIE AS SERIE, FormatNumber(([TBL_PEND]![VALOR_NOTA_FISCAL])) AS VALOR, TBL_CFOP.TIPO_NOTA AS TIPO, TBL_PEND.DT_EMISSAO AS DATA, " & _
"TBL_PEND.CNPJ AS CNPJ, TBL_PEND.NOME AS NOME " & _
"FROM (TBL_CFOP RIGHT JOIN TBL_PEND ON TBL_CFOP.CFOP = TBL_PEND.CFOP) LEFT JOIN TBL_NR_EXC ON (TBL_PEND.SERIE = TBL_NR_EXC.SERIE) AND (TBL_PEND.NOTA_FISCAL = TBL_NR_EXC.NOTA_FISCAL) AND (TBL_PEND.FILIAL = TBL_NR_EXC.FILIAL) " & _
"WHERE (((TBL_NR_EXC.NR_EXC) Is Null)) " & _
"ORDER BY TBL_PEND.DT_EMISSAO, TBL_PEND.FILIAL, TBL_PEND.SERIE;"
ElseIf Me.cbo_filial <> "TODAS" And Me.cbo_tipo = "TODAS" Then
strSql = "SELECT TBL_PEND.FILIAL AS FILIAL, TBL_PEND.FILIAL_DEST AS FL_DESTINO, TBL_PEND.NOTA_FISCAL AS NOTA, TBL_PEND.SERIE AS SERIE, FormatNumber(([TBL_PEND]![VALOR_NOTA_FISCAL])) AS VALOR, TBL_CFOP.TIPO_NOTA AS TIPO, TBL_PEND.DT_EMISSAO AS DATA, " & _
"TBL_PEND.CNPJ AS CNPJ, TBL_PEND.NOME AS NOME " & _
"FROM (TBL_CFOP RIGHT JOIN TBL_PEND ON TBL_CFOP.CFOP = TBL_PEND.CFOP) LEFT JOIN TBL_NR_EXC ON (TBL_PEND.FILIAL = TBL_NR_EXC.FILIAL) AND (TBL_PEND.NOTA_FISCAL = TBL_NR_EXC.NOTA_FISCAL) AND (TBL_PEND.SERIE = TBL_NR_EXC.SERIE) " & _
"WHERE (((TBL_PEND.FILIAL)=" & Me.cbo_filial & ") AND ((TBL_NR_EXC.NR_EXC) Is Null)) " & _
"ORDER BY TBL_PEND.DT_EMISSAO, TBL_PEND.FILIAL, TBL_PEND.SERIE;"
ElseIf Me.cbo_filial = "TODAS" And Me.cbo_tipo <> "TODAS" Then
strSql = "SELECT TBL_PEND.FILIAL AS FILIAL, TBL_PEND.FILIAL_DEST AS FL_DESTINO, TBL_PEND.NOTA_FISCAL AS NOTA, TBL_PEND.SERIE AS SERIE, FormatNumber(([TBL_PEND]![VALOR_NOTA_FISCAL])) AS VALOR, TBL_CFOP.TIPO_NOTA AS TIPO, TBL_PEND.DT_EMISSAO AS DATA, " & _
"TBL_PEND.CNPJ AS CNPJ, TBL_PEND.NOME AS NOME " & _
"FROM (TBL_CFOP INNER JOIN TBL_PEND ON TBL_CFOP.CFOP = TBL_PEND.CFOP) LEFT JOIN TBL_NR_EXC ON (TBL_PEND.SERIE = TBL_NR_EXC.SERIE) AND (TBL_PEND.NOTA_FISCAL = TBL_NR_EXC.NOTA_FISCAL) AND (TBL_PEND.FILIAL = TBL_NR_EXC.FILIAL) " & _
"WHERE (((TBL_CFOP.TIPO_NOTA)='" & Me.cbo_tipo & "') AND ((TBL_NR_EXC.NR_EXC) Is Null)) " & _
"ORDER BY TBL_PEND.DT_EMISSAO, TBL_PEND.FILIAL, TBL_PEND.SERIE;"
Else:
strSql = "SELECT TBL_PEND.FILIAL AS FILIAL, TBL_PEND.FILIAL_DEST AS FL_DESTINO, TBL_PEND.NOTA_FISCAL AS NOTA, TBL_PEND.SERIE AS SERIE, FormatNumber(([TBL_PEND]![VALOR_NOTA_FISCAL])) AS VALOR, TBL_CFOP.TIPO_NOTA AS TIPO, TBL_PEND.DT_EMISSAO AS DATA, " & _
"TBL_PEND.CNPJ AS CNPJ, TBL_PEND.NOME AS NOME " & _
"FROM (TBL_CFOP RIGHT JOIN TBL_PEND ON TBL_CFOP.CFOP = TBL_PEND.CFOP) LEFT JOIN TBL_NR_EXC ON (TBL_PEND.FILIAL = TBL_NR_EXC.FILIAL) AND (TBL_PEND.NOTA_FISCAL = TBL_NR_EXC.NOTA_FISCAL) AND (TBL_PEND.SERIE = TBL_NR_EXC.SERIE) " & _
"WHERE (((TBL_PEND.FILIAL)=" & Me.cbo_filial & ") AND ((TBL_CFOP.TIPO_NOTA)='" & Me.cbo_tipo & "') AND ((TBL_NR_EXC.NR_EXC) Is Null)) " & _
"ORDER BY TBL_PEND.DT_EMISSAO, TBL_PEND.FILIAL, TBL_PEND.SERIE;"
End If
''Carregar a Relação de Notas Pendentes no Painel
Me.lst_nota.RowSource = ""
Me.lst_nota.RowSource = strSql
'Carregar a Quantidade de Notas
reg = Me.lst_nota.ListCount
Me.txt_Tnota = reg
Me.txt_nota_2 = reg
'Calcular o Valor em aberto de Notas Pendentes
If Me.cbo_filial = "TODAS" And Me.cbo_tipo = "TODAS" Then
strvl = "SELECT TBL_PEND.FILIAL AS FILIAL, Count(TBL_PEND.NOTA_FISCAL) AS QTDE, Sum(TBL_PEND.VALOR_NOTA_FISCAL) AS VALOR, NULL AS TIPO " & _
"FROM (TBL_PEND INNER JOIN TBL_CFOP ON TBL_PEND.CFOP = TBL_CFOP.CFOP) LEFT JOIN TBL_NR_EXC ON (TBL_PEND.SERIE = TBL_NR_EXC.SERIE) AND (TBL_PEND.NOTA_FISCAL = TBL_NR_EXC.NOTA_FISCAL) AND (TBL_PEND.FILIAL = TBL_NR_EXC.FILIAL) " & _
"GROUP BY TBL_PEND.FILIAL,TBL_NR_EXC.NR_EXC " & _
"HAVING (((TBL_NR_EXC.NR_EXC) Is Null));"
ElseIf Me.cbo_filial <> "TODAS" And Me.cbo_tipo = "TODAS" Then
strvl = "SELECT TBL_PEND.FILIAL AS FILIAL, Count(TBL_PEND.NOTA_FISCAL) AS QTDE, Sum(TBL_PEND.VALOR_NOTA_FISCAL) AS VALOR, TBL_CFOP.TIPO_NOTA AS TIPO " & _
"FROM (TBL_PEND INNER JOIN TBL_CFOP ON TBL_PEND.CFOP = TBL_CFOP.CFOP) LEFT JOIN TBL_NR_EXC ON (TBL_PEND.SERIE = TBL_NR_EXC.SERIE) AND (TBL_PEND.NOTA_FISCAL = TBL_NR_EXC.NOTA_FISCAL) AND (TBL_PEND.FILIAL = TBL_NR_EXC.FILIAL) " & _
"GROUP BY TBL_PEND.FILIAL,TBL_CFOP.TIPO_NOTA,TBL_NR_EXC.NR_EXC " & _
"HAVING (((TBL_PEND.FILIAL)=" & Me.cbo_filial & ") AND ((TBL_NR_EXC.NR_EXC) Is Null));"
ElseIf Me.cbo_filial = "TODAS" And Me.cbo_tipo <> "TODAS" Then
strvl = "SELECT TBL_PEND.FILIAL AS FILIAL, Count(TBL_PEND.NOTA_FISCAL) AS QTDE, Sum(TBL_PEND.VALOR_NOTA_FISCAL) AS VALOR, TBL_CFOP.TIPO_NOTA AS TIPO " & _
"FROM (TBL_PEND INNER JOIN TBL_CFOP ON TBL_PEND.CFOP = TBL_CFOP.CFOP) LEFT JOIN TBL_NR_EXC ON (TBL_PEND.SERIE = TBL_NR_EXC.SERIE) AND (TBL_PEND.NOTA_FISCAL = TBL_NR_EXC.NOTA_FISCAL) AND (TBL_PEND.FILIAL = TBL_NR_EXC.FILIAL) " & _
"GROUP BY TBL_PEND.FILIAL,TBL_CFOP.TIPO_NOTA,TBL_NR_EXC.NR_EXC " & _
"HAVING (((TBL_CFOP.TIPO_NOTA)='" & Me.cbo_tipo & "') AND ((TBL_NR_EXC.NR_EXC) Is Null));"
Else:
strvl = "SELECT TBL_PEND.FILIAL AS FILIAL, Count(TBL_PEND.NOTA_FISCAL) AS QTDE, Sum(TBL_PEND.VALOR_NOTA_FISCAL) AS VALOR, TBL_CFOP.TIPO_NOTA AS TIPO " & _
"FROM (TBL_PEND INNER JOIN TBL_CFOP ON TBL_PEND.CFOP = TBL_CFOP.CFOP) LEFT JOIN TBL_NR_EXC ON (TBL_PEND.SERIE = TBL_NR_EXC.SERIE) AND (TBL_PEND.NOTA_FISCAL = TBL_NR_EXC.NOTA_FISCAL) AND (TBL_PEND.FILIAL = TBL_NR_EXC.FILIAL) " & _
"GROUP BY TBL_PEND.FILIAL,TBL_CFOP.TIPO_NOTA,TBL_NR_EXC.NR_EXC " & _
"HAVING (((TBL_PEND.FILIAL)=" & Me.cbo_filial & ") AND ((TBL_CFOP.TIPO_NOTA)='" & Me.cbo_tipo & "') AND ((TBL_NR_EXC.NR_EXC) Is Null));"
End If
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
'Carregar a o valor total em aberto
rs.Open strvl, cnn
Me.list_valores_2.RowSource = ""
Me.list_valores.RowSource = ""
If Me.cbo_filial = "TODAS" And Me.cbo_tipo = "TODAS" Then
Do Until rs.EOF
Me.list_valores_2.AddItem rs!FILIAL & ";" & rs!qtde & ";" & Format(rs!VALOR, "currency")
rs.MoveNext
Loop
' Encerrar Conexão
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Call SomaListBox_2
Else:
Me.list_valores.RowSource = ""
Do Until rs.EOF
Me.list_valores.AddItem rs!FILIAL & ";" & rs!TIPO & ";" & rs!qtde & ";" & Format(rs!VALOR, "currency")
rs.MoveNext
Loop
'Encerrar Conexão
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Call SomaListBox
End If
End Function