MS Access 2010 query that accepts multiple inputs

[DebbieAnne]

Good day,



This query works in MS Access 2010 but I as the programmer have to access the SQL statement through View->SQL View and run it.

UPDATE tblstubentry SET tblstubentry.RetRecd = Yes
WHERE tblstubentry.LICNUM IN (1001,20451,874)

I would like to change it so the user can enter the inputs instead of me. I've tried the following but it doesn't work:
UPDATE tblstubentry SET tblstubentry.RetRecd = Yes
WHERE tblstubentry.LICNUM IN ([Enter license numbers:])

This query doesn't recognize a list passed into it in this format: 1001,20451,874

Is there a way to do this in MS Access 2010?

Thanks

[JamesDeckert]

have you tried enclosing with quotes. It's probably not liking the commas.

[DebbieAnne]

Hi James,
Thanks for your reply. I tried the quotes. That didn't work either. I've read everything I can find on the Internet. Now I'm trying to have the user enter the numbers in a text box on a form and pass this to the query...but still no joy. I can't figure out how to pass in a list of values to a query parameter.

[JoeM]

have you tried enclosing with quotes. It's probably not liking the commas.

Enclosing in quotes probably wouldn't work if the LICNUM field is not a Text data type. Numeric values do not like quotes around them.

Now I'm trying to have the user enter the numbers in a text box on a form and pass this to the query...but still no joy. I can't figure out how to pass in a list of values to a query parameter.

You are sort of on the right track. Instead of trying to pass the parameters to the query, use the text box entry to actually build the SQL string, and assign the SQL string to your query.

I have helped people do that in the past. If you like, I can probably dig up some old posts where I showed people how to do that.

[JoeM]

Here is a link where I showed someone how to do that: http://www.mrexcel.com/forum/microso...ml#post3099809
In the last step, you can open any object you want. I showed how to open a Form based on the Query, but you can open the Query itself or a Report based on the Query.

[orange]

I would suggest you work from a Form.
A listbox with LICNUMs that could be updated (and probably those that currently have retrecd = NO)

Have the user select the LICNUM to Update, then click a Button, and execute the SQL behind the button click event.


I mocked up your set up

Private Sub btnUPDATE_Click()
   On Error GoTo btnUPDATE_Click_Error


    Dim item As Variant
    Dim USql As String
    Dim MyList As String
    
    'are there any selected items if not send a message, if yes then process the list
    If Me.ListLICNUM.ItemsSelected.Count > 0 Then
        For item = 0 To Me.ListLICNUM.ItemsSelected.Count - 1
            MyList = MyList & Me.ListLICNUM.ItemData(item) & ","
            Debug.Print MyList
        Next item
        MyList = "(" & Mid(MyList, 1, Len(MyList) - 1) & ")"
        Debug.Print "revised mylist    " & MyList
    Else
        MsgBox "You must select an Item to Delete"  'Update/Edit/Delete  as required
    End If
    DSql = "UPDATE tblStubEntry  SET RetRecd = Yes " _
           & " WHERE tblstubentry.LICNUM IN " & MyList
    Debug.Print DSql
    CurrentDb.Execute DSql, dbFailOnError
Exit_btnuPDATE_Click:
    Exit Sub


   On Error GoTo 0
   Exit Sub

btnUPDATE_Click_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure btnUPDATE_Click of VBA Document Form_Form18"

End Sub

Good luck. There are some debugging statements in the event code.

[DebbieAnne]

It would be great if I could do this but I don't know what the license numbers are going to be and they come in by the hundreds. :-( It's too bad Microsoft doesn't have a means of doing this. I guess I will continue to keep updating them via going into the database and entering the list myself in the SQL. It would have been better if I could have the user do this though.

[DebbieAnne]

Hi Joe,

I read your article and gave it a try. It works if I have only one entry in the textbox but not for multitple license numbers.

[ItsMe]

I would probably have a form with an unbound textbox control, command button, and a subform control. Have the user type a value and click the button. When the button is clicked, append a record to a temp table. Have the records in the temp table display in the subform. After the user is finished adding records, open a second form that is bound to a query that joins the temp table to the production table.

[orange]

I don't know what the license numbers are going to be and they come in by the hundreds.

There must be some process because you know which to delete when you delete them.
What is that process? How do you decide?
You can select record by date, age.... to restrict the size of a list, BUT you have to know the details for doing so.

Did you look at the database I sent?

[JoeM]

The method I provided will work if done right, as you are creating exactly what you need the SQL code to look like.
If you can do it manually in a query, then you can do it using that method also. You just need to make the SQL code look like what the manual code look like.
I often use MsgBox commands while building my code to test it and to see what it looks like, so I can compare it to what it NEEDS to look like.

[DebbieAnne]

The method I provided will work if done right, as you are creating exactly what you need the SQL code to look like.
If you can do it manually in a query, then you can do it using that method also. You just need to make the SQL code look like what the manual code look like.
I often use MsgBox commands while building my code to test it and to see what it looks like, so I can compare it to what it NEEDS to look like.

Thanks to everyone who offered help with this issue. In the end I solved it by putting the license numbers in an excel spreadsheet. I created a lookup table and imported the excel spreadsheet into the table; then I just changed my original query to use the lookup table to see what licenses had been returned.

Thanks for your help.

[orange]

Message was for a different post--- my error.