Results 1 to 5 of 5
  1. #1
    AccessJunky is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    2

    Multi Search Form in Access 2010 Issues

    I have created a multi-Search Form to search through an Excel Document I've imported to Access, but after attempting 1 search when running the form, the SubForm doesn't reset back to listing all of the items in the excel document when I attempt to click the Clear or Search button.

    Here is the code I have:

    Private Sub cmdClear_Click()
    Me.WireDataFinalSubForm.Form.RecordSource = "SELECT * FROM WireDataFinal "
    Me.WireDataFinalSubForm.Requery
    txtVolts = ""
    txtTemp = ""
    txtWL = ""
    txtDiameter = ""
    txtGauge = ""
    txtOpen = ""
    txtJacket = ""
    txtCond = ""
    txtStrip = ""
    txtVolts.SetFocus


    End Sub






    Private Function BuildSearch() As Variant
    Dim varZero As Variant
    Dim strZero As String
    strZero = """"
    varZero = Null

    If Me.txtVolts > "" Then


    varZero = varZero & "[Volts] >= " & Me.txtVolts & " AND "
    End If
    If Me.txtTemp > "" Then
    varZero = varZero & "[Temp] >= " & Me.txtTemp & " AND "
    End If
    If Me.txtWL > "" Then
    varZero = varZero & "[Weight (lb/in)] like " & Me.txtWL & " AND "
    End If
    If Me.txtDiameter > "" Then
    varZero = varZero & "[Diameter (in)] like " & Me.txtDiameter & " AND "
    End If
    If Me.txtGauge > "" Then
    varZero = varZero & "[Gauge (AWG)] like " & Me.txtGauge & " AND "
    End If
    If Me.txtOpen > "" Then
    varZero = varZero & "[Open/Protected] like " & Me.txtOpen & " AND "
    End If
    If Me.txtJacket > "" Then
    varZero = varZero & "[Outter Jacket] like " & Me.txtJacket & " AND "
    End If
    If Me.txtCond > "" Then
    varZero = varZero & "[Conductor Type] like " & Me.txtCond & " AND "
    End If
    If Me.txtStrip > "" Then
    varZero = varZero & "[Stripper Tool] like " & Me.txtStrip & " AND "
    End If




    If IsNull(varZero) Then
    varZero = ""
    Else
    varZero = "WHERE " & varZero

    If Right(varZero, 5) = " AND " Then
    varZero = Left(varZero, Len(varZero) - 5)
    End If
    End If




    BuildSearch = varZero


    End Function



    Private Sub cmdClose_Click()
    DoCmd.Close

    End Sub


    Private Sub cmdSearch_Click()
    On Error GoTo errr
    Me.WireDataFinalSubForm.Form.RecordSource = "SELECT * FROM WireDataFinal " & BuildSearch
    Me.WireDataFinalSubForm.Requery

    Exit Sub
    errr:
    MsgBox Err.Description
    End Sub






    I am assuming it has something to do with my IsNull function, but I haven't been able to figure the right way to go about fixing it.


    Any advice / help would be much appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Code looks okay. Have you step debugged? Follow the code as it executes. Find where it deviates from expectation and fix.

    Alternative to setting RecordSource is to set the Filter and FilterOn properties. This is what I usually do.
    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.

  3. #3
    AccessJunky is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2013
    Posts
    2
    I think I had a corrupted file or something, I backed up my original before I edited it. After I used the original and put the code above in it worked fine. Thanks for the response appreciate the feedback.

  4. #4
    philip1101 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Aug 2019
    Posts
    5
    Hi,

    Is it possible to have a multi search items in a form? because i have 20k records on my database and im just using the passport no. of our employees to search usually in a batch.
    in excel i just do v-look up and filter them. Im just thinking is it possible to have a form in access just copy and paste the passport numbers from an excel form then paste it directly to an access form to search? please help. i will highly appreciate ur assistance guys. thank you in advance

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    Instead of hijacking a very old thread, you should start your own. It will get more attention.

    Data must be in a table, so no, cannot just paste a bunch of data into a form.

    I think the kind of 'search' you describe would be a query joining tables to get matching records.
    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.

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

Similar Threads

  1. Import from Excel to Access 2010 Issues
    By bcofie in forum Import/Export Data
    Replies: 7
    Last Post: 01-20-2014, 10:12 AM
  2. Replies: 4
    Last Post: 12-21-2012, 10:24 AM
  3. Multi-Field Search issues within Query
    By stiracerdude in forum Queries
    Replies: 3
    Last Post: 10-14-2012, 01:04 PM
  4. Creating a search box in Access 2010 form
    By d4jones in forum Forms
    Replies: 3
    Last Post: 07-18-2012, 02:53 PM
  5. Record lock issues - Access 2010
    By JTM39000 in forum Access
    Replies: 1
    Last Post: 01-11-2012, 07:58 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