Hi,
I have a userform that can Add, Find Next, Find Previous and all work ok. I have added an update button as each record will have to be updated over time. The problem is, when I click update it just creates a new record. How do I fix that?
Hi,
I have a userform that can Add, Find Next, Find Previous and all work ok. I have added an update button as each record will have to be updated over time. The problem is, when I click update it just creates a new record. How do I fix that?
Re: Use VBA user form to find and update a record
Very difficult to answer without seeing your existing code.
Re: Use VBA user form to find and update a record
Hi,
Here is the file.......
Option Explicit
Dim currentrow As Long
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub cdFindN_Click()
Dim lastrow
Dim BkRef As String
lastrow = Sheets("Park2Travel").Range("A" & Rows.Count).End(xlUp).Row
BkRef = txtConRef.Text
For currentrow = 5 To lastrow
If Cells(currentrow, 1).Text = BkRef Then
txtName.Text = Cells(currentrow, "E")
ArrDate.Text = Cells(currentrow, "C")
ArrTime.Text = Cells(currentrow, "D")
RetDate.Text = Cells(currentrow, "J")
RetTime.Text = Cells(currentrow, "K")
txtConRef.Text = Cells(currentrow, "A")
txtRef.Text = Cells(currentrow, "B")
txtRegNo.Text = Cells(currentrow, "F")
txtMake.Text = Cells(currentrow, "G")
txtMod.Text = Cells(currentrow, "H")
txtCol.Text = Cells(currentrow, "I")
txtMilein.Text = Cells(currentrow, "P")
txtMileO.Text = Cells(currentrow, "Q")
txtFuel.Text = Cells(currentrow, "O")
txtStor.Text = Cells(currentrow, "M")
txtRetBay.Text = Cells(currentrow, "N")
ListBox1.Text = Cells(currentrow, "L")
txtComp.Text = Cells(currentrow, "T")
txtCost.Text = Cells(currentrow, "S")
End If
Next currentrow
txtConRef.SetFocus
End Sub
Private Sub cdFindP_Click()
Dim lastrow
Dim BkRef As String
lastrow = Sheets("Park2Travel").Range("A" & Rows.Count).End(xlUp).Row
BkRef = txtConRef.Text
For currentrow = lastrow To 5 Step -1
If Cells(currentrow, 1).Text = BkRef Then
txtName.Text = Cells(currentrow, "E")
ArrDate.Text = Cells(currentrow, "C")
ArrTime.Text = Cells(currentrow, "D")
RetDate.Text = Cells(currentrow, "J")
RetTime.Text = Cells(currentrow, "K")
txtConRef.Text = Cells(currentrow, "A")
txtRef.Text = Cells(currentrow, "B")
txtRegNo.Text = Cells(currentrow, "F")
txtMake.Text = Cells(currentrow, "G")
txtMod.Text = Cells(currentrow, "H")
txtCol.Text = Cells(currentrow, "I")
txtMilein.Text = Cells(currentrow, "P")
txtMileO.Text = Cells(currentrow, "Q")
txtFuel.Text = Cells(currentrow, "O")
txtStor.Text = Cells(currentrow, "M")
txtRetBay.Text = Cells(currentrow, "N")
ListBox1.Text = Cells(currentrow, "L")
txtComp.Text = Cells(currentrow, "T")
txtCost.Text = Cells(currentrow, "S")
End If
Next currentrow
txtConRef.SetFocus
End Sub
Private Sub cdUpdate_Click()
Dim name As String, ArrDat As String, ArrTim As String, RetDat As String, RetTim As String, BkRef As String, Ref As String, RegNo As String, Make As String, Model As String, Col As String, Milein As String, mileO As String, Fuel As String, Stor As String, RetBay As String, BkCon As String, Listbox As String, Compl As String, Cost As String
Range("A" & currentrow) = Me.txtConRef.Value
name = txtName.Text
Cells(currentrow, 5).Value = name
ArrDat = ArrDate.Text
Cells(currentrow, 3).Value = ArrDate
ArrTim = ArrTime.Text
Cells(currentrow, 4).Value = ArrTime
RetDat = RetDate.Text
Cells(currentrow, 10).Value = RetDate
RetTim = RetTime.Text
Cells(currentrow, 11).Value = RetTime
BkRef = txtConRef.Text
Cells(currentrow, 1).Value = BkRef
Ref = txtRef.Text
Cells(currentrow, 2).Value = Ref
RegNo = txtRegNo.Text
Cells(currentrow, 6).Value = RegNo
Make = txtMake.Text
Cells(currentrow, 7).Value = Make
Model = txtMod.Text
Cells(currentrow, "H").Value = Model
Col = txtCol.Text
Cells(currentrow, "I").Value = Col
Milein = txtMilein.Text
Cells(currentrow, "P").Value = Milein
mileO = txtMileO.Text
Cells(currentrow, "Q").Value = mileO
Fuel = txtFuel.Text
Cells(currentrow, "O").Value = Fuel
Stor = txtStor.Text
Cells(currentrow, "M").Value = Stor
RetBay = txtRetBay.Text
Cells(currentrow, "N").Value = RetBay
BkCon = txtBkCon.Text
Cells(currentrow, "R").Value = BkCon
Listbox = ListBox1.Text
Cells(currentrow, "L").Value = Listbox
Compl = txtComp.Text
Cells(currentrow, "T").Value = Compl
Cost = txtCost.Text
Cells(currentrow, "S").Value = Cost
End Sub
Private Sub ClearButton_Click()
Call UserForm_Initialize
End Sub
Display More
Re: Use VBA user form to find and update a record
The board likes code to be wrapped in 'Code Tags'. These indent and colour the code making it easier to read. Please edit your post, select the code and click the # button.
Taking the code as it stands, the simplest thing to do would be to add a new button to add a new record. The code under that button would simply get the row number of the first empty row (you didn't seem to include that), clear the controls ready for input. The existing Update button would then work correctly but you might want to change the caaption to Save', for example.
Re: Use VBA user form to find and update a record
Can I attach the file for you to look at? Really appreciate your help
Re: Use VBA user form to find and update a record
Sorry, no time to look at a file. Perhaps someone else might be able to help.
Re: Use VBA user form to find and update a record
Ok thanks anyway
Re: Use VBA user form to find and update a record
Anyone else willing to help? Pleeeeease
Re: Use VBA user form to find and update a record
You have scoped the variable CurrentRow to the userform so that will keep its last assigned value while the userform is loaded. The absolute simplest way to do this is to exit a Find procedure as soon as the item is found and the controls have been updated.
Private Sub cdFindN_Click()
Dim lastrow
Dim BkRef As String
lastrow = Sheets("Park2Travel").Range("A" & Rows.Count).End(xlUp).Row
BkRef = txtConRef.Text
For currentrow = 5 To lastrow
If Cells(currentrow, 1).Text = BkRef Then
txtName.Text = Cells(currentrow, "E")
ArrDate.Text = Cells(currentrow, "C")
ArrTime.Text = Cells(currentrow, "D")
RetDate.Text = Cells(currentrow, "J")
RetTime.Text = Cells(currentrow, "K")
txtConRef.Text = Cells(currentrow, "A")
txtRef.Text = Cells(currentrow, "B")
txtRegNo.Text = Cells(currentrow, "F")
txtMake.Text = Cells(currentrow, "G")
txtMod.Text = Cells(currentrow, "H")
txtCol.Text = Cells(currentrow, "I")
txtMilein.Text = Cells(currentrow, "P")
txtMileO.Text = Cells(currentrow, "Q")
txtFuel.Text = Cells(currentrow, "O")
txtStor.Text = Cells(currentrow, "M")
txtRetBay.Text = Cells(currentrow, "N")
ListBox1.Text = Cells(currentrow, "L")
txtComp.Text = Cells(currentrow, "T")
txtCost.Text = Cells(currentrow, "S")
' this was moved up - only set focus if info found
txtConRef.SetFocus
Exit Sub
End If
Next currentrow
End Sub
Display More
Your Update procedure will then use that Row reference for the update.
Haven't delved into it too deeply, but you need to edit any other Find procedures and verify any place CurrentRow is modified does not invalidate any following Update.
Re: Use VBA user form to find and update a record
Hi niguk
With your userform code...
Option Explicit
Dim CurrentRow As Long
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub cdFindN_Click()
Dim lastrow, ItemRow
Dim BkRef As String
lastrow = Sheets("Park2Travel").Range("A" & Rows.Count).End(xlUp).Row
BkRef = txtConRef.Text
For ItemRow = CurrentRow + 1 To lastrow
If Cells(ItemRow, 1).Text = BkRef Then
txtName.Text = Cells(ItemRow, "E")
ArrDate.Text = Cells(ItemRow, "C")
ArrTime.Text = Cells(ItemRow, "D")
RetDate.Text = Cells(ItemRow, "J")
RetTime.Text = Cells(ItemRow, "K")
txtConRef.Text = Cells(ItemRow, "A")
txtRef.Text = Cells(ItemRow, "B")
txtRegNo.Text = Cells(ItemRow, "F")
txtMake.Text = Cells(ItemRow, "G")
txtMod.Text = Cells(ItemRow, "H")
txtCol.Text = Cells(ItemRow, "I")
txtMilein.Text = Cells(ItemRow, "P")
txtMileO.Text = Cells(ItemRow, "Q")
txtFuel.Text = Cells(ItemRow, "O")
txtStor.Text = Cells(ItemRow, "M")
txtRetBay.Text = Cells(ItemRow, "N")
ListBox1.Text = Cells(ItemRow, "L")
txtComp.Text = Cells(ItemRow, "T")
txtCost.Text = Cells(ItemRow, "S")
CurrentRow = ItemRow
Exit Sub
End If
Next ItemRow
txtConRef.SetFocus
End Sub
Private Sub cdFindP_Click()
Dim lastrow, ItemRow
Dim BkRef As String
lastrow = Sheets("Park2Travel").Range("A" & Rows.Count).End(xlUp).Row
BkRef = txtConRef.Text
For ItemRow = CurrentRow - 1 To 5 Step -1
If Cells(ItemRow, 1).Text = BkRef Then
txtName.Text = Cells(ItemRow, "E")
ArrDate.Text = Cells(ItemRow, "C")
ArrTime.Text = Cells(ItemRow, "D")
RetDate.Text = Cells(ItemRow, "J")
RetTime.Text = Cells(ItemRow, "K")
txtConRef.Text = Cells(ItemRow, "A")
txtRef.Text = Cells(ItemRow, "B")
txtRegNo.Text = Cells(ItemRow, "F")
txtMake.Text = Cells(ItemRow, "G")
txtMod.Text = Cells(ItemRow, "H")
txtCol.Text = Cells(ItemRow, "I")
txtMilein.Text = Cells(ItemRow, "P")
txtMileO.Text = Cells(ItemRow, "Q")
txtFuel.Text = Cells(ItemRow, "O")
txtStor.Text = Cells(ItemRow, "M")
txtRetBay.Text = Cells(ItemRow, "N")
ListBox1.Text = Cells(ItemRow, "L")
txtComp.Text = Cells(ItemRow, "T")
txtCost.Text = Cells(ItemRow, "S")
CurrentRow = ItemRow
Exit Sub
End If
Next ItemRow
txtConRef.SetFocus
End Sub
Private Sub cdUpdate_Click()
Dim name As String, ArrDat As String, ArrTim As String, RetDat As String, RetTim As String, BkRef As String, Ref As String, RegNo As String, Make As String, Model As String, Col As String, Milein As String, mileO As String, Fuel As String, Stor As String, RetBay As String, BkCon As String, Listbox As String, Compl As String, Cost As String
name = txtName.Text
Cells(CurrentRow, 5).Value = name
ArrDat = ArrDate.Text
Cells(CurrentRow, 3).Value = ArrDate
ArrTim = ArrTime.Text
Cells(CurrentRow, 4).Value = ArrTime
RetDat = RetDate.Text
Cells(CurrentRow, 10).Value = RetDate
RetTim = RetTime.Text
Cells(CurrentRow, 11).Value = RetTime
BkRef = txtConRef.Text
Cells(CurrentRow, 1).Value = BkRef
Ref = txtRef.Text
Cells(CurrentRow, 2).Value = Ref
RegNo = txtRegNo.Text
Cells(CurrentRow, 6).Value = RegNo
Make = txtMake.Text
Cells(CurrentRow, 7).Value = Make
Model = txtMod.Text
Cells(CurrentRow, "H").Value = Model
Col = txtCol.Text
Cells(CurrentRow, "I").Value = Col
Milein = txtMilein.Text
Cells(CurrentRow, "P").Value = Milein
mileO = txtMileO.Text
Cells(CurrentRow, "Q").Value = mileO
Fuel = txtFuel.Text
Cells(CurrentRow, "O").Value = Fuel
Stor = txtStor.Text
Cells(CurrentRow, "M").Value = Stor
RetBay = txtRetBay.Text
Cells(CurrentRow, "N").Value = RetBay
BkCon = txtBkCon.Text
Cells(CurrentRow, "R").Value = BkCon
Listbox = ListBox1.Text
Cells(CurrentRow, "L").Value = Listbox
Compl = txtComp.Text
Cells(CurrentRow, "T").Value = Compl
Cost = txtCost.Text
Cells(CurrentRow, "S").Value = Cost
End Sub
Private Sub ClearButton_Click()
Call UserForm_Initialize
End Sub
Private Sub OKButton_Click()
If txtConRef.Text = "" Then
MsgBox "You Must Enter Apcoa Reference", vbCritical
Exit Sub
End If
Dim emptyRow As Long
'Make Sheet1 active
'ActiveSheet.Protect UserInterfaceOnly:=True
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 4
'Transfer information
Cells(emptyRow, 1).Value = txtConRef.Value
Cells(emptyRow, 2).Value = txtRef.Value
Cells(emptyRow, 3).Value = ArrDate.Value
Cells(emptyRow, 4).Value = ArrTime.Value
Cells(emptyRow, 5).Value = txtName.Value
Cells(emptyRow, 6).Value = txtRegNo.Value
Cells(emptyRow, 7).Value = txtMake.Value
Cells(emptyRow, 8).Value = txtMod.Value
Cells(emptyRow, 9).Value = txtCol.Value
Cells(emptyRow, 10).Value = RetDate.Value
Cells(emptyRow, 11).Value = RetTime.Value
Cells(emptyRow, 12).Value = ListBox1.Value
Cells(emptyRow, 13).Value = txtStor.Value
Cells(emptyRow, 14).Value = txtRetBay.Value
Cells(emptyRow, 15).Value = txtFuel.Value
Cells(emptyRow, 16).Value = txtMilein.Value
Cells(emptyRow, 17).Value = txtMileO.Value
Cells(emptyRow, 18).Value = txtBkCon.Value
Cells(emptyRow, 19).Value = txtCost.Value
Cells(emptyRow, 20).Value = txtComp.Value
End Sub
Private Sub UserForm_Initialize()
CurrentRow = 4
'Park2TravelUserForm.cmbsearch.List = Range("A5:A999999").Value
'Empty txtConRef
txtConRef.Value = ""
'Empty txtRef
txtRef.Value = ""
'empty txtName
txtName.Value = ""
'empty ArrDate
ArrDate.Value = ""
'empty ArrTime
ArrTime.Value = ""
'Empty txtRegNo
txtRegNo.Value = ""
'Empty txtMake
txtMake.Value = "tba"
'empty txtMod
txtMod.Value = "tba"
'empty txtCol
txtCol.Value = "tba"
'empty RetDate
RetDate.Value = ""
'empty RetTime
RetTime.Value = ""
'Fill ListBox1
With ListBox1
.AddItem "Indoor"
.AddItem "Outdoor"
.AddItem "Meet & Greet"
End With
'empty txtStor
txtStor.Value = "tba"
'empty txtRet
txtRetBay.Value = "tba"
'empty txtFuel
txtFuel.Value = "tba"
'empty MileIn
txtMilein.Value = "tba"
'empty txtMileO
txtMileO.Value = "tba"
'Fill BkCon
With txtBkCon
.AddItem "Holiday Extras"
.AddItem "FHR"
.AddItem "Purple"
.AddItem "APH"
.AddItem "Walk In"
End With
'empty txtCost
txtCost.Value = "tba"
'empty txtComp
txtComp.Value = "tba"
'Set Focus on NameTextBox
txtName.SetFocus
End Sub
Display More
using the find function would be better
Re: Use VBA user form to find and update a record
Not sure I understand, sorry
Re: Use VBA user form to find and update a record
What do you not understand?
Re: Use VBA user form to find and update a record
Hi
Syntax updated in the userform as the way it was it did work as you anticipated
Re: Use VBA user form to find and update a record
Genius! Works like a charm. Thank you so much. I had been staring at it for days. :):thumbcoo:
Re: Use VBA user form to find and update a record
till it would be faster to use find function ..but glad it help with your code
Don’t have an account yet? Register yourself now and be a part of our community!