VBA Code to Populate Userform with Existing Records, Edit them, and add them back to the worksheet

I have the attached userform set up and I cannot figure out how to edit an existing record and write it back to the worksheet.

Thank you for your assistance.
Asset-Register---8-18-15.xlsm
mcarsonsrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
You have the row number in the variable currentrow, although you should get an error because that variable is declared twuce/ Currentrow's value is changed with the navigation buttons. Try this

Option Explicit
Dim ws As Worksheet
Dim rData As Range
'Dim currentrow As Long
Dim currentrow As Long

'
'Private Sub setinitialrange()
'
'    Set rData = Range("A4").CurrentRegion
'    currentrow = rData.Cells(rData.Rows.Count).End(xlUp).Row
'End Sub

Private Sub cmbAddRecord_Click()
    EditAdd
End Sub

Sub EditAdd()
    Dim emptyrow As Long, flag As Boolean, ID As Long

    ''///new record, if txtnumber has a value then a record exists and will be amended
    If Me.txtNumber.Value = "" Then
        Me.txtNumber.Value = Application.Max(rData.Columns(1)) + 1
        currentrow = rData.Rows.Count + 1
    End If

    With Me
        Cells(currentrow, 1).Value = txtNumber.Text
        Cells(currentrow, 2).Value = cboCategory.Text
        Cells(currentrow, 3).Value = txtName.Text
        Cells(currentrow, 4).Value = TextBox1.Text
        Cells(currentrow, 5).Value = txtCreator.Text
        Cells(currentrow, 6).Value = txtOwner.Text
        Cells(currentrow, 7).Value = cboContainer.Text
        Cells(currentrow, 8).Value = cboClass.Text
        Cells(currentrow, 9).Value = txtCustodian.Text
        Cells(currentrow, 10).Value = txtYears.Text
        Cells(currentrow, 11).Value = cboProtect.Text
    End With
    ClearForm

End Sub

Private Sub cmdbNextRecord_Click()
    If currentrow = rData.Rows.Count - 1 Then
        MsgBox "You have selected the last record", vbCritical, "Cancel"
        Exit Sub
    Else: currentrow = currentrow + 1
        LoadBoxes
    End If
End Sub

Private Sub cmdbPreviousRecord_Click()
    If currentrow = 4 Then
        MsgBox "You have selected the first record", vbCritical, "Cancel"
        Exit Sub
    Else
        currentrow = currentrow - 1
        LoadBoxes
    End If
End Sub

Sub cmdClear_Click()
    ClearForm
End Sub

Sub ClearForm()
'/// if you use a With Statement then the control needs prefixing with .
    With Me
        .txtNumber.Text = ""
        .cboCategory.ListIndex = -1
        .txtName.Value = ""
        .TextBox1.Value = ""
        .txtCreator.Value = ""
        .txtOwner.Value = ""
        .cboContainer.ListIndex = -1
        .cboClass.ListIndex = -1
        .txtCustodian.Value = ""
        .txtYears.Value = ""
        .cboProtect.ListIndex = -1
    End With
End Sub

Private Sub cmdFirstRecord_Click()
''///jumps to first record
    currentrow = 4
    LoadBoxes
End Sub



Private Sub UserForm_Initialize()
    Set rData = Sheet1.Range("A3").CurrentRegion
    currentrow = 4
    txtNumber.SetFocus

    With Me.cboClass
        .AddItem "Public"
        .AddItem "Sensitive"
        .AddItem "Confidential"
    End With

    With Me.cboProtect
        .AddItem "Kept in Locked Fire Proof File Cabiner"
        .AddItem "Kept in locked Desk"
        .AddItem "Kept in Unlocked File Cabinet"
        .AddItem "Kept in Unlocked Desk"
        .AddItem "Electronic Format - Kept in Network Drive, Shared on Desktop"
        .AddItem "Electronic Format - Third Party Data Center Server"
        .AddItem "Electronic Format - Kept on Desktop Local Hard Drive"
        .AddItem "Electronic Format - Kept on Unprotected Laptop"
        .AddItem "Electronic Format - Stored on Encrypted Laptop"
    End With

    With Me.cboContainer
        .AddItem "Paper Copies"
        .AddItem "Network Storage Server"
        .AddItem "PC Hard Drive"
        .AddItem "Laptop Hard Drive"
        .AddItem "Data Center Storage Server"
    End With



    LoadBoxes

End Sub

Sub LoadBoxes()
'/// if you use a With Statement then the control needs prefixing with .

    With Me
        .txtNumber.Text = Cells(currentrow, 1).Text
        .cboCategory.Text = Cells(currentrow, 2).Text
        .txtName.Value = Cells(currentrow, 3).Text
        .TextBox1.Value = Cells(currentrow, 4).Text
        .txtCreator.Value = Cells(currentrow, 5).Text
        .txtOwner.Value = Cells(currentrow, 6).Text
        .cboContainer.Value = Cells(currentrow, 7).Text
        .cboClass.Value = Cells(currentrow, 8).Text
        .txtCustodian.Value = Cells(currentrow, 9).Text
        .txtYears.Value = Cells(currentrow, 10).Text
        .cboProtect.Value = Cells(currentrow, 11).Text
    End With
End Sub

Open in new window

0
Roy CoxGroup Finance ManagerCommented:
I've added a Last Record button which actually jumps the the next empty row so that you can add a bew record.
Asset-Register---8-18-15.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think you need to add few more controls on the userform because Add A New Record simply means that you want to Add a completely new record on the sheet. And all other buttons are helpful for navigation between the records.
So what I think that you should have two more command button on your userform like Search and Update.
As you can see that when you open your userform, it is blank and you will need to click on First Record button to populate the userform and then you can navigate to other record by clicking on Next Record or Previous Record, its ok for navigation purpose, but what if you instantly want to populate 100th record or record belonging to a specific Asset ID?
And that's the feature your userform is missing completely.

Please find the attached workbook where I have inserted two new button on the userform named Search and Update next to Asset ID Number Textbox. Of course you can move them to different location as per your choice.

So once your userform is initiated and if you input an Asset ID in Asset Number Textbox and click Search, the userform will be populated with the correct record belonging to that asset number.

And once any record is populated on the userform whether by using the Search button or by navigation buttons, if you click on Update after making few changes in the values on the userform, it will correctly update the record depending upon the asset number.

See if this is something which is helpful to you.
Asset-Register---8-18-15.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Roy CoxGroup Finance ManagerCommented:
It's perfectly possible to use the same button to add or edit an entry. using the navigation buttons. The only missing control is a button to jump to the next empty row. as in my example. The code is actually based on a previous post of mine.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Roy_Cox

Its like you set the caption of a command button to Close and when a user clicks that button it does something else. lol

Its a matter of common sense that if you have set the Caption of a button to "Add A New Record", no End User will ever imagine that the same button can be used to Update an existing record also. It's certainly not a good userform design.

So either the button Add A New Record should be like "Add A New Record/ Update Existing Record."

I don't know how much do you deal with the UserForms but I completely disagree with your suggestion. Sorry!
0
Roy CoxGroup Finance ManagerCommented:
I obviously would change the code to Add or Amend, but it's early here and I'm off to work. The main thing was to correct the OP's code. I've amended the code slightly
Asset-Register---8-18-15.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Roy_Cox

Glad you got my point. :)
0
Roy CoxGroup Finance ManagerCommented:
Ha ha
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
No. If you change the Asset Number it will update the record which were previously in the Asset Number Field. Like open the userform and it will show you first record and if you change the Asset Number to something else like 999, it will change the record of Asset number 1 and will not Add a New Record.
0
Roy CoxGroup Finance ManagerCommented:
As I said earlier, it is a demonstration of how to use the code that the OP was attempting to use. It is not a final version and something like the asset number in a final version would be a label or a TextBox that is not enabled.

If you want to be rude then don't do it in the OP's thread
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hey Roy!

Sorry if you think I was rude, I had no such intentions. My apologies.
I thought you would take this criticism positively.

But at the same time I think it will misguide the OP pointing him in a wrong direction.
What I was trying to say that the Add/Amend button you coded doesn't work as expected because it doesn't know when to add a new record and when to update an existing record and that's the point I was discussing with you and emphasized to have a separate button to update an existing record. As you can see OP is confused that how to include the update event within the userform and we should point him in the right direction.

And no issues and apologies again. :)
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It is not a final version and something like the asset number in a final version would be a label or a TextBox that is not enabled.

I also pointed out that if the end user wants to navigate to Nth asset number, is user supposed to click the Next Record Nth times to navigate up to that record?

That's why I suggested OP to include a Search button within the form so that user can enter the desired Asset Number in the textbox and click on Search to populate that specific record details on the form.

But ultimately it is the OP who has to decide that what's the actual requirement not us.
0
mcarsonsrAuthor Commented:
You both are very helpful, and in this case sktneer has added code that accomplished a few things I want to happen that I didn't articulate very well.  I have additional questions, then I will reward to points to both of you.

In your version sktneer, if the userform is populated and the user clicks the add record button, the currently populated record is appended to the end of the data, as you would expect.  Is there a way to add a message that the user must clear the form before adding a record?  I tried an if statement and an exit sub statement, but it didn't work.

Also, is there a way to have the next sequential record number available for entry appear somewhere on the form so that the user will know to use that number?

Thanks again for all the help, both of you.

I have attached the sktneer version of the worksheet to this comment.
Asset-Register---8-19-15.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You haven't attached my proposed version.

What I suggest is, instead of asking the user to clear the userform ( I assume you mean Asset Number Textbox) before adding a new record, you can check if the Asset Number textbox entered in the Textbox already exist in the database and if so, give a message to the user that the Asset Number already exists in the database and ask the user to confirm that user still wants to add the record in the database and if the answer is yes, add that record in the end with the next asset number available in the database.

Because after all user will need to fill the form in order to add a new record so no use of asking the user to clear when the Add New Record button is clicked.

Or maybe I didn't get your point correctly.

As far as the displaying the entry number on the form is concerned, I think that you would not want the user to mention the asset number himself what if user purposely enters the wrong asset number and if so, the asset number sequence will be out of order in your database.
Rather the code should generate the asset number itself while user tries to add a record no matter whether the Asset Number Textbox is blank or not.
0
mcarsonsrAuthor Commented:
Sorry about not attaching the file, I must not have hit the upload button.  It is attached now, but re-named with today's date.  

Actually if the user chooses first record, next record, or the search record buttons, the form is then populated.   If the user then, for whatever reason, clicks the add button, the currently populated record is appended as a new record to the end of the data.  I would like to prevent that from happening, using whatever is the best code.

And I agree with you last point.  That is what I am looking for.

I hope this makes sense.

Thank you!
Asset-Register---8-19-15.xlsm
0
Roy CoxGroup Finance ManagerCommented:
So you don't actually want to add a new record at all., i;e it should work exactly as the code did in the original workbook from which the code came. The user can navigate using the buttons and amend whatever record is selected.

I would also recommend using a ComboBox for the Asset ID.  I can edit the code to use one later
Asset-Register---8-18-15.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Here it is. See if Add New Record is working correctly.
Asset-Register---8-19-15-2.xlsm
0
mcarsonsrAuthor Commented:
Thanks again both of you.  

Sktneer, your version is close, but the last record button goes to a blank row.  Also, the new record message is showing the row number rather than the record number (txtnumber field), which is different due to the headings.  Also, when you click OK on the record number message you receive another message that the record already exists, which is incorrect.

Roy, I am sorry I wasn't clear, I do want the user to be able to add new records to the data base.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Actually I placed the msgbox to track the last row and forgot to remove. :)
And as far as Last Row button is concerned, I didn't code for that.

Please find the attached and see if everything is working correctly.
Asset-Register---8-19-15-2-v2.xlsm
0
Roy CoxGroup Finance ManagerCommented:
Then my previous example works, just use the last record button to jump to the next empty row, the Asset ID self populates.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Roy

If I am not wrong, the Last Record Button was proposed by you, right?

I changed the code to populate the last record as it wasn't working for OP for some unknown reasons, would you take a look at the changes I have made in the code to make sure that I haven't done anything wrong with it? (of course if you have some time.)
0
mcarsonsrAuthor Commented:
sktneer,

Your version gives me everything I want with one exception.  Is there a way to have the ID number populate with  - ((the last row of data, column A) +1) when the Add Record Button is selected and before any of the other data is entered.  I want the user to have the next record number pre-populated on the action of adding a record.

Roy,  I couldn't see that your code for last record performs as you describe in your 12:09 comments.

Thanks!!!!!!!!!!!!!!!!!!!!!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Add New Record is a command button it can get focus will the tab movement only and cannot be selected with mouse.

So I have inserted another label on the form named Next Available Asset Number and that will be populated with the userform initialization.

See if this is something you can work with.
Asset-Register---8-19-15-2-v3.xlsm
0
mcarsonsrAuthor Commented:
sktneer,

I actually figured out how to do it myself.  I am learning, slowly, but surely.  One last question - is there a way to add code that prevents the debug error if the user clicks the search button, when the form is not populated?

Thanks and that should do it.
Asset-Register---8-19-15-2-v2---skt.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay done.

See the attached.
Asset-Register---8-19-15-2-v4.xlsm
0
mcarsonsrAuthor Commented:
Thank you Both!!!!!

Great assistance!!!!!!!!!!!!!!!!!!!!!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could help. :)

But I think you also selected one of my reply as an assisted solution by mistake so Roy has got no points.
If you have done this by mistake, you can use Request Attention so that a Moderator will fix this for you.
I think that Roy deserves points for all the assistance he has provided to your question.
0
mcarsonsrAuthor Commented:
Thank you sktneer!

I did mean to give Roy Points as well!!!  I am a novice as you can tell.  Thank you for bringing that to my attention.

I will use the Request Attention now to correct the situation.  Sorry Roy!!!!

Roy has been a tremendous help to me on the site and on a side product he has assisted me with.
0
mcarsonsrAuthor Commented:
Well How do I request attention?  I cant find it.  Thanks
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That sounds good. :)
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You will see a link Request Attention on your very first post with question.
0
mcarsonsrAuthor Commented:
Thank you!
0
mcarsonsrAuthor Commented:
Sorry to bother again.  Since you have requested attention on the other matter already, I do not see how to add another request for Attention??
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Click that link and see if you get a reply box below and if so, you can post your message there also and it will get noticed
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.