Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    Keefay78 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    36
    I have another database which is populated with a few names and jobs, also has dates, times worked etc.. I dont want to mess with it and end up destroying it so I have made a much simpler one to get the idea and the steps to have a data filtering form.



    I will have a look at that site now


    Quote Originally Posted by June7 View Post
    Have you seen this one: http://allenbrowne.com/tips.html

    I see you have UNBOUND combobox for staff but not procedure. I don't use macros, only VBA. The above link demonstrates VBA that builds filter criteria and applies to form Filter property.

    Not seeing a report and code to open filtered report.

  2. #17
    Keefay78 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    36
    i tried some of the coding on that site, I just cannot make it work, im missing something, probably very obvious LOL

  3. #18
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you download sample db to learn from?

    Can't advise without knowing what you attempted. Provide your db with attempted code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #19
    Keefay78 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    36
    I did, i tried to copy from the code they had within the db and repeat it on mine.

    to copy the example i tried to use a text box as my search, if i get it working I would like to use a combo with staff names but in trying to learn it I used what I seen.

    If Not IsNull(Me.txtFilterCity) Then
    strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) AND "
    End If

    in my db the text box was called txtfilterstaff and the field was StaffName so i changed it to

    Private Sub Command20_Click()
    If Not IsNull(Me.txtfilterstaff) Then
    strWhere = strWhere & "([staffName] = """ & Me.txtfilterstaff & """)"
    End If
    End Sub

    with command20 being the button on the header next to it
    Attached Files Attached Files

  5. #20
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You did not complete the code. Did you review the entire example?

    Code:
    Private Sub Command20_Click()
    If Not IsNull(Me.txtfilterstaff) Then
        strWhere = strWhere & "([staffName] = """ & Me.txtfilterstaff & """)"
    End If
    Me.Filter = strWhere
    Me.FilterOn = True
    End Sub
    The tutorial shows use of combobox as well as textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #21
    Keefay78 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    36
    I did honest lol, i just tried to gleen as much of what I thought was right to copy over.

    Now that I have your code to compare I will look at the combobox part and adjust it accordingly.

    I cannot thank you enough though for helping me, I was at my wits end.


    Quote Originally Posted by June7 View Post
    You did not complete the code. Did you review the entire example?

    Code:
    Private Sub Command20_Click()
    If Not IsNull(Me.txtfilterstaff) Then
        strWhere = strWhere & "([staffName] = """ & Me.txtfilterstaff & """)"
    End If
    Me.Filter = strWhere
    Me.FilterOn = True
    End Sub
    The tutorial shows use of combobox as well as textbox.

  7. #22
    Keefay78 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    36
    see, the text box works, i looked at the part of the code about comboboxes,

    If Me.cboFilterIsCorporate = -1 Then
    strWhere = strWhere & "([IsCorporate] = True) AND "
    ElseIf Me.cboFilterIsCorporate = 0 Then
    strWhere = strWhere & "([IsCorporate] = False) AND "
    End If

    changed it to suit my db

    If Me.cboproc = -1 Then
    strWhere = strWhere & "([procedureName] = True) AND "
    ElseIf Me.cboproc = 0 Then
    strWhere = strWhere & "([procedureName] = False) AND "
    End If

    then added this into the code for the filter button

    Private Sub Command20_Click()
    If Not IsNull(Me.txtfilterstaff) Then
    strWhere = strWhere & "([staffName] = """ & Me.txtfilterstaff & """)"
    End If


    If Me.cboproc = -1 Then
    strWhere = strWhere & "([procedureName] = True) AND "
    ElseIf Me.cboproc = 0 Then
    strWhere = strWhere & "([procedureName] = False) AND "
    End If
    Me.Filter = strWhere
    Me.FilterOn = True


    End Sub


    text box still searches fine but combo does nothing
    im not great with the code(obviously lol) but it looks right?

  8. #23
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The combobox you looked at in example has only Yes/No (True/False) options - look at the other combobox. Your combobox is selecting a procedure - not True or False. Code should be same as for textbox.

    If Not IsNull(Me.cboProc) Then

    You also need block of code at end of procedure:

    Code:
    '*********************************************************************** 'Chop off the trailing " AND ", and use the string as the form's Filter. '*********************************************************************** 'See if the string has more than 5 characters (a trailng " AND ") to remove. lngLen = Len(strWhere) - 5 If lngLen <= 0 Then 'Nah: there was nothing in the string. MsgBox "No criteria", vbInformation, "Nothing to do." Else 'Yep: there is something there, so remove the " AND " at the end. strWhere = Left$(strWhere, lngLen) 'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G). 'Debug.Print strWhere 'Finally, apply the string as the form's Filter. Me.Filter = strWhere Me.FilterOn = True End If

    An alternative to setting filter is to 'go to' desired record. One way to do that:

    DoCmd.SearchForRecord , , acFirst, strWhere
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #24
    Keefay78 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    36
    I tried to simulate the code for the line you posted for the combobox into my code, i get the followiung error

    Click image for larger version. 

Name:	cap12w.JPG 
Views:	12 
Size:	21.5 KB 
ID:	39151

    when I debug it i get this:

    Click image for larger version. 

Name:	123d.JPG 
Views:	12 
Size:	30.7 KB 
ID:	39152


  10. #25
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Suggest you look at the example code again.

    You have omitted the " AND " operator in concatenation.

    Copy/paste Allen's procedures and just change field names and control references. Remove ones not needed. Leave everything else.

    And look at my previous post again. I may have edited after you read it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #26
    Keefay78 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    36
    I tried, thought I had it there, staff name text box works fine but as soon as I enter a selection into procedure combo and hit search it throws up an error and debugging gives me this.

    Click image for larger version. 

Name:	aaaccc.JPG 
Views:	11 
Size:	34.8 KB 
ID:	39153

    I tried adding the AND

    I added the code at the end but still throwing up an error

  12. #27
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't nest the If Then blocks. I prefer apostrophe instead of doubling quote marks, easier to read, but either should work. With Allen's code, have to include " AND " even on the last criteria.
    Code:
    If Not IsNull(Me.txtfilterstaff) Then strWhere = strWhere & "([staffName] = '" & Me.txtfilterstaff & "') AND " End If If Not IsNull(Me.cboProc) Then strWhere = strWhere & "([procedureName] = '" & Me.cboProc & "') AND " End If
    However, you appear to be pulling procedure ID not name from combobox. This will cause filter to fail. Really better to do search and filter on ID values instead of text. The text delimiters (apostrophe or doubled quotes) would not be used with number fields.

    Please post code as text between CODE tags instead of images so we can copy/paste your code. Consistently structured indentation makes code easier to read.
    Last edited by June7; 07-22-2019 at 12:38 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #28
    Keefay78 is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2019
    Posts
    36
    Thanks, I think I may have messed up the combobox when I made it, I made another using only name and reworded it in the code using the parts you gave me:

    Private Sub Command20_Click()
    If Not IsNull(Me.txtfilterstaff) Then
    strWhere = strWhere & "([staffName] = '" & Me.txtfilterstaff & "') AND "
    End If


    If Not IsNull(Me.cboproc2) Then
    strWhere = strWhere & "([procedureName] = '" & Me.cboproc2 & "') AND "
    End If


    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
    strWhere = Left$(strWhere, lngLen)
    End If
    Me.Filter = strWhere
    Me.FilterOn = True
    End Sub

    and VOILA it works like a dream.

    I cannot thank you enough June7, I was close to calling it a bust with this project

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-03-2015, 11:19 PM
  2. Split Form that Filters
    By QuantifyRisk in forum Forms
    Replies: 12
    Last Post: 11-20-2014, 01:09 PM
  3. Replies: 12
    Last Post: 02-25-2013, 08:38 AM
  4. Filters in one form affecting another...
    By Dominaz in forum Access
    Replies: 1
    Last Post: 10-25-2011, 04:55 PM
  5. Form Filters Help
    By JeffG3209 in forum Forms
    Replies: 1
    Last Post: 05-22-2011, 10:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums