[dba-SQLServer] List Box values to SP

The following sub takes the values from two text boxes (one for the beginning date, one for the ending date), then sends them to a few sprocs and then finally opens a report.

I don't have any validation here to make sure the dates are valid, which really should be added.....

There should be enough here to get you started.




Private Sub cmdRunReport_Click()
Dim dtBegin As Date
Dim dtEnd As Date
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
Dim prm As ADODB.Parameter
dtBegin = txtBegin.Value
dtEnd = txtEnd.Value
Set prm = cmd.CreateParameter("dtBegin", adDate, adParamInput, , dtBegin)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("dtEnd", adDate, adParamInput, , dtEnd)
cmd.Parameters.Append prm
cmd.CommandText = "dbo.spJLFilteredBBTs"
cmd.Execute
cmd.CommandText = "dbo.spJLFilteredNotPacked"
cmd.Execute
cmd.CommandText = "dbo.spJLPackageDataPack"
cmd.Execute
cmd.CommandText = "dbo.spJLPackNotFiltered"
cmd.Execute
Set prm = Nothing
Set cmd = Nothing
cmdBBTs.Enabled = True
Dim stDocName As String
stDocName = "rptBATFJeff"
DoCmd.OpenReport stDocName, acPreview

End Sub



To send list box values you can piece something together from the following two snippets.  The first sends values from one list box to another:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strFermCode As String
Dim intCurrentRow As Integer
Set ctlSource = Forms!frmFilterCodeCreate!lstCellar
Set ctlDest = Forms!frmFilterCodeCreate!lstSelected
For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
        strFermCode = ctlSource.Column(3)
        strItems = strItems & ctlSource.Column(0, intCurrentRow) & ";"
        strItems = strItems & """" & strFermCode & """" & ";"
    End If
Next intCurrentRow
    ' Reset destination control's RowSource property.
ctlDest.RowSource = ""
ctlDest.RowSource = strItems
lstSelected.Requery


Here is a snippet that will loop through a multi-select list box, append a parameter, run a sproc, delete the parameter and repeat.

It is probably better for a multi-select list box to send the entire string of selected items to the sproc, parse it there and run it multiple times.

    For i = 0 To lstSelected.ListCount - 1
        intFERMID = lstSelected.Column(0, i)
        Set prm = cmd.CreateParameter("FERMID", adInteger, adParamInput, , intFERMID)
        cmd.Parameters.Append prm
        cmd.Execute
        cmd.Parameters.Delete "FERMID"
    Next





Message: 3
Date: Sat, 8 May 2004 10:54:18 +0100
From: "Martin Reid" <mwp.reid at qub.ac.uk>
Subject: [dba-SQLServer] List Box values to SP
To: <dba-sqlserver at databaseadvisors.com>
Message-ID: <006301c434e2$716a9480$1b02a8c0 at MARTINREID>
Content-Type: text/plain;	charset="iso-8859-1"

I need to pass the values from a list box in Access to a stored procedure
which does a multi table insert.Unless of course theres a better way to do
this?

Martin





------------------------------

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver


End of dba-SQLServer Digest, Vol 15, Issue 6
********************************************