By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Combo and List Box Problem with select more than one value

P: 44
Hi,
I have created a form, which shows values in a combo box and a button with the following code behind:

Private Sub Open1_Click()
On Error GoTo Err_macro1_Click

Dim db As DAO.Database
Dim strSQL As String
Dim qdf As DAO.QueryDef

Set db = CurrentDb()
DoCmd.Close acQuery, "qry_023_Select_Agent_Numbers_National"
' Delete Temp Query
DoCmd.DeleteObject acQuery, "qry_023_Select_Agent_Numbers_National"
' Construct SQL statement
strSQL = "SELECT [tbl_004_Agent Numbers].Name, [tbl_004_Agent Numbers].[Staff No]," & _
"[qry_023b_Select_Employment_Status].Title, [qry_023a_Select_Department].[Dept Name]," & _
"[qry_023b_Select_Employment_Status].[Employment Status]," & _
"[tbl_004_Agent Numbers].AFSL, [tbl_004_Agent Numbers].[Individual ID]," & _
"[tbl_004_Agent Numbers].[CAPS ID], [tbl_004_Agent Numbers].[Primary CAPS]," & _
"[tbl_004_Agent Numbers].[CFS ID], [tbl_004_Agent Numbers].[CBA Short Name]," & _
"[tbl_004_Agent Numbers].[PRU Agency No], [tbl_004_Agent Numbers].CAN," & _
"[tbl_004_Agent Numbers].Symetry, [tbl_004_Agent Numbers].[Symetry Logon], " & _
"[tbl_004_Agent Numbers].[Symetry Password], [tbl_004_Agent Numbers].[Broker Code]," & _
"[tbl_004_Agent Numbers].[ATC Code], [tbl_004_Agent Numbers].[IRESS Broker Code]," & _
"[tbl_004_Agent Numbers].AXA, [tbl_004_Agent Numbers].BT," & _
"[tbl_004_Agent Numbers].[Challenger (HMT)], [tbl_004_Agent Numbers].Citicorp," & _
"[tbl_004_Agent Numbers].[Credit Suisse], [tbl_004_Agent Numbers].ING," & _
"[tbl_004_Agent Numbers].Macquarie, [tbl_004_Agent Numbers].[Merrill Lynch]," & _
"[tbl_004_Agent Numbers].[MLC Life], [tbl_004_Agent Numbers].[MLC Invest]," & _
"[tbl_004_Agent Numbers].[MLC Logon], [tbl_004_Agent Numbers].Perpetual," & _
"[tbl_004_Agent Numbers].Sandhurst " & _
"FROM ([qry_023a_Select_Department] INNER JOIN [qry_023b_Select_Employment_Status] " & _
"ON [qry_023a_Select_Department].OUN = [qry_023b_Select_Employment_Status].OUN)" & _
"INNER JOIN [tbl_004_Agent Numbers] " & _
"ON [qry_023b_Select_Employment_Status].[Staff No] = [tbl_004_Agent Numbers].[Staff No]" & _
"WHERE ((([tbl_004_Agent Numbers].[Staff No]) > 100)) and " & _
"([qry_023a_Select_Department].[Dept Name] = '" & cboDept.Value & "') and" & _
"(qry_023b_Select_Employment_Status.[Employment Status] = '" & cboStat.Value & "')" & _
" ORDER BY [tbl_004_Agent Numbers].[Staff No];"
Debug.Print strSQL
' Create query and provide it with its SQL statement
Set qdf = db.CreateQueryDef("qry_023_Select_Agent_Numbers_Na tional", strSQL)
' Open query for user
DoCmd.OpenQuery "qry_023_Select_Agent_Numbers_National"


Open1_Click_Click:
Exit Sub

Err_macro1_Click:
MsgBox Err.Description
Resume Open1_Click_Click

End Sub
Oct 5 '06 #1
Share this Question
Share on Google+
2 Replies


P: 44
Sorry I was too fast with the submit button. Here again the code a bit smaller:

Private Sub Open1_Click()

On Error GoTo Err_macro1_Click
Dim db As DAO.Database
Dim strSQL As String
Dim qdf As DAO.QueryDef
Set db = CurrentDb()

DoCmd.Close acQuery, "MyQry"
DoCmd.DeleteObject acQuery, "MyQry"
strSQL = "SELECT ...
"FROM ...
"WHERE "([MyQry].[Dept Name] = '" & cboDept.Value & "') and" & _
"(MyQry.[Employment Status] = '" & cboStat.Value & "')" & _
" ORDER BY ...;"
Debug.Print strSQL

Set qdf = db.CreateQueryDef("MyQry", strSQL)
DoCmd.OpenQuery "MyQry"

Open1_Click_Click:
Exit Sub

Err_macro1_Click:
MsgBox Err.Description
Resume Open1_Click_Click

End Sub


Now my question. First I had a combo box with the values. There I can only select one value (or I didn't find out how to select mor than one - any idea??) and the user can add or amend the values (bad :( ).
That's why I chose a list box. But I don't know how the code must be that I can select more than one value from the list box and get those as criterias in the output. Currently it shows me nothing.

Any help is much appreciated :)
Thanks
Oct 5 '06 #2

PEB
Expert 100+
P: 1,418
PEB
Hi isetea!

About the multiple list select , there is a good exemple in the help.... And to set all values as criteria in your query...

Use OR ()

Or better use
IN ("A", "B", "C")

As format of your where clause!

Best regards!

Example
The following example uses the Selected property to move selected items in the lstSource list box to the lstDestination list box. The lstDestination list box's RowSourceType property is set to Value List and the control's RowSource property is constructed from all the selected items in the lstSource control. The lstSource list box's MultiSelect property is set to Extended. The CopySelected( ) procedure is called from the cmdCopyItem command button.

Private Sub cmdCopyItem_Click()
CopySelected Me
End Sub

Public Sub CopySelected(ByRef frm As Form)

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = frm!lstSource
Set ctlDest = frm!lstDestination

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Reset destination control's RowSource property.
ctlDest.RowSource = ""
ctlDest.RowSource = strItems

Set ctlSource = Nothing
Set ctlDest = Nothing

End Sub
Oct 8 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.