I have a selection form to filter criteria and open a query (then export to excel). I thought I had it working properly but it appears not.
I have three combo boxes and one list box. The 3rd combo box (cboCompany) limits the choices to only that companies plans in the list box. (cboSelectPlanType)<<I changed it from a combo box to a list box, hence the improper naming. Anyway, I created a text box that was to be invisible and where the selections would be and then run the query. It just shows all values instead of the one selected or none depending on what criteria I use. Not sure what info to supply but here's what I have for the after update event of the list box:
I did not write the code, I did a lot of googling and found it, but it works to put the criteria into the text box (txtSelected)
In my query, under Table: tblInsuranceCompanyPlanLnk Field: fkInsurancePlanTypeID I have the following criteriapointing to the list box, which I suspect is the problem:
The list box does show the fkInsurancePlanTypeID--or at least I believe it does, it shows the ID number followed by a comma then the next ID number. Do I have to say something in my criteria to say there are multiple values? I tried setting that criteria to the list box, but that returned everything. How I have it set now returns nothing.
As additional information, below is the after update event from cboCompany that populates the list box (also didn't write that all myself, part of it was already in the DB).
Thank you for any help you can provide.
I have three combo boxes and one list box. The 3rd combo box (cboCompany) limits the choices to only that companies plans in the list box. (cboSelectPlanType)<<I changed it from a combo box to a list box, hence the improper naming. Anyway, I created a text box that was to be invisible and where the selections would be and then run the query. It just shows all values instead of the one selected or none depending on what criteria I use. Not sure what info to supply but here's what I have for the after update event of the list box:
Code:
Dim lst As Access.ListBox
Dim varItem As Variant
Dim strBuild As String
Set lst = Forms![frmSelectToExport]![cboSelectPlanType]
If lst.ItemsSelected.Count > 0 Then
For Each varItem In lst.ItemsSelected
strBuild = strBuild & lst.ItemData(varItem) & ","
Next varItem
Me![txtSelected] = Left$(strBuild, Len(strBuild) - 1)
End If
End Sub
I did not write the code, I did a lot of googling and found it, but it works to put the criteria into the text box (txtSelected)
In my query, under Table: tblInsuranceCompanyPlanLnk Field: fkInsurancePlanTypeID I have the following criteriapointing to the list box, which I suspect is the problem:
Code:
[Forms]![frmSelectToExport]![txtSelected]
The list box does show the fkInsurancePlanTypeID--or at least I believe it does, it shows the ID number followed by a comma then the next ID number. Do I have to say something in my criteria to say there are multiple values? I tried setting that criteria to the list box, but that returned everything. How I have it set now returns nothing.
As additional information, below is the after update event from cboCompany that populates the list box (also didn't write that all myself, part of it was already in the DB).
Thank you for any help you can provide.
Code:
Private Sub cboSelectCompany_AfterUpdate()
Dim strSQL As String
Dim strWhere As String
strWhere = " WHERE (1=1)"
If (IsNull(Me.cboSelectCompany) = False) Then strWhere = strWhere & " AND (fkInsuranceCompanyID=" & Me.cboSelectCompany & ")"
strSQL = "SELECT DISTINCT tblInsurancePlanType.pkInsurancePlanTypeID, tblInsurancePlanType.PlanType " & _
"FROM tblInsurancePlanType LEFT JOIN tblInsuranceCompanyPlanLink ON tblInsurancePlanType.pkInsurancePlanTypeID = tblInsuranceCompanyPlanLink.fkInsurancePlanTypeID " & _
strWhere & _
"ORDER BY tblInsurancePlanType.PlanType;"
Me.cboSelectPlanType.RowSource = strSQL
Me.cboSelectPlanType.Requery
Me.cboSelectPlanType = 0
End Sub