Selecting Top Values/Records for a query via Form

[RZ15]

Hi,

I have a form that opens a report. The form allows the user to pick a particular warehouse or supplier and an order to sort by. Here is the code for it:

Private Sub cmdOK_Click()
 
    Dim strSQL As String
    If Me.cboSort1 <> "" Then
        strSQL = strSQL & Me.cboSort1
        If Me.Chk1 = True Then
            strSQL = strSQL & " DESC"
        End If
        strSQL = strSQL & ", "
        DoCmd.OpenReport "rptTopSuppliers", acViewPreview
        DoCmd.Maximize
    If strSQL <> "" Then
        strSQL = Left(strSQL, (Len(strSQL) - 2))
        Reports![rptTopSuppliers].OrderBy = strSQL
        Reports![rptTopSuppliers].OrderByOn = False
    Else
        Reports![rptTopSuppliers].OrderByOn = True
    End If
 
    Else
        MsgBox "Please choose a field to sort by.", vbOKOnly, "Missing Information"
    End If
End Sub
 

In this, I want to include a list box that tells the report to only return top 5, 10, 25 or all records (or if possible, have the user enter how many records and if left blank return all records). I need help incorporating this. I tried it briefly, but I am too unfamiliar with VB. I need a good place to start I think, because I'm baffled at the moment.

[JConsulting]

Hi,

I have a form that opens a report. The form allows the user to pick a particular warehouse or supplier and an order to sort by. Here is the code for it:

Private Sub cmdOK_Click()
 
    Dim strSQL As String
    If Me.cboSort1 <> "" Then
        strSQL = strSQL & Me.cboSort1
        If Me.Chk1 = True Then
            strSQL = strSQL & " DESC"
        End If
        strSQL = strSQL & ", "
        DoCmd.OpenReport "rptTopSuppliers", acViewPreview
        DoCmd.Maximize
    If strSQL <> "" Then
        strSQL = Left(strSQL, (Len(strSQL) - 2))
        Reports![rptTopSuppliers].OrderBy = strSQL
        Reports![rptTopSuppliers].OrderByOn = False
    Else
        Reports![rptTopSuppliers].OrderByOn = True
    End If
 
    Else
        MsgBox "Please choose a field to sort by.", vbOKOnly, "Missing Information"
    End If
End Sub
 

In this, I want to include a list box that tells the report to only return top 5, 10, 25 or all records (or if possible, have the user enter how many records and if left blank return all records). I need help incorporating this. I tried it briefly, but I am too unfamiliar with VB. I need a good place to start I think, because I'm baffled at the moment.

You're going to have to split up your starting strSQL this way:

        If Nz(Me.mylistbox, "") <> "" Then
            strSQL = "select top" & Me.mylistbox & " FROM yourtable "
        End If
 

Remember that combo boxes are 0 based..so the first column is going to be the value used, unless you select the column number me.mylistbox.column(1)

J