List Box

[auujxa2 via AccessMonster.com]

I'm getting a type mismatch error. I want to update the check box [Indicator]
to "0" in [MasterTbl] based on the criteria. Thanks for your help in advance

Dim strSQL As String
Dim varitem As Variant

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = No" & "WHERE [MasterTbl].
[Vendor] = " & Me.List22.Column(0) _
And "WHERE [MasterTbl].[Department] = " & Me.Combo64 _
And "WHERE [MasterTbl].[Stores] = " & Me.List7.Column(0)

For Each varitem In Me.List7.ItemsSelected

DoCmd.RunSQL strSQL

Next varitem

 

[Golfinray]

Type mismatch is almost always because you are refering to something, say, as
date when it is set in the table as text.

 

[John Spencer]

Dim strSQL As String
Dim varitem As Variant

For Each varitem In Me.List7.ItemsSelected

'You need to rebuild the string each time you go through
'the loop.
'Assumption is that Vendor, Department, and stores are all
'number fields.


strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = " & Me.List22.Column(0) _
" And [MasterTbl].[Department] = " & Me.Combo64 _
" And [MasterTbl].[Stores] = " & Me.varItem.Column(0)


DoCmd.RunSQL strSQL

Next varitem

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[auujxa2 via AccessMonster.com]

aha!! that's why. all the fields are text. should i do "IS LIKE" or "LIKE"
instead of "="?

Dim strSQL As String
Dim varitem As Variant

For Each varitem In Me.List7.ItemsSelected

'You need to rebuild the string each time you go through
'the loop.
'Assumption is that Vendor, Department, and stores are all
'number fields.

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = " & Me.List22.Column(0) _
" And [MasterTbl].[Department] = " & Me.Combo64 _
" And [MasterTbl].[Stores] = " & Me.varItem.Column(0)

DoCmd.RunSQL strSQL

Next varitem

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I'm getting a type mismatch error. I want to update the check box [Indicator]
to "0" in [MasterTbl] based on the criteria. Thanks for your help in advance

[quoted text clipped - 12 lines]

Next varitem

 

[auujxa2 via AccessMonster.com]

It doesn't like the varitem part in the last line of the SQL statement:

Dim strSQL As String
Dim varitem As Variant


For Each varitem In Me.List7.ItemsSelected

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [Vendor] LIKE " & Me.List22.Column(0) _
And "[Department] LIKE " & Me.Combo64.Column(0) _
And "[Stores] LIKE " & Me.varitem.Column(0)

DoCmd.RunSQL strSQL

Next varitem

Dim strSQL As String
Dim varitem As Variant

For Each varitem In Me.List7.ItemsSelected

'You need to rebuild the string each time you go through
'the loop.
'Assumption is that Vendor, Department, and stores are all
'number fields.

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = " & Me.List22.Column(0) _
" And [MasterTbl].[Department] = " & Me.Combo64 _
" And [MasterTbl].[Stores] = " & Me.varItem.Column(0)

DoCmd.RunSQL strSQL

Next varitem

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I'm getting a type mismatch error. I want to update the check box [Indicator]
to "0" in [MasterTbl] based on the criteria. Thanks for your help in advance

[quoted text clipped - 12 lines]

Next varitem

 

[auujxa2 via AccessMonster.com]

I changed Me.varitem.Column(0) to varitem, and when I debug, it says it's
value is 0, NOT one the stores selected.

It doesn't like the varitem part in the last line of the SQL statement:

Dim strSQL As String
Dim varitem As Variant

For Each varitem In Me.List7.ItemsSelected

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [Vendor] LIKE " & Me.List22.Column(0) _
And "[Department] LIKE " & Me.Combo64.Column(0) _
And "[Stores] LIKE " & Me.varitem.Column(0)

DoCmd.RunSQL strSQL

Next varitem

Dim strSQL As String
Dim varitem As Variant

[quoted text clipped - 27 lines]

 

[John Spencer]

No you just need to include text delimiters. You should only use LIKE if
you are going to use wildcard matching.

One way to get the correct SQL string is to use

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = """ & Me.List22.Column(0) & """" & _
" And [MasterTbl].[Department] = """ & Me.Combo64 & """" &_
" And [MasterTbl].[Stores] = """ & varItem.Column(0) & """"

Or you can use
strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = " & Chr(34) & Me.List22.Column(0) &
Chr(34) & _
" And [MasterTbl].[Department] = "& Chr(34) & Me.Combo64 &
Chr(34) &_
" And [MasterTbl].[Stores] = " & Chr(34) & varItem.Column(0) &
Chr(34)

I must admit though that I'm not sure this will work with VarItem. I
haven't tested the code.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

aha!! that's why. all the fields are text. should i do "IS LIKE" or
"LIKE"
instead of "="?

Dim strSQL As String
Dim varitem As Variant

For Each varitem In Me.List7.ItemsSelected

'You need to rebuild the string each time you go through
'the loop.
'Assumption is that Vendor, Department, and stores are all
'number fields.

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = " & Me.List22.Column(0) _
" And [MasterTbl].[Department] = " & Me.Combo64 _
" And [MasterTbl].[Stores] = " & Me.varItem.Column(0)

DoCmd.RunSQL strSQL

Next varitem

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I'm getting a type mismatch error. I want to update the check box
[Indicator]
to "0" in [MasterTbl] based on the criteria. Thanks for your help in
advance

[quoted text clipped - 12 lines]

Next varitem

 

[John W. Vinson]

Dim strSQL As String
Dim varitem As Variant

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = No" & "WHERE [MasterTbl].
[Vendor] = " & Me.List22.Column(0) _
And "WHERE [MasterTbl].[Department] = " & Me.Combo64 _
And "WHERE [MasterTbl].[Stores] = " & Me.List7.Column(0)

For Each varitem In Me.List7.ItemsSelected

DoCmd.RunSQL strSQL

Next varitem

Rather than updating one record at a time, you can get it done all in one
swell foop by using the IN() operator:

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = " & Me.List22.Column(0) _
" And [MasterTbl].[Department] = " & Me.Combo64 _
" And [MasterTbl].[Stores] IN ("
For Each varItem In Me.List7.ItemsSelected
strSQL = strSQL & Chr(34) & varItem.Column(0) & Chr(34) & ","
Next varItem
' trim off the trailing comma and add a close paren
strSQL = Left(strSQL, Len(strSQL) - 1) & ")"
DoCmd.RunSQL strSQL

 

[auujxa2 via AccessMonster.com]

The good news is.. I don't get the type mismatch error anymore. The bad news
is, after the query runs, it doesn't update [MasterTbl].[Indicator] to FALSE.
So I used the debug.print method to see if null values were pulling and they
were.

Here is what is pulling null:

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = """ & Me.lstVendors.Column(0) & """" &
_
" And [MasterTbl].[Department] = """ & Me.cboDepartments & """" & _
" And [MasterTbl].[Stores] IN ("
For Each varItem In Me.lstActiveStores.ItemsSelected
strSQL = strSQL & Chr(34) & Me.lstActiveStores.ItemData(varItem) & Chr(34)
& ","
Next varItem

aha!! that's why. all the fields are text. should i do "IS LIKE" or "LIKE"
instead of "="?

Dim strSQL As String
Dim varitem As Variant

[quoted text clipped - 27 lines]

 

[auujxa2 via AccessMonster.com]

The good news is.. I don't get the type mismatch error anymore. The bad news
is, after the query runs, it doesn't update [MasterTbl].[Indicator] to FALSE.
So I used the debug.print method to see if null values were pulling and they
were.

Here is what is pulling null:

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = """ & Me.lstVendors.Column(0) & """" &
_
" And [MasterTbl].[Department] = """ & Me.cboDepartments & """" & _
" And [MasterTbl].[Stores] IN ("
For Each varItem In Me.lstActiveStores.ItemsSelected
strSQL = strSQL & Chr(34) & Me.lstActiveStores.ItemData(varItem) & Chr(34)
& ","
Next varItem

I can email you the database if that would help. I stripped the tables down
so it's under 1 MB.

Dim strSQL As String
Dim varitem As Variant

[quoted text clipped - 9 lines]

Next varitem

Rather than updating one record at a time, you can get it done all in one
swell foop by using the IN() operator:

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = " & Me.List22.Column(0) _
" And [MasterTbl].[Department] = " & Me.Combo64 _
" And [MasterTbl].[Stores] IN ("
For Each varItem In Me.List7.ItemsSelected
strSQL = strSQL & Chr(34) & varItem.Column(0) & Chr(34) & ","
Next varItem
' trim off the trailing comma and add a close paren
strSQL = Left(strSQL, Len(strSQL) - 1) & ")"
DoCmd.RunSQL strSQL

 

[auujxa2 via AccessMonster.com]

When I take out the last line, and just used selected Vendor and Department
as a filter, it worked. so the issue is why varItem is returning Null. The
code below works, but it's missing the varItem in the last line. I wonder if
it has to do with my list box itself? Or perhaps because it's text, not
numeric - if varItem is only for numeric.

For Each varItem In Me.lstActiveStores.ItemsSelected

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = """ & Me.lstVendors.Column(0) & """" &
_
" And [MasterTbl].[Department] = """ & Me.cboDepartments & """"

DoCmd.RunSQL strSQL

Next varItem

Dim strSQL As String
Dim varitem As Variant

[quoted text clipped - 9 lines]

Next varitem

Rather than updating one record at a time, you can get it done all in one
swell foop by using the IN() operator:

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = " & Me.List22.Column(0) _
" And [MasterTbl].[Department] = " & Me.Combo64 _
" And [MasterTbl].[Stores] IN ("
For Each varItem In Me.List7.ItemsSelected
strSQL = strSQL & Chr(34) & varItem.Column(0) & Chr(34) & ","
Next varItem
' trim off the trailing comma and add a close paren
strSQL = Left(strSQL, Len(strSQL) - 1) & ")"
DoCmd.RunSQL strSQL

 

[auujxa2 via AccessMonster.com]

GOT IT!!! I added ctl instead of me. Thank you all for your help!

Dim strSQL As String
Dim varItem As Variant
Dim frm As Form, ctl As Control
Set frm = Forms!frmMain
Set ctl = frm!lstActiveStores

For Each varItem In Me.lstActiveStores.ItemsSelected

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = """ & Me.lstVendors.Column(0) & """" &
_
" And [MasterTbl].[Department] = """ & Me.cboDepartments & """" & _
" And [MasterTbl].[Stores] = """ & ctl.ItemData(varItem) & """"

DoCmd.RunSQL strSQL

Next varItem

Dim strSQL As String
Dim varitem As Variant

[quoted text clipped - 9 lines]

Next varitem

Rather than updating one record at a time, you can get it done all in one
swell foop by using the IN() operator:

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = FALSE " & _
"WHERE [MasterTbl].[Vendor] = " & Me.List22.Column(0) _
" And [MasterTbl].[Department] = " & Me.Combo64 _
" And [MasterTbl].[Stores] IN ("
For Each varItem In Me.List7.ItemsSelected
strSQL = strSQL & Chr(34) & varItem.Column(0) & Chr(34) & ","
Next varItem
' trim off the trailing comma and add a close paren
strSQL = Left(strSQL, Len(strSQL) - 1) & ")"
DoCmd.RunSQL strSQL