Results 1 to 3 of 3
  1. #1
    BigPat is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    11

    Split form dynamic search box falls over with Attachments field

    Hi,

    I have a 1-table database and a split form accompanying it with a dynamic search box in its header. I really can't write VBA so I cribbed the dynamic search VBA from another post. It works fine until I add a new column into the table (TblAssets) for attachments. I can see why it would fail as it can't search the Attachments field, and so I'd like to edit this VBA so that it searches all fields except the field called Attachments.

    For information, the error I get when typing into the search box is Runtime 3709, The search key was not found in any record.



    Here's the code. I tried putting an If sField <> "Attachments" line in, but I clearly don't know what I'm doing. Any help gratefully received.
    Code:
    Private Sub txtFind_Change()    Dim sFind, sField, sFilter As String
        txtfind.SetFocus
        sFind = Nz(txtfind.Text, "")
        If Len(sFind) > 0 Then
            Dim iField As Integer
            sFilter = ""
            For iField = 0 To iFields - 1
                sField = rsMe.Fields(iField).Name
                If iField > 0 Then sFilter = sFilter & " Or "
                sFilter = sFilter & "[" & sField & "] Like '*" & sFind & "*'"
            Next iField
            Me.Filter = sFilter
            Me.FilterOn = True
        Else
            Me.Filter = ""
            Me.FilterOn = False
        End If
        txtfind.SetFocus
        If bFindLastKeySpace Then
            txtfind.Value = txtfind.Value & " "
            txtfind.SelStart = Len(sFind) + 2
        Else
            txtfind.SelStart = Len(sFind) + 1
        End If
        txtfind.SelLength = 0
        bFindLastKeySpace = False
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    how about

    Code:
    sField = rsMe.Fields(iField).Name
    if sfield<>"nameofattachmentfield" then
        If iField > 0 Then sFilter = sFilter & " Or "
        sFilter = sFilter & "[" & sField & "] Like '*" & sFind & "*'"
    end if

  3. #3
    BigPat is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    11
    That's fantastic, thank you so much! I'm truly grateful!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dynamic Search in Form
    By LonghronJ in forum Modules
    Replies: 3
    Last Post: 02-28-2018, 01:09 PM
  2. How to create a search box in split form?
    By Jeremy Sng in forum Modules
    Replies: 8
    Last Post: 03-15-2017, 05:22 AM
  3. Search on Split-Form
    By Ekhart in forum Programming
    Replies: 8
    Last Post: 09-07-2016, 10:55 AM
  4. Replies: 2
    Last Post: 07-06-2014, 05:37 PM
  5. Replies: 3
    Last Post: 12-04-2012, 05:22 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