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

Sort Order Combo box for report not working

P: 53
Hi guys,

I have made a form with a combo box listing fields that a user can sort by. I have a code that works for this when i have 4 levels to sort by. It is the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOK_Click()
  2.  
  3. Dim strSQL As String, intCounter As Integer
  4. For intCounter = 1 to 4
  5.  If Me("cboSort" & intCounter) <> "" Then
  6.   strSQL = strSQL & Me("cboSort" & intCounter)
  7.  If Me("Chk" & intCounter) = True Then
  8.   strSQL = strSQL & " DESC"
  9.  End If
  10. strSQL = strSQL & ", "
  11. End If
  12. Next
  13.  If cboSupplier <> "" Then
  14.   DoCmd.OpenReport "rptProdPerSupp", acViewPreview
  15.   DoCmd.Maximize
  16.  If strSQL <> "" Then
  17.   strSQL = Left(strSQL, (Len(strSQL) - 2))
  18.   Reports![rptProdPerSupp].OrderBy = strSQL
  19.   Reports![rptProdPerSupp].OrderByOn = True
  20.  Else
  21.   Reports![rptProdPerSupp].OrderByOn = False
  22.  End If
  23.  Else
  24.   MsgBox "Please choose a supplier whose products you would like to see."
  25.  End If
  26.  
  27. End Sub
  28.  
This code works fine for multiple sorting levels. Now I have another form that has a single combo box in which you can choose a field to sort by. I tried translating the above code into one that uses just one combo box for sorting. Here is what I did:

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. Else
  20.  MsgBox "Please choose a field to sort by.", vbOKOnly, "Missing Information"
  21. End If
  22. End Sub
  23.  
Needless to say, it does not sort. Can anyone pinpoint where I went wrong? The problem must lie in the first If statement in the code, since that is the only thing I changed.
Jul 26 '07 #1
Share this Question
Share on Google+
3 Replies


P: 53
can anyone help me with this?
Jul 27 '07 #2

P: 53
For the life of me I can't figure this out, can someone try taking a look at this please?
Jul 30 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOK_Click()
  2. Dim strSQL As String
  3.  
  4.     If Me.cboSort1 <> "" Then
  5.         strSQL = Me.cboSort1
  6.         If Me.Chk1 = True Then
  7.             strSQL = strSQL & " DESC"
  8.         End If
  9.         DoCmd.OpenReport "rptTopSuppliers", acViewPreview
  10.         DoCmd.Maximize
  11.         If strSQL <> "" Then
  12.             Reports![rptTopSuppliers].OrderBy = strSQL
  13.             Reports![rptTopSuppliers].OrderByOn = True
  14.         Else
  15.             Reports![rptProdPerSupp].OrderByOn = False
  16.         End If
  17.     Else
  18.         MsgBox "Please choose a field to sort by.", vbOKOnly, "Missing Information"
  19.     End If
  20.  
  21. End Sub
  22.  
Aug 6 '07 #4

Post your reply

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