If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Need help with Combo Box?
Sorry, I found the typo and fixed. It works fine only for the first choice
County. When I pick City or Company, I get the list of City or Companies but I do not get a list of doctors by City or Companies. I need to get a list of docs in the option I pick. I found that in my Doctor List source I have the following in the County field: [forms]![frm DOC Tracking]![PickCounty] How do I fix to enable City and Company? If I delete, it will not return list of docs in my "Doc List" box in my Form. Thank you once again. "Klatuu" wrote: Argh! Typo Dim strRowSouce As String should be Dim strRowSource As String -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Getting the following error Message when picking an option - it takes me to the code (enclosed below) and highlights the "strRowSource" right below Case 1 'County: What does that mean? What is not right? "Compile error: Variable Not Defined" Private Sub optsearch_AfterUpdate() Dim strRowSouce As String Select Case Me.optsearch Case 1 'County strRowSource = "SELECT [Counties] FROM qry_Counties;" Case 2 'City strRowSource = "SELECT [Cities] FROM qry_Cities;" Case 3 'Company strRowSource = "SELECT [Company] FROM qry_Company;" End Select Me.PickCounty.RowSource = strRowSource End Sub and str Row Source is highlighted "Klatuu" wrote: Yes, you are on the right track. I would set the query for the most common choice as the row source in design view. And, set the Default Value of the option group to match that selection. In other words, Use the County query as the row source in design view for the combo and set the default value of the option group that is the value for County. Now when you assign a new row source in the option group's after update, it will automatically requery the combo with the list using the new row source. -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you for responding. I already have the three queries for the three options. I was on the right track. I can do the After Update event of the option group. However, not sure how the Row Source of Combo1 is to read? This is how it reads now: SELECT [qry Counties].[COUNTY] FROM [qry Counties] GROUP BY [qry Counties].[COUNTY] ORDER BY [qry Counties].[COUNTY]; How should it read with the 3 different options? Thank you and this forum - you are a life saver!! "Klatuu" wrote: Ok, got it. What you need to do is to create a query for each of the 3 choices that would be the Row Source of Combo1. Then in the After Update event of the option group, select the row source for the combo: Private Sub opqList_AfterUpdate() Dim strRowSouce As String Select Case Me.opgList Case 1 'County strRowSource = "SELECT [County] FROM CountyTable;" Case 2 'City strRowSource = "SELECT [City] FROM CityTable;" Case 3 'Company strRowSource = "SELECT [Company] FROM CompanyTable;" End Select Me.Comb1.RowSource = strRowSource End Sub -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. County, City and Company would be my options in the Option Group. Whichever one they choose, I would like to see the list in the Combo Box 1 (list of counties, cities or companies) and then be able to choose/select from the Combo Box 1 and see the list of docs results in the Doc List. I hope this makes better sense. Thank you once again. "Klatuu" wrote: Thanks for the info. I do have a question. What do you mean by County, City, and Company? Do you mean you want them ordered by one of the 3 or is it you only want to see those that match up to one of the 3? and If you want to list only those for one of the 3, where do you select which it is you want to see? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. I have included SQL for the Combo Box, the list Box, Main form and subform. What I would like is an Option Group Box that lists County, City and Company. This would enable the user to choose how she wants to see the data. Therefore, what ever option is chosen. The Doc list will list the doctors by the option chosen. As I mentioned below. This works fine for the County but I need to add City and Company. I don't know how to do this without messing up what I have already. I would appreciate any help. Thank you. Combo Box 1 SELECT [qry Counties].[County] FROM [qry Counties] GROUP BY [qry Counties].[County] ORDER BY [qry Counties].[County]; Doc List SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].DOCID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOCCNTY])=[forms]![frm DOC Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH]; MainForm SELECT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[Next MLP], [tblOFCTRKG].[Company], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].[DOCSt], [tblOFCTRKG].[DOC ZIP], [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].[Add ID], FROM [tblOFCTRKG]; SubForm DOCID "Klatuu" wrote: Sure, you just modify the criteria on some of your controls and maybe add antother one or two to get what you want. Now, for an answer that will actually help, perhaps you could post a bit more detail -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. |
#12
|
|||
|
|||
Need help with Combo Box?
Is your list of doctors by selection going to be a combo, a list box, or a
subform? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Sorry, I found the typo and fixed. It works fine only for the first choice County. When I pick City or Company, I get the list of City or Companies but I do not get a list of doctors by City or Companies. I need to get a list of docs in the option I pick. I found that in my Doctor List source I have the following in the County field: [forms]![frm DOC Tracking]![PickCounty] How do I fix to enable City and Company? If I delete, it will not return list of docs in my "Doc List" box in my Form. Thank you once again. "Klatuu" wrote: Argh! Typo Dim strRowSouce As String should be Dim strRowSource As String -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Getting the following error Message when picking an option - it takes me to the code (enclosed below) and highlights the "strRowSource" right below Case 1 'County: What does that mean? What is not right? "Compile error: Variable Not Defined" Private Sub optsearch_AfterUpdate() Dim strRowSouce As String Select Case Me.optsearch Case 1 'County strRowSource = "SELECT [Counties] FROM qry_Counties;" Case 2 'City strRowSource = "SELECT [Cities] FROM qry_Cities;" Case 3 'Company strRowSource = "SELECT [Company] FROM qry_Company;" End Select Me.PickCounty.RowSource = strRowSource End Sub and str Row Source is highlighted "Klatuu" wrote: Yes, you are on the right track. I would set the query for the most common choice as the row source in design view. And, set the Default Value of the option group to match that selection. In other words, Use the County query as the row source in design view for the combo and set the default value of the option group that is the value for County. Now when you assign a new row source in the option group's after update, it will automatically requery the combo with the list using the new row source. -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you for responding. I already have the three queries for the three options. I was on the right track. I can do the After Update event of the option group. However, not sure how the Row Source of Combo1 is to read? This is how it reads now: SELECT [qry Counties].[COUNTY] FROM [qry Counties] GROUP BY [qry Counties].[COUNTY] ORDER BY [qry Counties].[COUNTY]; How should it read with the 3 different options? Thank you and this forum - you are a life saver!! "Klatuu" wrote: Ok, got it. What you need to do is to create a query for each of the 3 choices that would be the Row Source of Combo1. Then in the After Update event of the option group, select the row source for the combo: Private Sub opqList_AfterUpdate() Dim strRowSouce As String Select Case Me.opgList Case 1 'County strRowSource = "SELECT [County] FROM CountyTable;" Case 2 'City strRowSource = "SELECT [City] FROM CityTable;" Case 3 'Company strRowSource = "SELECT [Company] FROM CompanyTable;" End Select Me.Comb1.RowSource = strRowSource End Sub -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. County, City and Company would be my options in the Option Group. Whichever one they choose, I would like to see the list in the Combo Box 1 (list of counties, cities or companies) and then be able to choose/select from the Combo Box 1 and see the list of docs results in the Doc List. I hope this makes better sense. Thank you once again. "Klatuu" wrote: Thanks for the info. I do have a question. What do you mean by County, City, and Company? Do you mean you want them ordered by one of the 3 or is it you only want to see those that match up to one of the 3? and If you want to list only those for one of the 3, where do you select which it is you want to see? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. I have included SQL for the Combo Box, the list Box, Main form and subform. What I would like is an Option Group Box that lists County, City and Company. This would enable the user to choose how she wants to see the data. Therefore, what ever option is chosen. The Doc list will list the doctors by the option chosen. As I mentioned below. This works fine for the County but I need to add City and Company. I don't know how to do this without messing up what I have already. I would appreciate any help. Thank you. Combo Box 1 SELECT [qry Counties].[County] FROM [qry Counties] GROUP BY [qry Counties].[County] ORDER BY [qry Counties].[County]; Doc List SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].DOCID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOCCNTY])=[forms]![frm DOC Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH]; MainForm SELECT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[Next MLP], [tblOFCTRKG].[Company], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].[DOCSt], [tblOFCTRKG].[DOC ZIP], [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].[Add ID], FROM [tblOFCTRKG]; SubForm DOCID "Klatuu" wrote: Sure, you just modify the criteria on some of your controls and maybe add antother one or two to get what you want. Now, for an answer that will actually help, perhaps you could post a bit more detail -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. |
#13
|
|||
|
|||
Need help with Combo Box?
My list of doctors by selection is currently a combo box that lists the docs.
I have a subform that lists the pts associated with the doctor that is selected. Can this be fixed? Thank you. "Klatuu" wrote: Is your list of doctors by selection going to be a combo, a list box, or a subform? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Sorry, I found the typo and fixed. It works fine only for the first choice County. When I pick City or Company, I get the list of City or Companies but I do not get a list of doctors by City or Companies. I need to get a list of docs in the option I pick. I found that in my Doctor List source I have the following in the County field: [forms]![frm DOC Tracking]![PickCounty] How do I fix to enable City and Company? If I delete, it will not return list of docs in my "Doc List" box in my Form. Thank you once again. "Klatuu" wrote: Argh! Typo Dim strRowSouce As String should be Dim strRowSource As String -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Getting the following error Message when picking an option - it takes me to the code (enclosed below) and highlights the "strRowSource" right below Case 1 'County: What does that mean? What is not right? "Compile error: Variable Not Defined" Private Sub optsearch_AfterUpdate() Dim strRowSouce As String Select Case Me.optsearch Case 1 'County strRowSource = "SELECT [Counties] FROM qry_Counties;" Case 2 'City strRowSource = "SELECT [Cities] FROM qry_Cities;" Case 3 'Company strRowSource = "SELECT [Company] FROM qry_Company;" End Select Me.PickCounty.RowSource = strRowSource End Sub and str Row Source is highlighted "Klatuu" wrote: Yes, you are on the right track. I would set the query for the most common choice as the row source in design view. And, set the Default Value of the option group to match that selection. In other words, Use the County query as the row source in design view for the combo and set the default value of the option group that is the value for County. Now when you assign a new row source in the option group's after update, it will automatically requery the combo with the list using the new row source. -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you for responding. I already have the three queries for the three options. I was on the right track. I can do the After Update event of the option group. However, not sure how the Row Source of Combo1 is to read? This is how it reads now: SELECT [qry Counties].[COUNTY] FROM [qry Counties] GROUP BY [qry Counties].[COUNTY] ORDER BY [qry Counties].[COUNTY]; How should it read with the 3 different options? Thank you and this forum - you are a life saver!! "Klatuu" wrote: Ok, got it. What you need to do is to create a query for each of the 3 choices that would be the Row Source of Combo1. Then in the After Update event of the option group, select the row source for the combo: Private Sub opqList_AfterUpdate() Dim strRowSouce As String Select Case Me.opgList Case 1 'County strRowSource = "SELECT [County] FROM CountyTable;" Case 2 'City strRowSource = "SELECT [City] FROM CityTable;" Case 3 'Company strRowSource = "SELECT [Company] FROM CompanyTable;" End Select Me.Comb1.RowSource = strRowSource End Sub -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. County, City and Company would be my options in the Option Group. Whichever one they choose, I would like to see the list in the Combo Box 1 (list of counties, cities or companies) and then be able to choose/select from the Combo Box 1 and see the list of docs results in the Doc List. I hope this makes better sense. Thank you once again. "Klatuu" wrote: Thanks for the info. I do have a question. What do you mean by County, City, and Company? Do you mean you want them ordered by one of the 3 or is it you only want to see those that match up to one of the 3? and If you want to list only those for one of the 3, where do you select which it is you want to see? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. I have included SQL for the Combo Box, the list Box, Main form and subform. What I would like is an Option Group Box that lists County, City and Company. This would enable the user to choose how she wants to see the data. Therefore, what ever option is chosen. The Doc list will list the doctors by the option chosen. As I mentioned below. This works fine for the County but I need to add City and Company. I don't know how to do this without messing up what I have already. I would appreciate any help. Thank you. Combo Box 1 SELECT [qry Counties].[County] FROM [qry Counties] GROUP BY [qry Counties].[County] ORDER BY [qry Counties].[County]; Doc List SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].DOCID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOCCNTY])=[forms]![frm DOC Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH]; MainForm SELECT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[Next MLP], [tblOFCTRKG].[Company], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].[DOCSt], [tblOFCTRKG].[DOC ZIP], [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].[Add ID], FROM [tblOFCTRKG]; SubForm DOCID "Klatuu" wrote: Sure, you just modify the criteria on some of your controls and maybe add antother one or two to get what you want. Now, for an answer that will actually help, perhaps you could post a bit more detail -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. |
#14
|
|||
|
|||
Need help with Combo Box?
Okay, here we go again with the row source thing.
You Doctor comb row source also needs to be changed each time you change the city, county, company option. It needs to be filtered based on combo1, but since combo1 changes, you need 3 version of the row source for the doctor combo. You can do that in the code you already have in the After Update of the option group. Then all you need is to requery the doctor combo in the after update event of combo1 so it will then show the list of doctors based on the selection in the combo. -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: My list of doctors by selection is currently a combo box that lists the docs. I have a subform that lists the pts associated with the doctor that is selected. Can this be fixed? Thank you. "Klatuu" wrote: Is your list of doctors by selection going to be a combo, a list box, or a subform? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Sorry, I found the typo and fixed. It works fine only for the first choice County. When I pick City or Company, I get the list of City or Companies but I do not get a list of doctors by City or Companies. I need to get a list of docs in the option I pick. I found that in my Doctor List source I have the following in the County field: [forms]![frm DOC Tracking]![PickCounty] How do I fix to enable City and Company? If I delete, it will not return list of docs in my "Doc List" box in my Form. Thank you once again. "Klatuu" wrote: Argh! Typo Dim strRowSouce As String should be Dim strRowSource As String -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Getting the following error Message when picking an option - it takes me to the code (enclosed below) and highlights the "strRowSource" right below Case 1 'County: What does that mean? What is not right? "Compile error: Variable Not Defined" Private Sub optsearch_AfterUpdate() Dim strRowSouce As String Select Case Me.optsearch Case 1 'County strRowSource = "SELECT [Counties] FROM qry_Counties;" Case 2 'City strRowSource = "SELECT [Cities] FROM qry_Cities;" Case 3 'Company strRowSource = "SELECT [Company] FROM qry_Company;" End Select Me.PickCounty.RowSource = strRowSource End Sub and str Row Source is highlighted "Klatuu" wrote: Yes, you are on the right track. I would set the query for the most common choice as the row source in design view. And, set the Default Value of the option group to match that selection. In other words, Use the County query as the row source in design view for the combo and set the default value of the option group that is the value for County. Now when you assign a new row source in the option group's after update, it will automatically requery the combo with the list using the new row source. -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you for responding. I already have the three queries for the three options. I was on the right track. I can do the After Update event of the option group. However, not sure how the Row Source of Combo1 is to read? This is how it reads now: SELECT [qry Counties].[COUNTY] FROM [qry Counties] GROUP BY [qry Counties].[COUNTY] ORDER BY [qry Counties].[COUNTY]; How should it read with the 3 different options? Thank you and this forum - you are a life saver!! "Klatuu" wrote: Ok, got it. What you need to do is to create a query for each of the 3 choices that would be the Row Source of Combo1. Then in the After Update event of the option group, select the row source for the combo: Private Sub opqList_AfterUpdate() Dim strRowSouce As String Select Case Me.opgList Case 1 'County strRowSource = "SELECT [County] FROM CountyTable;" Case 2 'City strRowSource = "SELECT [City] FROM CityTable;" Case 3 'Company strRowSource = "SELECT [Company] FROM CompanyTable;" End Select Me.Comb1.RowSource = strRowSource End Sub -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. County, City and Company would be my options in the Option Group. Whichever one they choose, I would like to see the list in the Combo Box 1 (list of counties, cities or companies) and then be able to choose/select from the Combo Box 1 and see the list of docs results in the Doc List. I hope this makes better sense. Thank you once again. "Klatuu" wrote: Thanks for the info. I do have a question. What do you mean by County, City, and Company? Do you mean you want them ordered by one of the 3 or is it you only want to see those that match up to one of the 3? and If you want to list only those for one of the 3, where do you select which it is you want to see? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. I have included SQL for the Combo Box, the list Box, Main form and subform. What I would like is an Option Group Box that lists County, City and Company. This would enable the user to choose how she wants to see the data. Therefore, what ever option is chosen. The Doc list will list the doctors by the option chosen. As I mentioned below. This works fine for the County but I need to add City and Company. I don't know how to do this without messing up what I have already. I would appreciate any help. Thank you. Combo Box 1 SELECT [qry Counties].[County] FROM [qry Counties] GROUP BY [qry Counties].[County] ORDER BY [qry Counties].[County]; Doc List SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].DOCID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOCCNTY])=[forms]![frm DOC Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH]; MainForm SELECT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[Next MLP], [tblOFCTRKG].[Company], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].[DOCSt], [tblOFCTRKG].[DOC ZIP], [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].[Add ID], FROM [tblOFCTRKG]; SubForm DOCID "Klatuu" wrote: Sure, you just modify the criteria on some of your controls and maybe add antother one or two to get what you want. Now, for an answer that will actually help, perhaps you could post a bit more detail -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. |
#15
|
|||
|
|||
Need help with Combo Box?
I am sorry but my brain is fried. Can you please give me a sample of the row
source for the doctor combo? Thank you. "Klatuu" wrote: Okay, here we go again with the row source thing. You Doctor comb row source also needs to be changed each time you change the city, county, company option. It needs to be filtered based on combo1, but since combo1 changes, you need 3 version of the row source for the doctor combo. You can do that in the code you already have in the After Update of the option group. Then all you need is to requery the doctor combo in the after update event of combo1 so it will then show the list of doctors based on the selection in the combo. -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: My list of doctors by selection is currently a combo box that lists the docs. I have a subform that lists the pts associated with the doctor that is selected. Can this be fixed? Thank you. "Klatuu" wrote: Is your list of doctors by selection going to be a combo, a list box, or a subform? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Sorry, I found the typo and fixed. It works fine only for the first choice County. When I pick City or Company, I get the list of City or Companies but I do not get a list of doctors by City or Companies. I need to get a list of docs in the option I pick. I found that in my Doctor List source I have the following in the County field: [forms]![frm DOC Tracking]![PickCounty] How do I fix to enable City and Company? If I delete, it will not return list of docs in my "Doc List" box in my Form. Thank you once again. "Klatuu" wrote: Argh! Typo Dim strRowSouce As String should be Dim strRowSource As String -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Getting the following error Message when picking an option - it takes me to the code (enclosed below) and highlights the "strRowSource" right below Case 1 'County: What does that mean? What is not right? "Compile error: Variable Not Defined" Private Sub optsearch_AfterUpdate() Dim strRowSouce As String Select Case Me.optsearch Case 1 'County strRowSource = "SELECT [Counties] FROM qry_Counties;" Case 2 'City strRowSource = "SELECT [Cities] FROM qry_Cities;" Case 3 'Company strRowSource = "SELECT [Company] FROM qry_Company;" End Select Me.PickCounty.RowSource = strRowSource End Sub and str Row Source is highlighted "Klatuu" wrote: Yes, you are on the right track. I would set the query for the most common choice as the row source in design view. And, set the Default Value of the option group to match that selection. In other words, Use the County query as the row source in design view for the combo and set the default value of the option group that is the value for County. Now when you assign a new row source in the option group's after update, it will automatically requery the combo with the list using the new row source. -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you for responding. I already have the three queries for the three options. I was on the right track. I can do the After Update event of the option group. However, not sure how the Row Source of Combo1 is to read? This is how it reads now: SELECT [qry Counties].[COUNTY] FROM [qry Counties] GROUP BY [qry Counties].[COUNTY] ORDER BY [qry Counties].[COUNTY]; How should it read with the 3 different options? Thank you and this forum - you are a life saver!! "Klatuu" wrote: Ok, got it. What you need to do is to create a query for each of the 3 choices that would be the Row Source of Combo1. Then in the After Update event of the option group, select the row source for the combo: Private Sub opqList_AfterUpdate() Dim strRowSouce As String Select Case Me.opgList Case 1 'County strRowSource = "SELECT [County] FROM CountyTable;" Case 2 'City strRowSource = "SELECT [City] FROM CityTable;" Case 3 'Company strRowSource = "SELECT [Company] FROM CompanyTable;" End Select Me.Comb1.RowSource = strRowSource End Sub -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. County, City and Company would be my options in the Option Group. Whichever one they choose, I would like to see the list in the Combo Box 1 (list of counties, cities or companies) and then be able to choose/select from the Combo Box 1 and see the list of docs results in the Doc List. I hope this makes better sense. Thank you once again. "Klatuu" wrote: Thanks for the info. I do have a question. What do you mean by County, City, and Company? Do you mean you want them ordered by one of the 3 or is it you only want to see those that match up to one of the 3? and If you want to list only those for one of the 3, where do you select which it is you want to see? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. I have included SQL for the Combo Box, the list Box, Main form and subform. What I would like is an Option Group Box that lists County, City and Company. This would enable the user to choose how she wants to see the data. Therefore, what ever option is chosen. The Doc list will list the doctors by the option chosen. As I mentioned below. This works fine for the County but I need to add City and Company. I don't know how to do this without messing up what I have already. I would appreciate any help. Thank you. Combo Box 1 SELECT [qry Counties].[County] FROM [qry Counties] GROUP BY [qry Counties].[County] ORDER BY [qry Counties].[County]; Doc List SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].DOCID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOCCNTY])=[forms]![frm DOC Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH]; MainForm SELECT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[Next MLP], [tblOFCTRKG].[Company], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].[DOCSt], [tblOFCTRKG].[DOC ZIP], [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].[Add ID], FROM [tblOFCTRKG]; SubForm DOCID "Klatuu" wrote: Sure, you just modify the criteria on some of your controls and maybe add antother one or two to get what you want. Now, for an answer that will actually help, perhaps you could post a bit more detail -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. |
#16
|
|||
|
|||
Need help with Combo Box?
I would be happy to, but I don't know your data well enough. Basically, you
will need 3 different version depending on which list you are using in Combo1. -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: I am sorry but my brain is fried. Can you please give me a sample of the row source for the doctor combo? Thank you. "Klatuu" wrote: Okay, here we go again with the row source thing. You Doctor comb row source also needs to be changed each time you change the city, county, company option. It needs to be filtered based on combo1, but since combo1 changes, you need 3 version of the row source for the doctor combo. You can do that in the code you already have in the After Update of the option group. Then all you need is to requery the doctor combo in the after update event of combo1 so it will then show the list of doctors based on the selection in the combo. -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: My list of doctors by selection is currently a combo box that lists the docs. I have a subform that lists the pts associated with the doctor that is selected. Can this be fixed? Thank you. "Klatuu" wrote: Is your list of doctors by selection going to be a combo, a list box, or a subform? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Sorry, I found the typo and fixed. It works fine only for the first choice County. When I pick City or Company, I get the list of City or Companies but I do not get a list of doctors by City or Companies. I need to get a list of docs in the option I pick. I found that in my Doctor List source I have the following in the County field: [forms]![frm DOC Tracking]![PickCounty] How do I fix to enable City and Company? If I delete, it will not return list of docs in my "Doc List" box in my Form. Thank you once again. "Klatuu" wrote: Argh! Typo Dim strRowSouce As String should be Dim strRowSource As String -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Getting the following error Message when picking an option - it takes me to the code (enclosed below) and highlights the "strRowSource" right below Case 1 'County: What does that mean? What is not right? "Compile error: Variable Not Defined" Private Sub optsearch_AfterUpdate() Dim strRowSouce As String Select Case Me.optsearch Case 1 'County strRowSource = "SELECT [Counties] FROM qry_Counties;" Case 2 'City strRowSource = "SELECT [Cities] FROM qry_Cities;" Case 3 'Company strRowSource = "SELECT [Company] FROM qry_Company;" End Select Me.PickCounty.RowSource = strRowSource End Sub and str Row Source is highlighted "Klatuu" wrote: Yes, you are on the right track. I would set the query for the most common choice as the row source in design view. And, set the Default Value of the option group to match that selection. In other words, Use the County query as the row source in design view for the combo and set the default value of the option group that is the value for County. Now when you assign a new row source in the option group's after update, it will automatically requery the combo with the list using the new row source. -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you for responding. I already have the three queries for the three options. I was on the right track. I can do the After Update event of the option group. However, not sure how the Row Source of Combo1 is to read? This is how it reads now: SELECT [qry Counties].[COUNTY] FROM [qry Counties] GROUP BY [qry Counties].[COUNTY] ORDER BY [qry Counties].[COUNTY]; How should it read with the 3 different options? Thank you and this forum - you are a life saver!! "Klatuu" wrote: Ok, got it. What you need to do is to create a query for each of the 3 choices that would be the Row Source of Combo1. Then in the After Update event of the option group, select the row source for the combo: Private Sub opqList_AfterUpdate() Dim strRowSouce As String Select Case Me.opgList Case 1 'County strRowSource = "SELECT [County] FROM CountyTable;" Case 2 'City strRowSource = "SELECT [City] FROM CityTable;" Case 3 'Company strRowSource = "SELECT [Company] FROM CompanyTable;" End Select Me.Comb1.RowSource = strRowSource End Sub -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. County, City and Company would be my options in the Option Group. Whichever one they choose, I would like to see the list in the Combo Box 1 (list of counties, cities or companies) and then be able to choose/select from the Combo Box 1 and see the list of docs results in the Doc List. I hope this makes better sense. Thank you once again. "Klatuu" wrote: Thanks for the info. I do have a question. What do you mean by County, City, and Company? Do you mean you want them ordered by one of the 3 or is it you only want to see those that match up to one of the 3? and If you want to list only those for one of the 3, where do you select which it is you want to see? -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: Thank you so much for responding. I have included SQL for the Combo Box, the list Box, Main form and subform. What I would like is an Option Group Box that lists County, City and Company. This would enable the user to choose how she wants to see the data. Therefore, what ever option is chosen. The Doc list will list the doctors by the option chosen. As I mentioned below. This works fine for the County but I need to add City and Company. I don't know how to do this without messing up what I have already. I would appreciate any help. Thank you. Combo Box 1 SELECT [qry Counties].[County] FROM [qry Counties] GROUP BY [qry Counties].[County] ORDER BY [qry Counties].[County]; Doc List SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].DOCID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].St, [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOCCNTY])=[forms]![frm DOC Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[DOCPH]; MainForm SELECT [tblOFCTRKG].ID, [tblOFCTRKG].[DOCID], [tblOFCTRKG].[DOCNM], [tblOFCTRKG].[Next MLP], [tblOFCTRKG].[Company], [tblOFCTRKG].[DOCPH], [tblOFCTRKG].[PRVDR FX], [tblOFCTRKG].[DOCADD], [tblOFCTRKG].[DOCADD2], [tblOFCTRKG].[DOCCITY], [tblOFCTRKG].[DOCSt], [tblOFCTRKG].[DOC ZIP], [tblOFCTRKG].[DOCCNTY], [tblOFCTRKG].[Add ID], FROM [tblOFCTRKG]; SubForm DOCID "Klatuu" wrote: Sure, you just modify the criteria on some of your controls and maybe add antother one or two to get what you want. Now, for an answer that will actually help, perhaps you could post a bit more detail -- Dave Hargis, Microsoft Access MVP "TotallyConfused" wrote: I would appreciate any help with this. I currently have a form with two combo boxes and a subform. The first combo box lists counties and the second box lists doctors in selected county. After selecting county, doc the subform lists pts for this doc. All this works fine. However, I need to add a couple of more filters. I am stuck and would like to know how to do this. I don't want to mess up what I already have. How can I incorporate a couple more filters? I thought maybe adding an option box to the form????? Can someone please help me to accomplish this? Thank you. |
|
Thread Tools | |
Display Modes | |
|
|