Update SQL Table from Userform (Excel VBA)

Question

I have a table that lists Departments and Passwords in SQL Server. I am creating a Userform UI so that users are able to view list, amend an entry or delete an entry.

I have made it look like this:

I managed to set up the ADODB connection when a user opens the form, it would automatically load the list using:

Private Sub Userform_Activate()

My rational is that I want a user to select from the list box, it would display the Department and Password in textbox1 and textbox2, respectively.

The user then can either Update the record or Delete the record. This would be a very straight-forward SQL operation.

What I am hitting a road block with at the moment is actually getting the data displayed in Textbox1 and Textbox2.

Code

Private Sub Select_Click()
If Me.ListBox1.Selected(i) Then
    Me.TextBox1 = Me.ListBox1.List(i, 0)
    Me.TextBox2 = Me.ListBox1.List(i, 1)
End If
End Sub

Any advise would be appreciated.

Furthermore, is my logic sound? Or is there a better way of doing things?

I know this is 2-in-1 question, but thanks!

Private Sub Select_Click() this looks like a separate procedure that you wrote. Tie the code to the on click event of the actual listbox. So Private Sub Listbox1_Click() — Scott Holtzman
– Scott Holtzman, Commented Jun 25, 2018 at 16:52
That happens when a user clicks "Select" button. This will not work? — Oday Salim
– Oday Salim, Commented Jun 25, 2018 at 17:12

Scott Holtzman · Accepted Answer · 2018-06-25 17:35:07Z

1

Just tested and this works.

Private Sub ListBox1_Click()

    TextBox1.Value = ListBox1.List(ListBox1.ListIndex, 0)
    TextBox2.Value = ListBox1.List(ListBox1.ListIndex, 1)

End Sub

answered Jun 25, 2018 at 17:35

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update SQL Table from Userform (Excel VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related