Capture list box selections to build SQL query - Access97 4

[ehicks727]

Normally, I'd search until I found my answer, but it looks like the search has been down for two days....

Let me start by divulging my experience level, I've had to give myself a cram session in Access VBA in the last month... I'm normally a java programmer, so many of the things that I would be able to easily do, I'm having to find the VBA equivalent... thanks to tek-tips, I've found most of them.

Anyway, I have a form that has several multi-select listboxes, one is counties, for example.

I need to write code that grabs all the selected counties and somehow get that into a query string. (exp... UPDATE providers SET providers.userID = 1 WHERE providers.county = <listbox selection 0> OR providers.county = <listbox selection 1> etc...) Normally, I would create a collection, add all the selected items to the collection, and build a string by iterating through the collection (or something to that effect).

Unfortunately, I don't know what an equivalent procedure would be in Access 97. Any suggestions would be greatly appreciated. Thank you.

 

[hkaing79]

String concatenation is &.

for i = 0 to lstSelect.ItemsSelected.Count - 1
CurrentDB.Execute "UPDATE providers SET providers.userID = 1 WHERE providers.county = '" & lstSelect.column(0,i) & "'"
next i

Or something like that. Good luck.

 

[ehicks727]

Thank you.... that looks like that's the trick, let me implement it and I'll let you know. thanks again.

 

[TheAceMan1]

How are ya ehicks727 . . . . .

What you need is already there. Use the [blue]ItemsSelected Property[/blue] which returns a reference to the [blue]ItemsSelected Collection[/blue]. The collection [blue]returns indexes[/blue] that can be used with the [blue]Column Method[/blue].

[blue]   Dim LB As ListBox, valLB, idx
   
   Set LB = Me!YourListBoxName
   
   For Each idx In LB.ItemsSelected
      valLB = LB.Column(1, idx)
      [green]'Concatenate SQL here[/green]
   Next[/blue]

See Ya! . . . . . .

 

[ehicks727]

Ok, my next question is.... I believe I'm not doing this by the most efficient method.

What I mean is if the user selected 50 counties, I'll have a VERY long SQL string. Does anyone know a better way of executing this query? Thanks.

 

[PHV]

You may create a temporary table and use a join to it.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

Could you use the SQL IN operator? See for instance thread702-787778 (which is building a where clause, but shows the idea). Else FancyPraire has a faq on criterias from different form controls that might perhaps address the issue faq181-5497.

Roy-Vidar

 

[ehicks727]

I like the IN idea... I've used that in subquery statements in MySQL before, but didn't think of using it in this instance. Let me play with it and I'll let you know.. thanks!!

 

[hkaing79]

Change it to a combobox. Only one selection. :-D

 

[ehicks727]

Hey, it worked! Thank you all who helped, I appreciate it. Here's the code I ended up using in case anyone uses this in the future.

Dim uid, uName As String
    uid = Me!cmbUserName.Column(0)
    uName = Me!cmbUserName.Column(1)
    
    Dim sql As String
    sql = "UPDATE providers SET providers.userID = " & uid & ", providers.userName = '" & uName & "' WHERE [providers].[county-name] IN ("

    Dim lb As ListBox, valLB, idx
   
    Set lb = Me!lstCounty
    
    For Each idx In lb.ItemsSelected
        valLB = lb.Column(0, idx)
        sql = sql & "'" & valLB & "',"
    Next
    
    sql = sql & "'dummyEnding');"
    
    MsgBox sql
    
    'DoCmd.SetWarnings False
    DoCmd.RunSQL (sql)
    'DoCmd.SetWarnings True

 

[hkaing79]

Usually, I check for nulls to avoid the error of IN (). If not null, then I have to cut the last two character off because I concatenate with ", ". But your use of the 'dummyEnding' takes care of both. Brilliant!

Thanks for sharing your code.

 

[TheAceMan1]

Or

[blue]

[blue]SQL = Left(SQL,Len(SQL-1) & ";"[/b][/blue]

[img]http://home.nyc.rr.com/aceman1/Graphics/Calvin.gif[/img] [COLOR=darkgoldenrod][b]See Ya! .  .  .  .  .  .[/b][/color]

 

[TheAceMan1]

Lets try that again:

[blue]   SQL = Left(SQL, Len(SQL-1)) & ";" [/blue]

See Ya! . . . . . .

 

[FancyPrairie]

This FAQ faq181-5497 contains a function that will also do what you want. It works for list boxes, combo boxes, text boxes, ranges, options groups, and check boxes. You only have to do 3 things to make it work.

1. Open a new module, highlight the code in the FAQ, copy it, and paste it into your new module.
2. Set the tag properties as stated in the FAQ
3. Open the report as stated in the FAQ