Using a userform to update records

[groovedude]

Hi everyone,

Right I am rather new to VBA so I am currently in the process of educating myself and I am hoping you guys can steer me in the right direction.

I have built a data entry form in VBA however I would like to add some additional functions, I would like to have the ability to navigate through existing records and to amend them.

In ideal world I would like to navigate them based on owner, for example you select an owner then you can browse all records where they exist.

Private Sub ComboAssociated_Enter()
'Populate control.
    Me.ComboAssociated.AddItem "Project 1"
    Me.ComboAssociated.AddItem "Project 2"
    Me.ComboAssociated.AddItem "Project 3"
    Me.ComboAssociated.AddItem "Project 4"
End Sub
Private Sub ComboChangeType_Enter()
'Populate control.
    Me.ComboChangeType.AddItem "Change 1"
    Me.ComboChangeType.AddItem "Change 2"
    Me.ComboChangeType.AddItem "Change 3"
    Me.ComboChangeType.AddItem "Change 4"
End Sub
Private Sub ComboOwner_Enter()
'Populate control.
    Me.ComboOwner.AddItem "User 1"
    Me.ComboOwner.AddItem "User 2"
    Me.ComboOwner.AddItem "User 3"
    Me.ComboOwner.AddItem "User 4"
    Me.ComboOwner.AddItem "User 5"
End Sub
Private Sub cmdAdd_Enter()
    'Copy input values to sheet.
    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Raw")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.ComboChangeType.Value
        .Cells(lRow, 2).Value = Me.ComboAssociated.Value
        .Cells(lRow, 3).Value = Me.txtTitleofChange.Value
        .Cells(lRow, 4).Value = Me.ComboOwner.Value
        .Cells(lRow, 5).Value = Me.TextStart.Value
        .Cells(lRow, 6).Value = Me.TextFinish.Value
    End With
    
    'Clear input controls.
    Me.ComboChangeType.Value = ""
    Me.ComboAssociated.Value = ""
    Me.txtTitleofChange.Value = ""
    Me.ComboOwner.Value = ""
    Me.TextStart.Value = ""
    Me.TextFinish.Value = ""
End Sub
Private Sub cmdClose_Click()
    'Close UserForm.
    Unload Me
End Sub

 

[dmt32]

Hi,
I have updated some code that I assisted another on the board with a similar requirement to yours to take into account your control names & values but none of this has been tested.

You will need five commandbuttons on you userform named as follows:

· cmdAdd
· cmdNew
· cmdClose
· NextRecord
· PrevRecord

Don’t worry about applying captions – code will do this.

Make a backup of your workbook & then Replace all code in your forms code page with following:

Option Base 1
Dim ws As Worksheet
Dim r As Long, LastRow As Long
Dim IsNewRecord As Boolean
Const StartRow As Long = 2






'***************************************************************************************
'*********************************New Record Button*************************************
Private Sub cmdNew_Click()
    IsNewRecord = CBool(Me.cmdNew.Caption = Me.cmdNew.Tag)
    ResetButtons IsNewRecord
    Navigate Direction:=IIf(IsNewRecord, xlNone, xlRowItem)
End Sub




'***************************************************************************************
'*********************************Add Record Button*************************************
Private Sub cmdAdd_Click()
    Dim i As Integer
    Dim msg As String
    
    If IsNewRecord Then
        LastRow = LastRow + 1
        r = LastRow
        ResetButtons False
        msg = "New Record Added"
    Else
        msg = "Record Updated"
    End If
    
'Add / update record
    For i = 1 To UBound(ControlNames)
    With Me.Controls(ControlNames(i))
        If IsDate(.Text) Then
            ws.Cells(r, i).Value = DateValue(.Text)
        Else
            ws.Cells(r, i).Value = .Text
        End If
    End With
    Next i
    
'tell user what happened
    MsgBox msg, 48, msg
'clear flag
    IsNewRecord = False
End Sub


'***************************************************************************************
'*********************************Navigate Buttons & Settings***************************
Sub NavigationButtonsEnable(Optional ByVal Enable As Boolean)
    'set enabled status of next previous buttons
    Me.NextRecord.Enabled = IIf(Enable, False, r < LastRow)
    Me.PrevRecord.Enabled = IIf(Enable, False, r > StartRow)
End Sub


Private Sub NextRecord_Click()
    Navigate Direction:=xlNext
End Sub


Private Sub PrevRecord_Click()
    Navigate Direction:=xlPrevious
End Sub
'***************************************************************************************


Private Sub cmdClose_Click()
    'Close UserForm.
    Unload Me
End Sub


'***************************************************************************************
'**************************NewRecord & UpdateRecord Button Settings***********************
Sub ResetButtons(ByVal xlNew As Boolean)
'configure new button
    With Me.cmdNew
        .Caption = IIf(xlNew, "Cancel", .Tag)
        .BackColor = IIf(xlNew, &HFF&, &H8000000F)
        .ForeColor = IIf(xlNew, &HFFFFFF, &H0&)
        .Font.Bold = xlNew
    End With
'configure add button
    With Me.cmdAdd
        .Caption = IIf(xlNew, "Add New Record", .Tag)
        .WordWrap = xlNew
        If .Height < 30 Then .Height = 30
        .BackColor = IIf(xlNew, &HFF00&, &H8000000F)
        .ForeColor = IIf(xlNew, &HFFFFFF, &H0&)
        .Font.Bold = xlNew
    End With
'set focus to required control
    Me.Controls(ControlNames(1)).SetFocus
    NavigationButtonsEnable
End Sub


Sub Navigate(ByVal Direction As XlSearchDirection)
    Dim i As Integer
    
    Select Case Direction
    Case xlFirst
        r = StartRow
    Case xlPrevious
        r = r - 1
    Case xlNext
        r = r + xlNext
    Case xlLastCell
        r = LastRow
    Case xlRowItem
        r = r
    Case xlNone
        ClearForm = True
    End Select
    
'get last used row
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
'ensure value of r stays within data range
    If r < StartRow Then r = StartRow
    If r > LastRow Then r = LastRow
    
    
'get record
    For i = 1 To UBound(ControlNames)
         Me.Controls(ControlNames(i)).Text = IIf(ClearForm, "", ws.Cells(r, i).Text)
    Next i
    
    NavigationButtonsEnable ClearForm
    
End Sub

Private Sub UserForm_Initialize()
Set ws = ThisWorkbook.Worksheets("Raw")

'Populate controls
Me.ComboAssociated.List = Array("Project 1", "Project 2", "Project 3", "Project 4")
Me.ComboChangeType.List = Array("Change 1", "Change 2", "Change 3", "Change 4")
Me.ComboOwner.List = Array("User 1", "User 2", "User 3", "User 4", "User 5")

With Me.cmdAdd
.Caption = "Update"
.Tag = .Caption
End With

With Me.cmdNew
.Caption = "New"
.Tag = .Caption
End With

Me.PrevRecord.Caption = "<previous"
Me.NextRecord.Caption = "Next>"
Me.cmdClose.Caption = "Close"


'start at first record
Navigate Direction:=xlFirst
End Sub


Function ControlNames()
ControlNames = Array("ComboChangeType", "ComboAssociated", "txtTitleofChange", _
"ComboOwner", "TextStart", "TextFinish")
End Function
<previous"



NOTE the variables at TOP outside of any procedure - These MUST be placed at TOP of your forms code page unchanged.

If all works, you should be able to navigate the records using navigation buttons & Add new record or amend existing record.
Hopefully, this will give you something to work with and you can adjust as required to meet specific project need.

Dave

edit - sorry having problems with code tags!</previous"
</previous"