3 choices

D

Dan @BCBS

A command button on my form has three choices (code below).
The first two work fine, the third bombs at ".ItemsSelected"
Compile error: Method or data member not found.


Private Sub KeyIndicators_Click()
On Error GoTo Err_KeyIndicators_Click
Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim R As Integer
Dim stArea As Variant
Dim stProduct As Variant
Dim stReviewer As Variant


stAreaList = ""
stProductList = ""
stReviewerList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

Y = 0
For Each stProduct In lstProduct.ItemsSelected
If Y = 0 Then
stProductList = "In('" & lstProduct.ItemData(stProduct) & "'"
Else
stProductList = stProductList & ",'" &
lstProduct.ItemData(stProduct) & "'"
End If
Y = Y + 1
Next stProduct

stProductList = stProductList & ")"

R = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stReviewerList = "In('" & cboReviewer.ItemData(stReviewer) &
"'"
Else
stReviewerList = stReviewerList & ",'" &
cboReviewer.ItemData(stReviewer) & "'"
End If
R = R + 1
Next stReviewer

stReviewerList = stReviewerList & ")"

If stAreaList <> ")" And stProductList <> ")" And stReviewer <> ")"
Then
stLinkCriteria = "[gbulocation] " & stAreaList & " and
[insurancetype] " & stProductList
ElseIf stAreaList = ")" And stProductList <> ")" Then
stLinkCriteria = "[insurancetype] " & stProductList
ElseIf stAreaList <> ")" And stProductList = ")" Then
stLinkCriteria = "[gbulocation] " & stAreaList
End If

If stLinkCriteria = "" Then
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "#
and #" & Me.txtEnd & "#"
Else
stLinkCriteria = stLinkCriteria & " and [issueclosedate] between
#" & Me.txtStart & "# and #" & Me.txtEnd & "#"
End If

stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

End If

Exit_KeyIndicators_Click:
Exit Sub

Err_KeyIndicators_Click:
MsgBox err.Description
Resume Exit_KeyIndicators_Click

End Sub
 
D

Dirk Goldgar

Dan @BCBS said:
A command button on my form has three choices (code below).
The first two work fine, the third bombs at ".ItemsSelected"
Compile error: Method or data member not found.


Private Sub KeyIndicators_Click()
On Error GoTo Err_KeyIndicators_Click
Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim R As Integer
Dim stArea As Variant
Dim stProduct As Variant
Dim stReviewer As Variant


stAreaList = ""
stProductList = ""
stReviewerList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) & "'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

Y = 0
For Each stProduct In lstProduct.ItemsSelected
If Y = 0 Then
stProductList = "In('" &
lstProduct.ItemData(stProduct) & "'" Else
stProductList = stProductList & ",'" &
lstProduct.ItemData(stProduct) & "'"
End If
Y = Y + 1
Next stProduct

stProductList = stProductList & ")"

R = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stReviewerList = "In('" &
cboReviewer.ItemData(stReviewer) & "'"
Else
stReviewerList = stReviewerList & ",'" &
cboReviewer.ItemData(stReviewer) & "'"
End If
R = R + 1
Next stReviewer

stReviewerList = stReviewerList & ")"

If stAreaList <> ")" And stProductList <> ")" And stReviewer
<> ")" Then
stLinkCriteria = "[gbulocation] " & stAreaList & " and
[insurancetype] " & stProductList
ElseIf stAreaList = ")" And stProductList <> ")" Then
stLinkCriteria = "[insurancetype] " & stProductList
ElseIf stAreaList <> ")" And stProductList = ")" Then
stLinkCriteria = "[gbulocation] " & stAreaList
End If

If stLinkCriteria = "" Then
stLinkCriteria = "[issueclosedate] between #" &
Me.txtStart & "# and #" & Me.txtEnd & "#"
Else
stLinkCriteria = stLinkCriteria & " and [issueclosedate]
between #" & Me.txtStart & "# and #" & Me.txtEnd & "#"
End If

stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

End If

Exit_KeyIndicators_Click:
Exit Sub

Err_KeyIndicators_Click:
MsgBox err.Description
Resume Exit_KeyIndicators_Click

End Sub
Click to expand...

Do you mean it fails at this line:
For Each stReviewer In cboReviewer.ItemsSelected
Click to expand...

? Is cboReviewer a combo box, as its name implies? Combo boxes don't
have an ItemsSelected collection, because only one item can be selected
at a time.
 
D

Dan @BCBS

So, I should use a list box and change the Multi Selection to Extended????
I'll give that a try..
Thanks

Dirk Goldgar said:
Dan @BCBS said:
A command button on my form has three choices (code below).
The first two work fine, the third bombs at ".ItemsSelected"
Compile error: Method or data member not found.


Private Sub KeyIndicators_Click()
On Error GoTo Err_KeyIndicators_Click
Dim stDocName As String
Dim X As Integer
Dim Y As Integer
Dim R As Integer
Dim stArea As Variant
Dim stProduct As Variant
Dim stReviewer As Variant


stAreaList = ""
stProductList = ""
stReviewerList = ""

If IsNull(txtStart) Or IsNull(txtEnd) Then
MsgBox "Please enter start and end dates"
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" &
lstArea.ItemData(stArea) & "'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"

Y = 0
For Each stProduct In lstProduct.ItemsSelected
If Y = 0 Then
stProductList = "In('" &
lstProduct.ItemData(stProduct) & "'" Else
stProductList = stProductList & ",'" &
lstProduct.ItemData(stProduct) & "'"
End If
Y = Y + 1
Next stProduct

stProductList = stProductList & ")"

R = 0
For Each stReviewer In cboReviewer.ItemsSelected
If Y = 0 Then
stReviewerList = "In('" &
cboReviewer.ItemData(stReviewer) & "'"
Else
stReviewerList = stReviewerList & ",'" &
cboReviewer.ItemData(stReviewer) & "'"
End If
R = R + 1
Next stReviewer

stReviewerList = stReviewerList & ")"

If stAreaList <> ")" And stProductList <> ")" And stReviewer
<> ")" Then
stLinkCriteria = "[gbulocation] " & stAreaList & " and
[insurancetype] " & stProductList
ElseIf stAreaList = ")" And stProductList <> ")" Then
stLinkCriteria = "[insurancetype] " & stProductList
ElseIf stAreaList <> ")" And stProductList = ")" Then
stLinkCriteria = "[gbulocation] " & stAreaList
End If

If stLinkCriteria = "" Then
stLinkCriteria = "[issueclosedate] between #" &
Me.txtStart & "# and #" & Me.txtEnd & "#"
Else
stLinkCriteria = stLinkCriteria & " and [issueclosedate]
between #" & Me.txtStart & "# and #" & Me.txtEnd & "#"
End If

stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

End If

Exit_KeyIndicators_Click:
Exit Sub

Err_KeyIndicators_Click:
MsgBox err.Description
Resume Exit_KeyIndicators_Click

End Sub
Click to expand...

Do you mean it fails at this line:
For Each stReviewer In cboReviewer.ItemsSelected
Click to expand...

? Is cboReviewer a combo box, as its name implies? Combo boxes don't
have an ItemsSelected collection, because only one item can be selected
at a time.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Dan @BCBS said:
So, I should use a list box and change the Multi Selection to
Extended???? I'll give that a try..
Click to expand...

Could be Simple, or could be Extended, but if you intend to allow
multiple selections, you have to use a list box in Multiselect mode.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

3 list boxes 1 answer 9
Type Mismatch 5
DoCmd.SendObject 2
DoCmd.OpenQuery 4
Combine 3 List box Choices 1
Combo Box 15
Multi options 25
records per list 4

Top