+ Reply to Thread
Results 1 to 8 of 8

Add,Search,Edit,Delete via Useform

Hybrid View

puuts Add,Search,Edit,Delete via... 04-07-2013, 07:52 AM
abousetta Re: Add,Search,Edit,Delete... 04-07-2013, 08:16 AM
Norie Why 4 different forms? All... 04-07-2013, 08:17 AM
puuts Re: Add,Search,Edit,Delete... 04-08-2013, 07:55 AM
abousetta Re: Add,Search,Edit,Delete... 04-08-2013, 08:26 AM
Norie Re: Add,Search,Edit,Delete... 04-08-2013, 08:34 AM
puuts Re: Add,Search,Edit,Delete... 04-08-2013, 09:43 AM
Norie You don't need that line of... 04-08-2013, 11:05 AM
  1. #1
    Forum Contributor
    Join Date
    02-26-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    367

    Add,Search,Edit,Delete via Useform

    Hello, could you please help me with this data entry form?

    I just want:

    1. If user click "Add New PC" button, it will go to frm_add and fill up the form and once user click the "Save" Button it will save in "FTE Sheet"
    2. If user click "Delete PC" button, it will go to frm_delete and search the pc name via combobox, and once user click the "Delete" button, it will delete the data and clear the textboxes.
    3. If user click "Edit PC" button, it will go to frm_edit and search the pc name to edit via combobox, and once user click the "Update" button, it will update/overwrite the data and msgbox will pop-up "Update Successful".
    4. If user click "Search PC's" button, it will go to frm_search, and it will display the data depending on the list of the combobox picked by the user.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Add,Search,Edit,Delete via Useform

    RoyUK has a great example that does most of what you want on his website (www.excel-it.com/vba_examples.htm). I am attaching a copy of the database form example.

    abousetta
    Attached Files Attached Files
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645
    Why 4 different forms?

    All you describe could be done on one form.
    If posting code please use code tags, see here.

  4. #4
    Forum Contributor
    Join Date
    02-26-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    367

    Re: Add,Search,Edit,Delete via Useform

    Hi abousetta thanks for that.

    Just need some changes here, how do i permanently assign sheet1 as the database?

    when i try to add a sheet and run the macro, it gives me an error on this line:

     Set rSearch = Sheet1.Range("a6", Range("a65536").End(xlUp))

  5. #5
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Add,Search,Edit,Delete via Useform

    You are really close.

    It should be

    Sheets(1)
    Also as coding styles have changed, we no longer use the 65K limit set by Excel 2003.

    Try this:

     Set rSearch = Sheets(1).Range("A6", Range("A" & Rows.count).End(xlUp))
    Hope this helps.

    abousetta

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Add,Search,Edit,Delete via Useform

    The problem could be that you haven't fully referenced everything.

    Try this.
    Set rSearch = Sheet1.Range("A6", Sheet1.Range("A" & Rows.Count).End(xlUp))

  7. #7
    Forum Contributor
    Join Date
    02-26-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    367

    Re: Add,Search,Edit,Delete via Useform

    Hi norie, i've tried your code and now it gives this error line:

    it debugs in "c.Select"

    
    Private Sub cmbFind_Click()
        Dim strFind, FirstAddress As String   'what to find
        Dim rSearch As Range  'range to search
        Set rSearch = Sheet1.Range("A6", Sheet1.Range("A" & Rows.Count).End(xlUp))
        strFind = Me.TextBox1.Value    'what to look for
        Dim f      As Integer
        With rSearch
            Set c = .Find(strFind, LookIn:=xlValues)
            If Not c Is Nothing Then    'found it
                c.Select
                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
                    .cmbAmend.Enabled = True     'allow amendment or
                    .cmbDelete.Enabled = True    'allow record deletion
                    .cmbAdd.Enabled = False      'don't want to duplicate record
                    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
                    MsgBox "There are " & f & " instances of " & strFind
                    Me.Height = frmMax
                End If
            Else: MsgBox strFind & " not listed"    'search failed
            End If
        End With
    End Sub

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645
    You don't need that line of code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1