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

Clear form button and the code to make it work

P: 11
I am using a form to sort a report, but I can't seem to get the "clear form" button to work. I used the wizard when I installed the button, using the clear form option under the form option list. What I want to be able to do is clear the information on the drop down combo box in case I want to change the sort order. This is what the code looks like now:

Option Compare Database

Expand|Select|Wrap|Line Numbers
  1. Private Sub close_Click()
  2. On Error GoTo Err_close_Click
  3.  
  4.  
  5.     DoCmd.close
  6.  
  7. Exit_close_Click:
  8.     Exit Sub
  9.  
  10. Err_close_Click:
  11.     MsgBox Err.Description
  12.     Resume Exit_close_Click
  13.  
  14. End Sub
  15.  
  16. Private Sub cmdSetSort_Click()
  17.  
  18.     Dim strSQL As String, intCounter As Integer
  19.     'Build strSQL String
  20.     For intCounter = 1 To 4
  21.         If Me("cboSort" & intCounter) <> "" Then
  22.         strSQL = strSQL & "[" & Me("cboSort" & intCounter) & "]"
  23.         If Me("Chk" & intCounter) = True Then
  24.             strSQL = strSQL & " DESC"
  25.         End If
  26.         strSQL = strSQL & ", "
  27.     End If
  28.     Next
  29.  
  30.     If strSQL <> "" Then
  31.         'Strip Last Comma & Space
  32.         strSQL = Left(strSQL, (Len(strSQL) - 2))
  33.         'Set the OrderBy property
  34.         Reports![rptPersonnel].OrderBy = strSQL
  35.         Reports![rptPersonnel].OrderByOn = True
  36.     Else
  37.         Reports![rptPersonnel].OrderByOn = False
  38.  
  39.     End If
  40.  
  41. End Sub
  42.  
  43.  
  44. Private Sub Form_Open(Cancel As Integer)
  45. ' Open the report maximized, in Print Preview
  46.     DoCmd.OpenReport "rptPersonnel", acViewPreview
  47.     DoCmd.Maximize
  48. End Sub
  49.  
  50. Private Sub Refresh_Click()
  51. On Error GoTo Err_Refresh_Click
  52.  
  53.  
  54.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  55.  
  56. Exit_Refresh_Click:
  57.     Exit Sub
  58.  
  59. Err_Refresh_Click:
  60.     MsgBox Err.Description
  61.     Resume Exit_Refresh_Click
  62.  
  63. End Sub
Not sure what I'm doing wrong, but I would appreciate some help. Also attached is a picture of what my sort box looks like.
Attached Images
File Type: gif combobox.gif (4.6 KB, 581 views)
Feb 11 '08 #1
Share this Question
Share on Google+
1 Reply


P: 11
I am using a form to sort a report, but I can't seem to get the "clear form" button to work. I used the wizard when I installed the button, using the clear form option under the form option list. What I want to be able to do is clear the information on the drop down combo box in case I want to change the sort order. This is what the code looks like now:

Option Compare Database

Expand|Select|Wrap|Line Numbers
  1. Private Sub close_Click()
  2. On Error GoTo Err_close_Click
  3.  
  4.  
  5.     DoCmd.close
  6.  
  7. Exit_close_Click:
  8.     Exit Sub
  9.  
  10. Err_close_Click:
  11.     MsgBox Err.Description
  12.     Resume Exit_close_Click
  13.  
  14. End Sub
  15.  
  16. Private Sub cmdSetSort_Click()
  17.  
  18.     Dim strSQL As String, intCounter As Integer
  19.     'Build strSQL String
  20.     For intCounter = 1 To 4
  21.         If Me("cboSort" & intCounter) <> "" Then
  22.         strSQL = strSQL & "[" & Me("cboSort" & intCounter) & "]"
  23.         If Me("Chk" & intCounter) = True Then
  24.             strSQL = strSQL & " DESC"
  25.         End If
  26.         strSQL = strSQL & ", "
  27.     End If
  28.     Next
  29.  
  30.     If strSQL <> "" Then
  31.         'Strip Last Comma & Space
  32.         strSQL = Left(strSQL, (Len(strSQL) - 2))
  33.         'Set the OrderBy property
  34.         Reports![rptPersonnel].OrderBy = strSQL
  35.         Reports![rptPersonnel].OrderByOn = True
  36.     Else
  37.         Reports![rptPersonnel].OrderByOn = False
  38.  
  39.     End If
  40.  
  41. End Sub
  42.  
  43.  
  44. Private Sub Form_Open(Cancel As Integer)
  45. ' Open the report maximized, in Print Preview
  46.     DoCmd.OpenReport "rptPersonnel", acViewPreview
  47.     DoCmd.Maximize
  48. End Sub
  49.  
  50. Private Sub Refresh_Click()
  51. On Error GoTo Err_Refresh_Click
  52.  
  53.  
  54.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  55.  
  56. Exit_Refresh_Click:
  57.     Exit Sub
  58.  
  59. Err_Refresh_Click:
  60.     MsgBox Err.Description
  61.     Resume Exit_Refresh_Click
  62.  
  63. End Sub
Not sure what I'm doing wrong, but I would appreciate some help. Also attached is a picture of what my sort box looks like.
Never mind, I figured out the answer by myself. For those that are interested about the answer though, here is what I figured out:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdClear_Click()
  2. On Error Resume Next
  3. Me.cboSort1 = ""
  4. Me.cbosort2 = ""
  5. Me.cboSort3 = ""
  6. Me.cboSort4 = ""
  7. cboSort1.SetFocus
  8. End Sub
Hope this helps those of us out there that are still trying to puzzle their way through VBA and Access.
Feb 12 '08 #2

Post your reply

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