By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Selecting Top Values/Records for a query via Form

P: 53
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOK_Click()
  2.  
  3.     Dim strSQL As String
  4.     If Me.cboSort1 <> "" Then
  5.         strSQL = strSQL & Me.cboSort1
  6.         If Me.Chk1 = True Then
  7.             strSQL = strSQL & " DESC"
  8.         End If
  9.         strSQL = strSQL & ", "
  10.         DoCmd.OpenReport "rptTopSuppliers", acViewPreview
  11.         DoCmd.Maximize
  12.     If strSQL <> "" Then
  13.         strSQL = Left(strSQL, (Len(strSQL) - 2))
  14.         Reports![rptTopSuppliers].OrderBy = strSQL
  15.         Reports![rptTopSuppliers].OrderByOn = False
  16.     Else
  17.         Reports![rptTopSuppliers].OrderByOn = True
  18.     End If
  19.  
  20.     Else
  21.         MsgBox "Please choose a field to sort by.", vbOKOnly, "Missing Information"
  22.     End If
  23. End Sub
  24.  
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.
Jul 24 '07 #1
Share this Question
Share on Google+
1 Reply


JConsulting
Expert 100+
P: 603
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOK_Click()
  2.  
  3.     Dim strSQL As String
  4.     If Me.cboSort1 <> "" Then
  5.         strSQL = strSQL & Me.cboSort1
  6.         If Me.Chk1 = True Then
  7.             strSQL = strSQL & " DESC"
  8.         End If
  9.         strSQL = strSQL & ", "
  10.         DoCmd.OpenReport "rptTopSuppliers", acViewPreview
  11.         DoCmd.Maximize
  12.     If strSQL <> "" Then
  13.         strSQL = Left(strSQL, (Len(strSQL) - 2))
  14.         Reports![rptTopSuppliers].OrderBy = strSQL
  15.         Reports![rptTopSuppliers].OrderByOn = False
  16.     Else
  17.         Reports![rptTopSuppliers].OrderByOn = True
  18.     End If
  19.  
  20.     Else
  21.         MsgBox "Please choose a field to sort by.", vbOKOnly, "Missing Information"
  22.     End If
  23. End Sub
  24.  
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:

Expand|Select|Wrap|Line Numbers
  1.         If Nz(Me.mylistbox, "") <> "" Then
  2.             strSQL = "select top" & Me.mylistbox & " FROM yourtable "
  3.         End If
  4.  
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
Jul 31 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.