List Box Access 97

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an SQL query that returns the result in a List Box,
I would like to save that result in a table to use for reporting.

The list box is in a Form, that requires users to fill in variables.

I display the result in a list box control, but I also need to use the result
for reporting purposes.
 
With whatever query you use to populate the list box, use as the basis of an
append query... to write the data to the desired table.
 
I used an SQL statement to populate the list box, it is create with different
options user pick on a form. When I try to convert the result to a query, the
optional fields on the form become parameters that I have to input again to
create the result. How could I pass the value of the variable fields to the
query definition, in order to save the results.

Option Compare Database
Option Explicit
Dim SQL As String
Dim v_txtTB As Double
Dim v_txtFL As Double
Dim v_vtxtFI As Double
Dim v_txtTML As Double
Public Sub RefreshQuery()
'
If Me.chkFctBanque Then
SQL = "SELECT * , ([SALAIRE_ANNUALISE]*([PRIME_PCT]/100))* txtTB * vtxtFI AS
'PRIME_VERSEE'" _
& " FROM [Tableau Primes Yés] WHERE [NO_YE] <> '999999'"
End If
'
If Me.chkFctLigne Then
SQL = "SELECT *, ([SALAIRE_ANNUALISE]*([PRIME_PCT]/100))*((PCT_FB * txtTB) +
(PCT_LIGNE * txtFL) * [vtxtFI]) AS 'PRIME_VERSEE' FROM [Tableau Primes Yés]" _
& " WHERE [BUSLINE_CD] = [cmbRechLigne]"
End If
'
If Me.chkFctEquipe Then
SQL = "SELECT *, (([SALAIRE_ANNUALISE]*([PRIME_PCT]/100))*((PCT_FB * txtTB)
+ (PCT_LIGNE * txtFL) + (PCT_EQUIPE * txtTML)) * [vtxtFI]) AS 'PRIME_VERSEE'
FROM [Tableau Primes Yés]" _
& " WHERE [BUSLINE_CD] = 'L101' AND ACTYSUBSECT_CD = [cmbRechEquipe]"
End If
'
SQL = SQL & ";"
'
Me.lstResult.RowSource = SQL
Me.lstResult.Requery
'
End Sub

Private Sub chkFctBanque_Click()
Me.txtTB.Visible = Not Me.txtTB.Visible
RefreshQuery
End Sub

Private Sub chkFctEquipe_Click()
Me.cmbRechEquipe.Visible = Not Me.cmbRechEquipe.Visible
RefreshQuery
End Sub

Private Sub chkFctLigne_Click()
Me.cmbRechLigne.Visible = Not Me.cmbRechLigne.Visible
RefreshQuery
End Sub

Private Sub cmbRechEquipe_AfterUpdate()
RefreshQuery
Me.txtTML.Visible = Not Me.txtTML.Visible
End Sub

Private Sub cmbRechLigne_AfterUpdate()
RefreshQuery
Me.txtFL.Visible = Not Me.txtFL.Visible
End Sub

Private Sub Commande30_Click()
Dim bds As Database
Dim qdf As QueryDef
Set bds = CurrentDb
bds.QueryDefs.Refresh
RefreshQuery
DoCmd.DeleteObject acQuery, "Resultats"
Set qdf = bds.CreateQueryDef("Resultats", Me.lstResult.RowSource)
DoCmd.OpenQuery qdf.Name
' Ouvre la requête en mode Feuille de données.
Set bds = Nothing
End Sub

Private Sub Form_Load()
Dim ctl As Control
For Each ctl In Me.Controls
Select Case Left(ctl.Name, 3)
Case "chk"
ctl.Value = 0
Case "txt"
ctl.Visible = False
ctl.Value = ""
Case "cmb"
ctl.Visible = False
End Select
Next ctl
End Sub

Private Sub txtFI_AfterUpdate()
RefreshQuery
End Sub

Private Sub txtFL_AfterUpdate()
RefreshQuery
End Sub

Private Sub txtTB_AfterUpdate()
RefreshQuery
End Sub

Private Sub txtTML_AfterUpdate()
RefreshQuery
End Sub
 
Back
Top