Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Posts
    20

    Unanswered: Creating Filters in MS ACCESS

    Hi Everyone,

    I am a total newbie and I have a problem developing a filter on a form in MS ACCESS (2000). I don't know where to begin, so any help would be greatly appreciated.

    The problem I have is the form has (3) command buttons and each of these buttons has to filter the data to display different components of the data. The form's data source is a query. The first button displays information for a training device component parts and associated costs. The data is then supplied to a series of subforms. The second button displays only spare parts and associated costs for the same training devices. The third button displays all costs associated with the training device (combined button 1 & 2).

    I have tried suggestions in the FAQ's but nothing seems to work. I believe this to be a reflection of my novice stature. I hope I have stated my problem sufficiently. Again, any suggestions or help would be sincerely appreciated.

  2. #2
    Join Date
    Mar 2003
    Posts
    46
    In the Click event of each of your buttons write

    Dim strsql as string

    strsql = ((query.field=""" & "criteria" & """)) [substitute whatever sql statement you need to filter your records]

    Me.filter = strsql
    Me.FilterOn = true

    You can easily get the sql statment you need from the 'WHERE' clause in the query window of your underlying query if you set it up with the relevant criteria and then switch to SQL view
    Hope this helps.

  3. #3
    Join Date
    Jul 2003
    Posts
    20
    Thank you very much for the reply. As I said in my original post, I am a complete novice and am apparently missing something that is very easy. I tried your suggestion but am still having a problem. the underlying query is:

    qrySUMMARY_LOAD

    SELECT SUMMARY_TMP.*, Left([SUMMARY_TMP]![SER_NO],1) AS CHECK_TN
    FROM SUMMARY_TMP
    ORDER BY SUMMARY_TMP.MTD, SUMMARY_TMP.BLOCK, SUMMARY_TMP.MTDP, SUMMARY_TMP.TDP, SUMMARY_TMP.PART_NO;

    This is the statement I entered at each button. In each case the value of CHECK_TN determines whether the part is for a trainer or a spare (P = Part and S= Spare)

    Private Sub cmdTRNR_Click()

    Dim strSQL As String

    On Error GoTo Err_cmdTRNR_Click

    '#######################################
    strSQL = (("qrySUMMARY_LOAD.CHECK_TN=""" & "P" & """))
    Me.Filter = strSQL
    Me.FilterOn = True

    When I try to enter the strSQL statement I get an error message:
    Compile Error: Expected: )

    What am I doing wrong?

    Thanks again for your help and your patience with a newbie.

  4. #4
    Join Date
    Mar 2003
    Posts
    46
    oops, sorry, left out quotation marks, the following shouold work:

    strSQL = "((CHECK_TN=""" & "P" & """))"

  5. #5
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133

    Lightbulb

    Originally posted by CCC
    oops, sorry, left out quotation marks, the following shouold work:

    strSQL = "((CHECK_TN=""" & "P" & """))"
    Whoa! Hold your harses, guys! Don't make Access any more complicated than needed!
    Following line should also do the job and is much easier to read :
    strSQL = "CHECK_TN='" & "P" & "'"

    Note the sequences of single and double quotation marks!

  6. #6
    Join Date
    Jul 2003
    Posts
    20
    Hi Again Everyone,

    Still havng problems. I've tried the suggestions you all have provided and still can't get the program to do what it is supposed to. It appears to work properly for the first button clicked, but clicking either of the other buttons and nothing happens. The same data is displayed as for the first selection. What am I still missing?

    It is hell being a novice!!

    Thanks again for your patience and assistance.

  7. #7
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Originally posted by REFIN
    Hi Again Everyone,

    Still havng problems. I've tried the suggestions you all have provided and still can't get the program to do what it is supposed to. It appears to work properly for the first button clicked, but clicking either of the other buttons and nothing happens. The same data is displayed as for the first selection. What am I still missing?

    It is hell being a novice!!

    Thanks again for your patience and assistance.
    Tried to requery after setting the filter?
    Just add Me.Requery at the end of the code

  8. #8
    Join Date
    Mar 2003
    Posts
    46
    Shouldn't need to do a requery...
    I know this might sound obvious but have you changed the criteria for each button?

  9. #9
    Join Date
    Jul 2003
    Posts
    20
    Hi All,

    Thanks for all your input. I've tried everything suggested and still can't seem to make it work. I have checked the criteria for each button and they are set okay. Any other suggestions?

    Thanks for everything so far. I hope being new isn't preventing me from stating something that may be obvious or necessary but my ignorance is blocking it. I really appreciate the time and help you have been giving me.

  10. #10
    Join Date
    Mar 2003
    Posts
    46
    Can you post the code for all three buttons please?

  11. #11
    Join Date
    Jul 2003
    Posts
    20
    Hi Again,

    Here is the code for the (3) buttons.

    ==============================================
    ==============================================
    Private Sub cmdTRNR_Click()

    Dim strSQL As String

    On Error GoTo Err_cmdTRNR_Click

    '#######################################
    strSQL = (("qrySUMMARY_LOAD.CHECK_TN=""" & "S" & """))
    Me.Filter = strSQL
    Me.FilterOn = True
    '#######################################
    Me.cmdTRNR.ForeColor = 12615680
    Me.cmdTRNR.FontBold = True
    Me.lbl_MTDP_TPWP.BackColor = 12615680
    Me!txtMTDP_Wrk.Visible = True
    Me.lbl_TDP.BackColor = 12615680
    Me!txtTDP_Wrk.Visible = True
    Me.sfrmSUMMARY_MTDP.BorderColor = 12615680
    Me.sfrmSUMMARY_TDP.BorderColor = 12615680
    Me.sfrmBOE.BorderColor = 12615680
    Me.sfrmPUR.BorderColor = 12615680
    Me.sfrmSCH.BorderColor = 12615680
    '#######################################
    Me.cmdPRV.FontBold = False
    Me.cmdTOT.FontBold = False
    '#######################################

    Exit_cmdTRNR_Click:
    Exit Sub

    Err_cmdTRNR_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_cmdTRNR_Click

    End Sub
    ==============================================
    ==============================================
    Private Sub cmdPRV_Click()

    Dim strSQL As String

    On Error GoTo Err_cmdTRNR_Click

    '#######################################
    strSQL = (("qrySUMMARY_LOAD.CHECK_TN=""" & "P" & """))
    Me.Filter = strSQL
    Me.FilterOn = True
    '#######################################
    Me.cmdPRV.ForeColor = 16711680
    Me.cmdPRV.FontBold = True
    Me.lbl_MTDP_TPWP.BackColor = 16711680
    Me!txtMTDP_Wrk.Visible = True
    Me.lbl_TDP.BackColor = 16711680
    Me!txtTDP_Wrk.Visible = True
    Me.sfrmSUMMARY_MTDP.BorderColor = 16711680
    Me.sfrmSUMMARY_TDP.BorderColor = 16711680
    Me.sfrmBOE.BorderColor = 16711680
    Me.sfrmPUR.BorderColor = 16711680
    Me.sfrmSCH.BorderColor = 16711680
    '#######################################
    Me.cmdTRNR.FontBold = False
    Me.cmdTOT.FontBold = False
    '#######################################

    Exit_cmdPRV_Click:
    Exit Sub

    Err_cmdPRV_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_cmdPRV_Click

    End Sub
    ==============================================
    ==============================================
    Private Sub cmdTOT_Click()

    Dim strSQL As String

    On Error GoTo Err_cmdTOT_Click

    '#######################################
    strSQL = ""
    Me.Filter = strSQL
    Me.FilterOn = False
    '#######################################
    Me.cmdTOT.ForeColor = 0
    Me.cmdTOT.FontBold = True
    Me.lbl_MTDP_TPWP.BackColor = 0
    Me!txtMTDP_Wrk.Visible = True
    Me.lbl_TDP.BackColor = 0
    Me!txtTDP_Wrk.Visible = True
    Me.sfrmSUMMARY_MTDP.BorderColor = 0
    Me.sfrmSUMMARY_TDP.BorderColor = 0
    Me.sfrmBOE.BorderColor = 0
    Me.sfrmPUR.BorderColor = 0
    Me.sfrmSCH.BorderColor = 0
    '#######################################
    Me.cmdPRV.FontBold = False
    Me.cmdTRNR.FontBold = False
    '#######################################

    Exit_cmdTOT_Click:
    Exit Sub

    Err_cmdTOT_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_cmdTOT_Click

    End Sub
    ==============================================
    ==============================================

    The purpose of each button is to display different aspects of the same data (i.e. Button (1) shows budgets, costs, schedules for training devices; Button (2) shows the same information for spare or provisional parts for the same trainers; and Button (3) shows all information. The purpose of the changing colors and intensity is to alert the user which button was selected.

    Hope this helps. Again thanks for your time and assistance.

  12. #12
    Join Date
    Mar 2003
    Posts
    46
    OK - first off rewrite you strsql statement to what Herman advised

    "CHECK_TN='" & "P" & "'" (substituting your own criteria for each button)

    In your second button the line of code

    On Error GoTo Err_cmdTRNR_Click

    should read

    On Error GoTo Err_cmdPRV_Click


    Otherwise not quite sure why it's not working, providing you're wanting to display the same data items for either Check_TN=P or Check_TN=S.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •