Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    30

    Unanswered: Run an update query on items in a list box.

    This is probably a simple matter but I have searched hi and low and can't find any help on this. I have a list box of table names and I would like to run an update query or an append query on each one selected. Nothing fancy. I don't need to loop through the entries just run the updates one at a time.

    Private Sub Command20_Click()

    DoCmd.RunSQL "UPDATE [list0] SET [list0] tblWWWW1.[date] = [enter a date]"

    End Sub

    This doesn't work. But a hardcoded table name does. This seems like something that should be very common but I can't find any help anywhere. Basically I want to run the same query on about 20 different tables. prompting for the date.
    I know that sql won't evaluate something like list0. Anyway to force it to. Any suggestions will be appreciated.

    Thanks
    Jim D.
    Last edited by darejamr; 09-12-07 at 20:02.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The question implies a design issue, so I'd wonder what the different tables represent. In any case, first you want to search here on "multiselect" to get the code to loop through the items selected in the listbox. Then your code would have to be tweaked a little:

    DoCmd.RunSQL "UPDATE " & ListboxVariable & " SET tblWWWW1.[date] = [enter a date]"
    Paul

  3. #3
    Join Date
    Jul 2005
    Posts
    30

    Many thanks for the help

    Tweaking the code worked like a charm:

    DoCmd.RunSQL "UPDATE " & List0 & " SET " & List0 & ".[date] = [enter a date]"

    runs the update query from the selection in the list box.

    DoCmd.RunSQL "INSERT INTO treatment ( [well name], [date], Product, Qty, Treater )" & _
    "SELECT " & List0 & ".[well name], " & List0 & ".date, " & List0 & ".product, " & List0 & ".qty, " & List0 & ".treater FROM " & List0 & ""

    works as well for the append query.

    Many thanks for the help!!!
    Jim D.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Glad it worked, though I'd still be interested to know what the tables hold. You may have a design issue which will continue to cause problems like this.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •