Compile multiple items from text box into update query

[efiftythree]

I need to build a query from a list of items in a text box. The text box will contain around 40 items most of the time. I have created a form with the necessary objects such as a text box for the item list and a text box for the date. Basically what the query needs to do (as you can see below) is to set a target date for each of the items in the text box list.

User List Text Box = txtDateUser
Date Text Box = txtDate
Button = btnDateExecute

e29251
e20275
e10158

Private Sub btnDateExecute_Click()

DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE MigrationData SET MigrationData.TargetDate = [Forms]![UpdateForm]![txtDate] WHERE (((MigrationData.UserID)="e29251")) OR (((MigrationData.UserID)="e20275")) OR (((MigrationData.UserID)="e10158"));"

DoCmd.SetWarnings True

End Sub

What I need help with is taking the information that is pasted into the text box and compiling it into a usable piece of the query.. the (((MigrationData.UserID)="e29251")) OR parts.

Any help would be appreciated!

 

[Remou]

What you need is IN.

TextBox: "e29251","e20275"

[tt]strSQL=StrSQL & "Where MigrationData.UserID IN (" & Me.TextBox & ")"[/tt]


This is much easier with a listbox: thread702-1390952

 

[VRoscioli]

I would do something like this:

Private Sub btnDateExecute_Click()

Dim infoString as String

    DoCmd.SetWarnings False
    
infoString = "'" & Replace(txtDateUser,vbCrLf,"', '") & "'"

    DoCmd.RunSQL "UPDATE MigrationData SET MigrationData.TargetDate = [Forms]![UpdateForm]![txtDate] WHERE (MigrationData.UserID IN (" & infoString & "));"
    
    DoCmd.SetWarnings True

End Sub

I'm not entirely sure if the Replace will work correctly on the carriage returns, but it's worth a shot.



-V

 

[efiftythree]

Hey! That worked great! Many thanks to both of you especially VRoscioli!