combo box...then multi select list box

[Kevin]

I have a form with a first combo box - cboProjectName, that when selected
filters a second combo box - cboBidNumber. This serves as a parameter query
for various reports. I wish to change the second combo box to a multi-select
list box - now called lstBidNumber. I used some code that I found online as
a starting point. So far, I select the "ProjectName", and it properly gives
me the available "BidNumber" selections.

When I click on any BidNumber, I get the following error message: Run Time
Error 2001 - "You cancelled the previous operation".

Here is the code I entered in the OnClick procedure for lstBidNumber:
Private Sub lstBidNumber_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryDialog")
For Each varItem In Me!lstBidNumber.ItemsSelected
strCriteria = strCriteria & ",'" & Me.lstBidNumber.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM Bid " & _
"WHERE Bid.BidNumber IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryDialog"
Set db = Nothing
Set qdf = Nothing
End Sub

I don't have too much experience with VB, so I am somewhat at a loss at how
to identify what is wrong. Should there be something in the AfterUpdate
event too?Any help would be greatly appreciated!!

 

[Klatuu]

I don't see anything obviously wrong with your code. What line is it failing
on?

 

[Kevin]

When I run the debugger, it highlights the fourth line from the bottom:
DoCmd.OpenQuery "qryDialog"

Thanks in advance for your help!
Kevin

 

[Klatuu]

Have you stepped through in debug mode and looked at the SQL in qryDialog?
It may be that there is a syntax problem.

 

[Kevin]

I have never stepped through VB code, but found how to do so in the Help
Topics. While I'm working on that, I have another question...

I set up this code in the OnClick Event. My form is set up so that you make
the selections in each box, then click a command button for various available
reports. When I had the 2 combo boxes, the second box had no event code, so
it didn't do anything until you clicked the appropriate command button. It
seems to me, logically, that having the code in the OnClick event would fire
something prematurely. Am I wrong in thinking that?

To further add to my dilemma/question...The SQL for qryDialog was previously:
SELECT Bid.ProjectName, *
FROM Bid
WHERE (((Bid.ProjectName)=[Forms]![frmEstimatSelector1]![cboProjectName])
AND ((Bid.BidNumber)=[Forms]![frmEstimatSelector1]![lstBidNumber]));


Now, after I click in the control lstBidNumber and get the error, it
automatically changes the SQL to:
SELECT *
FROM Bid
WHERE (((Bid.BidNumber) In ('1')));

I am probably in over my head with this, but have received great help with
this forum in the past, and have learned a great deal. If any of the above
info helps clue you in to a possible problem, I certainly appreciate any help
in the matter!
Thanks again!

 

[Klatuu]

I don't see anything obvious. I would put a breakpoint just before the line
qdf.SQL = strSQL
I am pretty sure there is a problem with the SQL string based on the error
you are getting and where it is happening.

Also, I have included a function I use to build my where conditions

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

I have never stepped through VB code, but found how to do so in the Help
Topics. While I'm working on that, I have another question...

I set up this code in the OnClick Event. My form is set up so that you make
the selections in each box, then click a command button for various available
reports. When I had the 2 combo boxes, the second box had no event code, so
it didn't do anything until you clicked the appropriate command button. It
seems to me, logically, that having the code in the OnClick event would fire
something prematurely. Am I wrong in thinking that?

To further add to my dilemma/question...The SQL for qryDialog was previously:
SELECT Bid.ProjectName, *
FROM Bid
WHERE (((Bid.ProjectName)=[Forms]![frmEstimatSelector1]![cboProjectName])
AND ((Bid.BidNumber)=[Forms]![frmEstimatSelector1]![lstBidNumber]));


Now, after I click in the control lstBidNumber and get the error, it
automatically changes the SQL to:
SELECT *
FROM Bid
WHERE (((Bid.BidNumber) In ('1')));

I am probably in over my head with this, but have received great help with
this forum in the past, and have learned a great deal. If any of the above
info helps clue you in to a possible problem, I certainly appreciate any help
in the matter!
Thanks again!

Have you stepped through in debug mode and looked at the SQL in qryDialog?
It may be that there is a syntax problem.

 

[Kevin]

Additional VB question: Does it matter that the code for the command buttons
is geographically before the code for my multi-select list box? When I
changed the original combo box to a list box, it automatically put any new
code at the end.
Kevin

I don't see anything obvious. I would put a breakpoint just before the line
qdf.SQL = strSQL
I am pretty sure there is a problem with the SQL string based on the error
you are getting and where it is happening.

Also, I have included a function I use to build my where conditions

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

I have never stepped through VB code, but found how to do so in the Help
Topics. While I'm working on that, I have another question...

I set up this code in the OnClick Event. My form is set up so that you make
the selections in each box, then click a command button for various available
reports. When I had the 2 combo boxes, the second box had no event code, so
it didn't do anything until you clicked the appropriate command button. It
seems to me, logically, that having the code in the OnClick event would fire
something prematurely. Am I wrong in thinking that?

To further add to my dilemma/question...The SQL for qryDialog was previously:
SELECT Bid.ProjectName, *
FROM Bid
WHERE (((Bid.ProjectName)=[Forms]![frmEstimatSelector1]![cboProjectName])
AND ((Bid.BidNumber)=[Forms]![frmEstimatSelector1]![lstBidNumber]));


Now, after I click in the control lstBidNumber and get the error, it
automatically changes the SQL to:
SELECT *
FROM Bid
WHERE (((Bid.BidNumber) In ('1')));

I am probably in over my head with this, but have received great help with
this forum in the past, and have learned a great deal. If any of the above
info helps clue you in to a possible problem, I certainly appreciate any help
in the matter!
Thanks again!

Have you stepped through in debug mode and looked at the SQL in qryDialog?
It may be that there is a syntax problem.

 

[Klatuu]

No, it does not matter where the code is within the module.

Additional VB question: Does it matter that the code for the command buttons
is geographically before the code for my multi-select list box? When I
changed the original combo box to a list box, it automatically put any new
code at the end.
Kevin

I don't see anything obvious. I would put a breakpoint just before the line
qdf.SQL = strSQL
I am pretty sure there is a problem with the SQL string based on the error
you are getting and where it is happening.

Also, I have included a function I use to build my where conditions

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

I have never stepped through VB code, but found how to do so in the Help
Topics. While I'm working on that, I have another question...

I set up this code in the OnClick Event. My form is set up so that you make
the selections in each box, then click a command button for various available
reports. When I had the 2 combo boxes, the second box had no event code, so
it didn't do anything until you clicked the appropriate command button. It
seems to me, logically, that having the code in the OnClick event would fire
something prematurely. Am I wrong in thinking that?

To further add to my dilemma/question...The SQL for qryDialog was previously:
SELECT Bid.ProjectName, *
FROM Bid
WHERE (((Bid.ProjectName)=[Forms]![frmEstimatSelector1]![cboProjectName])
AND ((Bid.BidNumber)=[Forms]![frmEstimatSelector1]![lstBidNumber]));


Now, after I click in the control lstBidNumber and get the error, it
automatically changes the SQL to:
SELECT *
FROM Bid
WHERE (((Bid.BidNumber) In ('1')));

I am probably in over my head with this, but have received great help with
this forum in the past, and have learned a great deal. If any of the above
info helps clue you in to a possible problem, I certainly appreciate any help
in the matter!
Thanks again!

:

Have you stepped through in debug mode and looked at the SQL in qryDialog?
It may be that there is a syntax problem.