Code for using a list box for criteria in a form

[tomnsd]

I am an Access novice, but I want to refresh a subform on a main form using a query that looks at a list box and date fields on the main form.



Here are the names/queiries, forms, controls involved:
Table: Opportunity
MainForm: SearchMain
SubFrom: ProposalsSub
Query: Proposals2
Mainform Listbox Control Name: FDisposition
Mainform Date Control Box: RDateS and RDateF

The query proposals2 is the control source for the Form. I have the list Box set up for multiselect. I found some existing code to get me started but could not get it to work.

Private Sub Command12_Click()
Dim db As DAO.DatabaseDim qdf As DAO.QueryDefDim varItem As VariantDim strCriteria As StringDim strSQL As String
Set db = CurrentDb()Set qdf = db.QueryDefs("Proposals2")'Now for the code that reads the user's selection from the listbox:For Each varItem In Me!FDisposition.ItemsSelected   strCriteria = strCriteria & ",'" & Me!FDisposition.ItemData(varItem) & "'"Next varItemstrCriteria = Right(strCriteria, Len(strCriteria) - 1)'The criteria string is now ready to be incorporated into an SQL statement. For this example I have kept the SQL statement simple. You can see how the criteria string variable is added between the brackets of the IN( ---string goes here--- ) expression:strSQL = "SELECT * FROM tblOpportunity " & _         "WHERE tblOpportunity.Disposition IN(" & strCriteria & ");"

DoCmd.OpenQuery "Proposals2"End Sub

Any help would be greatly appreciated.

[pbaldy]

That code is basically unreadable like that. In any case, if the form is based on that query, and you've updated the SQL of it, the last line would likely be:

Me.Requery

[alcapps]

you can try.. Go to the query in the subform. in crieteria field and right click use build. select loaded forms pick the main form of your subform and click on the combo control paste it.. do as many combo's you have with builder. then save the query. Goto to all your cboboxes and go to afterupdate event and add me.subformcontrol.requery and you will get your result. If you have a database upload it and we can help you make the change.

hope this helps

[pbaldy]

you can try.. Go to the query in the subform. in crieteria field and right click use build. select loaded forms pick the main form of your subform and click on the combo control paste it.. do as many combo's you have with builder. then save the query. Goto to all your cboboxes and go to afterupdate event and add me.subformcontrol.requery and you will get your result.

With a multiselect listbox? I don't think so.

[alcapps]

Actually you can. I have a list in clause where you build an in clause using the list box selected property.

[alcapps]

call these functions and you can get a In clause to use on your query from a list box.

Public Function ListInClause(lst As Control, Optional OneToForceAll As Integer) As String

Dim ListRow As Variant
Dim strList As String
Dim strTemp As String
Dim i As Integer

If IsNumeric(lst.Column(0, 0)) Then
If OneToForceAll = 1 Then
strTemp = "In("
For i = 0 To lst.ListCount - 1
strList = strList & "," & lst.Column(0, i)
Next i
strList = strTemp & Mid(strList, 2) & ")"
ListInClause = strList
Else
strTemp = "In("
For Each ListRow In lst.ItemsSelected
strList = strList & "," & lst.Column(0, ListRow)
Next
strList = strTemp & Mid(strList, 2) & ")"
ListInClause = strList
End If
Else
If OneToForceAll = 1 Then
strTemp = "In("
For i = 0 To lst.ListCount - 1
strList = strList & ",'" & lst.Column(0, i) & "'"
Next i
strList = strTemp & Mid(strList, 2) & ")"
ListInClause = strList
Else
strTemp = "In("
For Each ListRow In lst.ItemsSelected
strList = strList & ",'" & lst.Column(0, ListRow) & "'"
Next
strList = strTemp & Mid(strList, 2) & ")"
ListInClause = strList
End If
End If

End Function
Public Function ListInClauseInt(lst As Control, Optional OneToForceAll As Integer) As String

Dim ListRow As Variant
Dim strList As String
Dim strTemp As String
Dim i As Integer

If OneToForceAll = 1 Then
strTemp = "In("
For i = 0 To lst.ListCount - 1
strList = strList & "," & lst.Column(0, i)
Next i
strList = strTemp & Mid(strList, 2) & ")"
ListInClauseInt = strList
Else
strTemp = "In("
For Each ListRow In lst.ItemsSelected
strList = strList & "," & lst.Column(0, ListRow)
Next
strList = strTemp & Mid(strList, 2) & ")"
ListInClauseInt = strList
End If


End Function

[pbaldy]

Actually you can.

I didn't say you "couldn't" so much as that the instructions you gave weren't appropriate for it. In other words, your response didn't address the issue at hand.

[alcapps]

I see Paul,
I misread the issue thinking it was a combo box.