Omagoodness
New member
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:
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