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.
:
Is your list of doctors by selection going to be a combo, a list box, or a
subform?
--
Dave Hargis, Microsoft Access MVP
:
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.
:
Argh! Typo
Dim strRowSouce As String
should be
Dim strRowSource As String
--
Dave Hargis, Microsoft Access MVP
:
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
:
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
:
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!!
:
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
:
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.
:
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
:
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
:
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
:
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.
