Solved: Add textbox values to database using loop

[tccmdr]

I'm creating a database in excel, mainly beacause I can use all the analysis features.

My issue relates to the adding of values from approx. 40 userform textboxes to the relevant 40 columns in the database to create the row record.

How would I code this process using a loop:doh: rather than adding one by one?

Additionally, if anyone knows how I could also Delete, Search and Edit then that would be great too:ipray:

[mikerickson]

This assumes that your userform is named UserForm1 and that there are 40 text boxes named TextBox1, TextBox2, ..., TextBox40.

It also assumes that you want the data from TextBox1 to go in the first column of the row, TextBox2 to column 2, etc.

It loops through the textboxes, reading their values to create an array. That array is then written to the spreadsheet. Bulk writting is so much faster than looping through writting to one cell.

I hope this helps you.

Sub test()
Dim i As Integer
Dim xControl As Control
Dim rowNumber As Long: rowNumber = 1
Dim valuesRRay(1 To 40) As String

For i = 1 To 40
Set xControl = UserForm1.Controls("TextBox" & CStr(i))
valuesRRay(i) = xControl.Text
Next i

Range(Cells(rowNumber, 1), Cells(rowNumber, 40)).Value = valuesRRay

End Sub

[tccmdr]

Thanks Mike......but...:mkay

When I enter a new record the previous is overwritten.

How can I ensure each new record is posted under the previous:dunno

[mikerickson]

I set rowNumber to 1 as a test value. If you edit in this one line:

Dim valuesRRay(1 To 40) As String

rowNumber = ThisWorkbook.Sheets("sheet1").Range("a65536").End(xlUp).Row + 1

For i = 1 To 40Your data will be written immediatly below the last entry in column A of sheet1.

[tccmdr]

OK Mike, you're a superstar so far :thumb

How about searching for and deleting an existing record:p

[lucas]

Nice use of an array Mike....welcome to VBA Express.

[mikerickson]

More assumptions. I am assuming:
that each record has a unique identifing string
that each record is listed as a row on sheet 1, and
that the identifing string is in (but not nessesarily all of) one of the cells in that row.
Given the identifier, this function will return the row number that that identifier is on.

Function foundRecordRow(ByVal searchFor As String) As Long
Dim foundRay As Range
With ThisWorkbook
Set foundRay = .Sheets("sheet1").Cells.Find(What:=searchFor, _
After:=.Sheets("sheet1").Range("a1"), LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Nothing Is foundRay Then
foundRecordRow = 0
Else
foundRecordRow = foundRay.Row
End If
End With
End Function

OK, that's the Find. (If no record exists, findRecordRow("notFound") = False)

This delete routine will take either the identifing string or the row number as its argument.

Sub deleteRecord(killMe As Variant)
Dim promptStr As String
With ThisWorkbook
If TypeName(killMe) = "String" Then killMe = foundRecordRow(killMe)
If 1 > killMe Then Exit Sub
.Sheets("sheet1").Cells(killMe, 1).Parent.Activate
.Sheets("sheet1").Cells(killMe, 1).Select
promptStr = "Delete the record on row " & killMe & "?" _
& vbCrLf & "There is no UnDo."
If MsgBox(promptStr, vbYesNo) = vbNo Then
Exit Sub
Else
.Sheets("sheet1").Cells(killMe, 1).EntireRow.Delete shift:=xlUp
End If
End With
End SubSince there is no undo, it asks for consent before deleting. Note that it deletes the entire row, not just columns 1-40.

[tccmdr]

Looks good Mike,

How would I call the function when clicking a "Search/Delete" button:banghead:

[mikerickson]

The Button_Click routin would be something like this
Sub CommandButtonClick()

Dim nameEntered As String

nameEntered = Application.InputBox("Who do you want to delete", Type:=2)

If nameEntered <> "False" Then Call deleteRecord(nameEntered)

End Sub

[tccmdr]

Thanks Mike, everything seems to be working so far.

The search function isn't returning anything......any ideas:think:
I used the same code set to call both the delete and search functions


ps. Is it possible to populate all the text boxes with the database string that matches the delete or search:dunno

[mikerickson]

Ideas? ....Did you have to modify the sheet name and location of the name column? Did you apply that modification in two places in the search routine?

[tccmdr]

Sorry Mike, I don't understand:mkay

[mikerickson]

How are you calling the function?

[tccmdr]

:think:


Private Sub CommandButton2_Click()
Dim nameEntered As String
nameEntered = Application.InputBox("Who do you want to search", Type:=2)
If nameEntered <> "False" Then Call foundRecordRow(nameEntered)
End Sub

[mikerickson]

Try this

Private Sub CommandButton2_Click()
Dim nameEntered As String
nameEntered = Application.InputBox("Who do you want to search", Type:=2)
MsgBox nameEntered & " is on row " & foundRecordRow(nameEntered)

End Sub

[tccmdr]

Thanks Mike - :thumb

My only issue now is if any data contains the same search or delete string, the first instance is returned.

I know, I know you already told me:



that each record has a unique identifing string



I'll have to dig a little further:think:

[mikerickson]

I'm glad it worked for you. If you plan to deal with multiple "John Smiths", you might think of creating a helper column "key" that is unique for each person. Keying off of a unique identifier will make life much easier than always having to account for "what if there are two Johns Smith."

Good luck

[tccmdr]

Thanks Mike :friends: