Utilizing Stored procedure parameters in VBA
I have Userform that does manupulate a spreadsheet by reading the column titles and rows of the cells to add or edit the values. What I want to do now is modify this so I can pass in the parameters with the text box with a stored procedure that looks into a data file in a spreadsheet.
Here is how I have it working:
The text Fileds are: ID, Name, Title
The first text box has this code:
Code:
Private Sub TextBox1_Change()
GetData
End Sub
The commandButtons have these codes:
Code:
Private Sub CommandButton1_Click()
EditAdd
End Sub
Private Sub CommandButton2_Click()
ClearForm
End Sub
Private Sub CommandButton3_Click()
Unload Me
End Sub
NOW THE Functions and Subs
Code:
Dim id As Integer, i As Integer, j As Integer, flag As Boolean
Sub GetData()
If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
Do While Cells(i + 1, 1).Value <> ""
If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 3
UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
Next j
End If
i = i + 1
Loop
If flag = False Then
For j = 2 To 3
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End If
Else
ClearForm
End If
End Sub
Sub ClearForm()
For j = 1 To 3
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End Sub
Sub EditAdd()
Dim emptyRow As Long
If UserForm1.TextBox1.Value <> "" Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Do While Cells(i + 1, 1).Value <> ""
If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 3
Cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
End If
i = i + 1
Loop
If flag = False Then
For j = 1 To 3
Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
End If
End If
End Sub
THE STORED PROCEDURE THAT WILL CREATE THE TABLE VALUES IN THE SPREADSHEET:
Code:
Select ID, Name, Title From Employees
