CODEWITHSUNDEEP
vbams-access
Thang Dang
Thang Dang

Reputation: 493

Continue Loop in TextBox to search multiple value from Database, MS Access

I've changed the textbox "enter key behavior", so that everytime I hit enter, textbox will go to the next line,

Project's Form View

When user Click the Button, subform show result for the first line, but then how to get result in every line user input.

Private Sub CountButton_Click()
    Dim SQL As String
    
    SQL = "SELECT database.Tracking, database.Date, DateDiff(""d"",[Date],Date()) As Aeging FROM database;"
                
    Me.Query1_subform.Form.RecordSource = SQL
    Me.Query1_subform.Form.Requery
    
End Sub

Do I need to use Loop or VbCrLf or something else to function the textbox

Upvotes: 1

Views: 210

Answers (1)

June7
June7

Reputation: 21379

Split textbox value to an array and loop array to build comma-separated string or:

strIN = Replace(Me.textbox, vbCrLf, "','")
If InStrRev(strIN, "','") > 0 Then strIN = Left(strIN, Len(strIN)-3)
SQL = "SELECT database.Tracking, database.Date, DateDiff('d', [Date], Date()) As Aging " & _ 
"FROM database " & _
"WHERE Tracking IN('" & strIN & "');"

Using a textbox relies on users to be consistent with input - not starting input with a CR and no other extraneous characters input accidentally or otherwise.

More reliable alternative is a multi-select listbox of Tracking values. Code loops through selected items and builds comma-separated string.

Upvotes: 2

Related Questions