What am I doing wrong? -MultiSelect List box 
Author Message
 What am I doing wrong? -MultiSelect List box

Hi,

Hope someone can help me out with this....Access97...

I have looked in help, the kb, Dev's sight....worked on this thing for I
don't know how long...here's what I have:

A multiselect list box with a table (tblTest) as the row source...there is a
yes/no field in the table base on whether the item is selected from the list
box...No matter what I do with the code below it will only update one
record...

********Start Code******

Private Sub Command2_Click()
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strSql As String
Dim db As Database
Set db = CurrentDb

strSql = "UPDATE tblTest SET [tblTest].[Use_Section] = True Where "
Set frm = Forms!frmMultiTest
Set ctl = Me!List0
 For Each varItm In ctl.ItemsSelected
     strSql = strSql & "[tblTEST].[Sec_Num]=" & ctl.ItemData(varItm) & " OR
[tblTEST].[Sec_Num]="
 Next varItm

strSql = Left$(strSql, Len(strSql) - 23)

db.Execute strSql

End Sub

************End Code*******************

Thanks in advance for any help and guidance,

Bud Dean



Fri, 19 Apr 2002 03:00:00 GMT  
 What am I doing wrong? -MultiSelect List box
Sorry fo the post...figured it out... only need the "or"

Bud Dean



Sat, 20 Apr 2002 03:00:00 GMT  
 What am I doing wrong? -MultiSelect List box

Quote:

> Hi,

> Hope someone can help me out with this....Access97...

> I have looked in help, the kb, Dev's sight....worked on this thing for I
> don't know how long...here's what I have:

> A multiselect list box with a table (tblTest) as the row source...there is a
> yes/no field in the table base on whether the item is selected from the list
> box...No matter what I do with the code below it will only update one
> record...

> ********Start Code******

> Private Sub Command2_Click()
> Dim frm As Form, ctl As Control
> Dim varItm As Variant
> Dim strSql As String
> Dim db As Database
> Set db = CurrentDb

> strSql = "UPDATE tblTest SET [tblTest].[Use_Section] = True Where "
> Set frm = Forms!frmMultiTest
> Set ctl = Me!List0
>  For Each varItm In ctl.ItemsSelected
>      strSql = strSql & "[tblTEST].[Sec_Num]=" & ctl.ItemData(varItm) & " OR
> [tblTEST].[Sec_Num]="
>  Next varItm

> strSql = Left$(strSql, Len(strSql) - 23)

> db.Execute strSql

> End Sub

> ************End Code*******************

> Thanks in advance for any help and guidance,

> Bud Dean

Hi

When I build a SQL string in code I always use it in a message box as a
debugging step before trying to execute it.

If you did this after your line:
strSql = Left$(strSql, Len(strSql) - 23)
MsgBox strSql
you should see a problem with your SQL statement.

Try this:
strSql = strSql & "[tblTEST].[Sec_Num]=" & ctl.ItemData(varItm) & " OR "

then
strSql = Left$(strSql, Len(strSql) - 4)

Also, it might be wise to check whether any items have been selected in
the listbox:
If ctl.ItemsSelected.Count > 0 Then

Regards - Colin



Sun, 21 Apr 2002 03:00:00 GMT  
 What am I doing wrong? -MultiSelect List box
Thanks,

I'll start doing that....

Bud

Quote:

> > Hi,

> > Hope someone can help me out with this....Access97...

> > I have looked in help, the kb, Dev's sight....worked on this thing for I
> > don't know how long...here's what I have:

> > A multiselect list box with a table (tblTest) as the row source...there
is a
> > yes/no field in the table base on whether the item is selected from the
list
> > box...No matter what I do with the code below it will only update one
> > record...

> > ********Start Code******

> > Private Sub Command2_Click()
> > Dim frm As Form, ctl As Control
> > Dim varItm As Variant
> > Dim strSql As String
> > Dim db As Database
> > Set db = CurrentDb

> > strSql = "UPDATE tblTest SET [tblTest].[Use_Section] = True Where "
> > Set frm = Forms!frmMultiTest
> > Set ctl = Me!List0
> >  For Each varItm In ctl.ItemsSelected
> >      strSql = strSql & "[tblTEST].[Sec_Num]=" & ctl.ItemData(varItm) & "
OR
> > [tblTEST].[Sec_Num]="
> >  Next varItm

> > strSql = Left$(strSql, Len(strSql) - 23)

> > db.Execute strSql

> > End Sub

> > ************End Code*******************

> > Thanks in advance for any help and guidance,

> > Bud Dean

> Hi

> When I build a SQL string in code I always use it in a message box as a
> debugging step before trying to execute it.

> If you did this after your line:
> strSql = Left$(strSql, Len(strSql) - 23)
> MsgBox strSql
> you should see a problem with your SQL statement.

> Try this:
> strSql = strSql & "[tblTEST].[Sec_Num]=" & ctl.ItemData(varItm) & " OR "

> then
> strSql = Left$(strSql, Len(strSql) - 4)

> Also, it might be wise to check whether any items have been selected in
> the listbox:
> If ctl.ItemsSelected.Count > 0 Then

> Regards - Colin



Sun, 21 Apr 2002 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Combo box with Multiselect (drop down list with multiselect)

2. Recordset: What am I doing wrong?

3. What am I doing wrong??

4. What am I doing wrong?

5. Help with syntax. What am I doing wrong

6. What am I doing wrong?

7. What am I doing wrong?

8. What am I doing wrong?

9. Please tell me what I am doing wrong - DAO parameter Append Query :(

10. Select Case - What am I doing wrong?

11. What am I doing wrong (easy).

12. OLE problem, or what am I doing wrong?

 

 
Powered by phpBB® Forum Software