Results 1 to 3 of 3

Thread: Updating Table Data With User Form

  1. #1
    Neophyte Omagoodness's Avatar
    Join Date
    Apr 2015
    Location
    NS Canada
    Posts
    3
    Articles
    0

    Updating Table Data With User Form



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a data table with demographic info for community residents. I have a user form for the user to enter new records that works perfectly.
    I have a second form that I want the user to be able to use to edit records. The record is selected from a combo box and the form is populated with the record data based on the combo box selection.

    My problem is getting the table to update with the changes made in the form. I have searched everywhere for help with this. I found sample code on this site (old post with older version of Excel - I am using version 2013) but I cannot get it working. Can anyone tell me where the problem is?

    BTW - There are 3 calculated fields on the form. The calculated fields create the data used in a pivot table.

    This is the code I have:

    Code:
    Private Sub cmdUpdate_Click()
    
    'Concatenate the sreet number and street name to auto-fill Address field
    Me.Address = (Me.StreetNo & " " & Me.Street)
    'Concatenate the name fields to auto-fill the Full Name field
    
    'Concatenation if there is a second last name
    If Not IsNull(LastName2) Then
    Me.FullName.Value = (LastName) & "," & " " & (FirstName) & " " & (FirstName2) & " " & (LastName2)
    End If
    'Concatenation if there is only one last name but two first names
    If IsNull(LastName2) And Not IsNull(FirstName2) Then
    Me.FullName.Value = (LastName) & "," & " " & (FirstName) & " " & "and" & " " & (FirstName2)
    End If
    'Concatenation if there is only one first name
    If IsNull(FirstName2) Then
    Me.FullName.Value = (LastName) & "," & " " & (FirstName)
    End If
    
        'To write edited info of form to Sheets("Data")
        Dim LastRow As Long
        Dim ABnum As Double
        Dim ABrng As Range
        Dim WriteRow As Long
    
    ' Make sure we're on the right sheet
    Sheets("Data").Select
        With ActiveSheet
    ' Get the last row used so can set up the search range
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ' Set the range to search for the AB number
            Set ABrng = .Range("A1:A" & LastRow)
    ' Get the AB number from what is selected on form
        ABnum = LastName.Value
    ' Get the row of sheet for this AB number
        WriteRow = Application.Match(ABnum, ABrng, 0)
    
    ' Make this AB number the active cell
    Cells(WriteRow, 1).Select
            With ActiveCell
            .Offset(0, 1).Value = FullName.Value
            .Offset(0, 2).Value = LastName.Value
        .Offset(0, 3).Value = LastName2.Value
        .Offset(0, 4).Value = FirstName.Value
        .Offset(0, 5).Value = FirstName2.Value
        .Offset(0, 6).Value = StreetNo.Value
        .Offset(0, 7).Value = Street.Value
        .Offset(0, 8).Value = Phone.Value
        .Offset(0, 9).Value = CellPhone.Value
        .Offset(0, 10).Value = Email.Value
        .Offset(0, 11).Value = AltEmail.Value
        .Offset(0, 12).Value = LotNo.Value
        .Offset(0, 13).Value = Notes.Value
        .Offset(0, 14).Value = (FullName) & Chr(10) & _
    (StreetNo) & " " & (Street) & Chr(10) & _
    (Phone) & Chr(10) & _
    (Email) & Chr(10) & _
    (AltEmail)
    
    End With
    Application.ScreenUpdating = True
    
        Cells(1, 1).Select
        End With
        
    'Refresh the pivot table to reflect dtaa changes
    
    Sheets("Directory").Select
    ActiveSheet.PivotTables("TableData").RefreshTable
        
        
        Unload Me
    
    
    
    End Sub
    The excitement of learning separates youth from old age. As long as you're learning you're not old. (~ Rosalyn S. Yalow ~ )

  2. #2
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi Omagoodness

    Welcome to the Forum!!!

    When you say it's not working, are you getting an error message, is the Data posting to the wrong Line, what's happening (or not happening)?

    A sample of your File, with Code, will be helpful.
    John

  3. #3
    Neophyte Omagoodness's Avatar
    Join Date
    Apr 2015
    Location
    NS Canada
    Posts
    3
    Articles
    0
    Thank you for responding. I found some help with an online tutorial and my issues have been resolved - finally!!!! This is the code that worked for me:
    Private Sub cmdEdit_Click()
    'declare the variables
    Dim findvalue As Range
    'error handling
    On Error GoTo errHandler:
    'check for values
    If Reg1.Value = "" Or Reg3.Value = "" Then
    MsgBox "There is not data to edit"
    Exit Sub
    End If
    'edit the row
    Set findvalue = Sheet2.Range("H:H").Find(What:=Reg6, LookIn:=xlValues).Offset(0, -5)

    'Concatenate the full name field
    'get the full name
    If Me.Reg2.Value <> "if applicable" Then
    Reg5 = (Reg1 & "," & " " & Reg3 & " " & "and" & " " & Reg4 & " " & Reg2)
    End If
    If (Me.Reg2.Value = "if applicable") And (Me.Reg4.Value = "if applicable") Then
    Me.Reg5.Value = Reg1 & "," & " " & Me.Reg3
    End If
    If (Me.Reg2.Value = "if applicable") And (Me.Reg4.Value <> "if applicable") Then
    Me.Reg5.Value = (Me.Reg1 & "," & " " & Reg3 & " " & "and" & " " & Me.Reg4)
    End If

    'Concatenate the fields for the Directory Listing

    Me.Reg17.Value = Me.Reg5 & Chr(10) & Me.Reg8 & " " & Me.Reg9 & Chr(10) & Me.Reg10 & Chr(10) & Me.Reg12 & Chr(10) & Me.Reg13

    For X = 1 To cNum
    findvalue = Me.Controls("Reg" & X).Value
    Set findvalue = findvalue.Offset(0, 1)
    Next
    MsgBox "Changes Saved", vbOKOnly, "The Crossing"
    'refresh the listbox
    Lookup
    'error block
    On Error GoTo 0
    Exit Sub
    errHandler:
    MsgBox "An Error has Occurred " & vbCrLf & _
    "The error number is: " & Err.Number & vbCrLf & _
    Err.Description & vbCrLf & "Please notify the administrator"

    End Sub
    The excitement of learning separates youth from old age. As long as you're learning you're not old. (~ Rosalyn S. Yalow ~ )

Posting Permissions

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