Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-10-2014, 12:11 PM
johndough johndough is offline UserForm Search with multiple TextBoxes Windows 7 64bit UserForm Search with multiple TextBoxes Office 2010 32bit
Novice
UserForm Search with multiple TextBoxes
 
Join Date: May 2014
Posts: 2
johndough is on a distinguished road
Default UserForm Search with multiple TextBoxes

Ok so I am still trying to allow the userForm to allow the user to search using 1 of 3 textboxes. I will only be entering data into 1 not all 3 but I want to choose which one of the 3. Here is some code. It doesn't work, all it does is popup the msg box. This is a reference maybe it will help you understand what I am looking for. As you can tell I am new to this lol.




Code:
Private Sub cmbFind_Click()
    Dim strFind As String    'what to find
    Dim strFind1 As String
    Dim strFind2 As String
    Dim FirstAddress As String
    Dim f As Integer
    Dim g As Integer
    Dim h As Integer
    
  
     strFind = Me.TextBox1.Value    'what to look for
    
   
     With MyData
    .AutoFilter
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
             With Me    'load entry to form
                .TextBox2.Value = c.Offset(0, 1).Value
                .TextBox3.Value = c.Offset(0, 2).Value
                .TextBox4.Value = c.Offset(0, 3).Value
                .TextBox5.Value = c.Offset(0, 4).Value
                .TextBox6.Value = c.Offset(0, 5).Value
                .TextBox7.Value = c.Offset(0, 6).Value
                .TextBox8.Value = c.Offset(0, 8).Value
                .cmbAmend.Enabled = True     'allow amendment or
                .cmbDelete.Enabled = True    'allow record deletion
                .cmbAdd.Enabled = False      'don't want to duplicate record
                If c.Offset(0, 7).Value = "Yes" Then .optYes = True
                If c.Offset(0, 7).Value = "No" Then .optYes = True
                r = c.Row
                f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
                 Case vbOK
                    FindAll
                Case vbCancel
                    'do nothing
                End Select
                Me.Height = frmMax
             End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
    
        strFind1 = Me.TextBox2.Value
        With MyData
    .AutoFilter
        Set d = .Find(strFind1, LookIn:=xlValues)
        If Not d Is Nothing Then
             With Me    'load entry to form
                .TextBox2.Value = d.Offset(0, 1).Value
                .TextBox3.Value = d.Offset(0, 2).Value
                .TextBox4.Value = d.Offset(0, 3).Value
                .TextBox5.Value = d.Offset(0, 4).Value
                .TextBox6.Value = d.Offset(0, 5).Value
                .TextBox7.Value = d.Offset(0, 6).Value
                .TextBox8.Value = d.Offset(0, 8).Value
                .cmbAmend.Enabled = True     'allow amendment or
                .cmbDelete.Enabled = True    'allow record deletion
                .cmbAdd.Enabled = False      'don't want to duplicate record
                If d.Offset(0, 7).Value = "Yes" Then .optYes = True
                If d.Offset(0, 7).Value = "No" Then .optYes = True
                r = d.Row
                g = 0
            End With
            FirstAddress = d.Address
            Do
                g = g + 1    'count number of matching records
                Set d = .FindNext(d)
            Loop While Not d Is Nothing And d.Address <> FirstAddress
            If g > 1 Then
                Select Case MsgBox("There are " & g & " instances of " & strFind1, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
                 Case vbOK
                    FindAll
                Case vbCancel
                    'do nothing
                End Select
                Me.Height = frmMax
             End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
    
     strFind2 = Me.TextBox6.Value
        With MyData
    .AutoFilter
        Set e = .Find(strFind2, LookIn:=xlValues)
        If Not e Is Nothing Then
             With Me    'load entry to form
                .TextBox2.Value = e.Offset(0, 1).Value
                .TextBox3.Value = e.Offset(0, 2).Value
                .TextBox4.Value = e.Offset(0, 3).Value
                .TextBox5.Value = e.Offset(0, 4).Value
                .TextBox6.Value = e.Offset(0, 5).Value
                .TextBox7.Value = e.Offset(0, 6).Value
                .TextBox8.Value = e.Offset(0, 8).Value
                .cmbAmend.Enabled = True     'allow amendment or
                .cmbDelete.Enabled = True    'allow record deletion
                .cmbAdd.Enabled = False      'don't want to duplicate record
                If e.Offset(0, 7).Value = "Yes" Then .optYes = True
                If e.Offset(0, 7).Value = "No" Then .optYes = True
                r = e.Row
                h = 0
            End With
            FirstAddress = e.Address
            Do
                h = h + 1    'count number of matching records
                Set e = .FindNext(e)
            Loop While Not e Is Nothing And e.Address <> FirstAddress
            If h > 1 Then
                Select Case MsgBox("There are " & h & " instances of " & strFind2, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")
                 Case vbOK
                    FindAll
                Case vbCancel
                    'do nothing
                End Select
                Me.Height = frmMax
             End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
End Sub
Attached Files
File Type: xlsm Copy of DataBaseForm.xlsm (85.3 KB, 28 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
search on multiple word documents Guy Roth Word 7 03-06-2017 01:31 PM
UserForm Search with multiple TextBoxes UserForm Search, delete issues johndough Excel Programming 3 05-11-2014 11:44 AM
Multiple words, one search return2300 Word VBA 0 08-30-2013 12:26 PM
Excel - search Multiple Values (HELP!) duskdjl Excel 4 03-28-2013 01:11 AM
UserForm Search with multiple TextBoxes Search with multiple strings silverspr Excel 7 03-03-2013 04:45 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:39 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft