I really need help with this...Ive been struggling forever and just need to complete this....
I am attempting to make a Form that sets criteria for a query that when it all works correctly well eventually export to Excel. I am mostly getting stuck trying to Add "All" to my dropdown choices. I have googled for days on this and all it does is confuse me. Here is what I have so far for selection on the form:
cboAgent: unbound Drop down box to select an Agent or All Agents.
Row Source is qryAgentUnionAll (SELECT AgentLastName & ", " & AgentFirstName,pkAgentID FROM tblAgentDetail UNION SELECT '(All)', null FROM tblAgentDetail
Bound Column: 2
Number of Columns: 2
Column Widths 2.0; 0
**This works. When I go to my qryListExport, under fkAgentID from TablePolicySales I have this criteria: [Forms]![frmSelectToExport]![cboAgent] Or [Forms]![frmSelectToExport]![cboAgent] Is Null
When I select All in the combo box, the word all does not stay there but the query runs and All agents show up.
cboCounty: unbound drop down box to Select specific County or All Counties
RowSource is qryCountyUnionAll (SELECT CountyName, pkCountyID FROM tblCounty UNION SELECT '(All)' , null FROM tblCounty
Bound Column: 2
Number of Columns: 2
Column Widths 2.0; 0
**This works. When I go to my qryListExport, under fkCountyID from TableAddresses I have this criteria: [Forms]![frmSelectToExport]![cboCounty] Or [Forms]![frmSelectToExport]![cboCounty] Is Null
When I select All in the combo box, the word all does not stay there but the query runs and All counties show up.
cboCompany--> this is where the problem starts. I need this box to do two things. It needs to select company or All companies, then based on that selection, limit the choices in the next( and last) combo box.
RowSource: qryUnionCompanyAll (SELECT Company, pkInsuranceCompanyID FROM tblInsuranceCompanies UNION SELECT '(All)' , null FROM tblInsuranceCompaniesBound Column: 2
Number of Columns: 2
Column Widths 2.0; 0
**This works if I select a specific company. Its AfterUpdate event is this code:
What doesn't work is if I select ALL in Company. I am assuming that is because I don't have an ALL in my cboSelectPlanType row source. Thats row source is based on the code above and my table PlanType.
I do have a query named qryPlanTypeUnionAll. That allows for an All choice but I am afraid to change anything in case the whole thing stops working.
Can anyone tell me how to fix this?
I am using Access 2007. I am an intermediate beginner, I can follow along figuring out what most vba is trying to say but once it gets complicated, I have to guess too much and resort to googling. I di not write all the code above, I had help with the unions and after update event.
The query will return results that combine info from several tables. I know all my tables and relationships are correct.
I'd appreciate any help I could get on this.
Thank you so much
I am attempting to make a Form that sets criteria for a query that when it all works correctly well eventually export to Excel. I am mostly getting stuck trying to Add "All" to my dropdown choices. I have googled for days on this and all it does is confuse me. Here is what I have so far for selection on the form:
cboAgent: unbound Drop down box to select an Agent or All Agents.
Row Source is qryAgentUnionAll (SELECT AgentLastName & ", " & AgentFirstName,pkAgentID FROM tblAgentDetail UNION SELECT '(All)', null FROM tblAgentDetail
Bound Column: 2
Number of Columns: 2
Column Widths 2.0; 0
**This works. When I go to my qryListExport, under fkAgentID from TablePolicySales I have this criteria: [Forms]![frmSelectToExport]![cboAgent] Or [Forms]![frmSelectToExport]![cboAgent] Is Null
When I select All in the combo box, the word all does not stay there but the query runs and All agents show up.
cboCounty: unbound drop down box to Select specific County or All Counties
RowSource is qryCountyUnionAll (SELECT CountyName, pkCountyID FROM tblCounty UNION SELECT '(All)' , null FROM tblCounty
Bound Column: 2
Number of Columns: 2
Column Widths 2.0; 0
**This works. When I go to my qryListExport, under fkCountyID from TableAddresses I have this criteria: [Forms]![frmSelectToExport]![cboCounty] Or [Forms]![frmSelectToExport]![cboCounty] Is Null
When I select All in the combo box, the word all does not stay there but the query runs and All counties show up.
cboCompany--> this is where the problem starts. I need this box to do two things. It needs to select company or All companies, then based on that selection, limit the choices in the next( and last) combo box.
RowSource: qryUnionCompanyAll (SELECT Company, pkInsuranceCompanyID FROM tblInsuranceCompanies UNION SELECT '(All)' , null FROM tblInsuranceCompaniesBound Column: 2
Number of Columns: 2
Column Widths 2.0; 0
**This works if I select a specific company. Its AfterUpdate event is this code:
Code:
Private Sub cboSelectCompany_AfterUpdate()
Dim strSQL As String
Dim strWhere As String
strWhere = "WHERE 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
What doesn't work is if I select ALL in Company. I am assuming that is because I don't have an ALL in my cboSelectPlanType row source. Thats row source is based on the code above and my table PlanType.
I do have a query named qryPlanTypeUnionAll. That allows for an All choice but I am afraid to change anything in case the whole thing stops working.
Can anyone tell me how to fix this?
I am using Access 2007. I am an intermediate beginner, I can follow along figuring out what most vba is trying to say but once it gets complicated, I have to guess too much and resort to googling. I di not write all the code above, I had help with the unions and after update event.
The query will return results that combine info from several tables. I know all my tables and relationships are correct.
I'd appreciate any help I could get on this.
Thank you so much