Combo Box /Row Source Help

  • Thread starter TotallyConfused
  • Start date
T

TotallyConfused

I hope someone can please help me complete this. I have a form with an
Option Group consisting of 3 choices: Counties, Cities, Companies. 2 combo
boxes. Combo box 1 - List the names of the choice picked from the Option
Group. Combo Box 2 - should list the doctors in the choice selected from
Combox box 1. That is my problem. I can't get the Combo box 2 to work.
Below is the code for After Update in the Option Group.

Private Sub optsearch_AfterUpdate()
Dim strRowSource As String
Select Case Me.optsearch
Case 1 'County
strRowSource = "SELECT [qry Counties].[CNTY] FROM [qry Counties]"

Case 2 'City
strRowSource = "SELECT [qry Cities].[City] FROM [qry Cities]"

Case 3 'Group
strRowSource = "SELECT [qry Groups].[Company] FROM [qry Groups]"

End Select

Me.PickCounty.RowSource = strRowSource

End Sub

This is the SQL for Combo Box 2 (DOC List)

SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC
NAME], [tblOFCTRKG].[DOC PH], [tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD],
[tblOFCTRKG].[DOC ADD2], [tblOFCTRKG].[DOC CITY], [tblOFCTRKG].St,
[tblOFCTRKG].[DOC CNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].ID,
[tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC PH],
[tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD], [tblOFCTRKG].[DOC ADD2],
[tblOFCTRKG].[DOC CITY], tblOFCTRKG].St, [tblOFCTRKG].[DOC CNTY],
[tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOC CNTY])=[forms]![frm DOC
Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC
PH];

How can I get the Doc list to list the doc from the selection of Combo Box
1? I have tried everything I can. I would appreciate any help please.
Thank you.
 
J

Jeff Boyce

It sounds like you are saying that your combobox1 (?[PickCounty]) may hold
counties, cities or companies. And then, after a choice in [PickCounty],
you want combobox2 to display doctors in the location (county, city or
company) picked.

First, you'll need to use the AfterUpdate event of [PickCounty] to requery
the combobox2 (?[PickDoctor]), with something like:

Me![PickDoctor].Requery

Then you'll need a query (?perhaps queries) for combobox2 that reflects the
fact that combobox1 does NOT always hold counties. The SQL statement you
provided looks like you are only looking for counties.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
T

TotallyConfused

Thank you for responding. Okay I have 3 queries for Combo box 2 to list by
whatever is chosen in Combo Box 1. How do I write this in the code below?

Jeff Boyce said:
It sounds like you are saying that your combobox1 (?[PickCounty]) may hold
counties, cities or companies. And then, after a choice in [PickCounty],
you want combobox2 to display doctors in the location (county, city or
company) picked.

First, you'll need to use the AfterUpdate event of [PickCounty] to requery
the combobox2 (?[PickDoctor]), with something like:

Me![PickDoctor].Requery

Then you'll need a query (?perhaps queries) for combobox2 that reflects the
fact that combobox1 does NOT always hold counties. The SQL statement you
provided looks like you are only looking for counties.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

TotallyConfused said:
I hope someone can please help me complete this. I have a form with an
Option Group consisting of 3 choices: Counties, Cities, Companies. 2 combo
boxes. Combo box 1 - List the names of the choice picked from the Option
Group. Combo Box 2 - should list the doctors in the choice selected from
Combox box 1. That is my problem. I can't get the Combo box 2 to work.
Below is the code for After Update in the Option Group.

Private Sub optsearch_AfterUpdate()
Dim strRowSource As String
Select Case Me.optsearch
Case 1 'County
strRowSource = "SELECT [qry Counties].[CNTY] FROM [qry Counties]"

Case 2 'City
strRowSource = "SELECT [qry Cities].[City] FROM [qry Cities]"

Case 3 'Group
strRowSource = "SELECT [qry Groups].[Company] FROM [qry Groups]"

End Select

Me.PickCounty.RowSource = strRowSource

End Sub

This is the SQL for Combo Box 2 (DOC List)

SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC
NAME], [tblOFCTRKG].[DOC PH], [tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD],
[tblOFCTRKG].[DOC ADD2], [tblOFCTRKG].[DOC CITY], [tblOFCTRKG].St,
[tblOFCTRKG].[DOC CNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].ID,
[tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC PH],
[tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD], [tblOFCTRKG].[DOC ADD2],
[tblOFCTRKG].[DOC CITY], tblOFCTRKG].St, [tblOFCTRKG].[DOC CNTY],
[tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOC CNTY])=[forms]![frm DOC
Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC
PH];

How can I get the Doc list to list the doc from the selection of Combo Box
1? I have tried everything I can. I would appreciate any help please.
Thank you.
Click to expand...
Click to expand...
 
K

Klatuu

Jeff,
The OP has multiple posts on this problem. I gave him the solution, but I
don't think he is getting it. The code he posted is code I suggested to him.

Basically what he wants is a multi use combo (his combo1) that will allow
him to change the row source to list one of the 3 entities. All he needs to
do is build row source queries for the doctor combo (combo2) and add to the
existing code to set the row source for combo2 based on which row source was
selected for combo1.
--
Dave Hargis, Microsoft Access MVP


Jeff Boyce said:
It sounds like you are saying that your combobox1 (?[PickCounty]) may hold
counties, cities or companies. And then, after a choice in [PickCounty],
you want combobox2 to display doctors in the location (county, city or
company) picked.

First, you'll need to use the AfterUpdate event of [PickCounty] to requery
the combobox2 (?[PickDoctor]), with something like:

Me![PickDoctor].Requery

Then you'll need a query (?perhaps queries) for combobox2 that reflects the
fact that combobox1 does NOT always hold counties. The SQL statement you
provided looks like you are only looking for counties.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

TotallyConfused said:
I hope someone can please help me complete this. I have a form with an
Option Group consisting of 3 choices: Counties, Cities, Companies. 2 combo
boxes. Combo box 1 - List the names of the choice picked from the Option
Group. Combo Box 2 - should list the doctors in the choice selected from
Combox box 1. That is my problem. I can't get the Combo box 2 to work.
Below is the code for After Update in the Option Group.

Private Sub optsearch_AfterUpdate()
Dim strRowSource As String
Select Case Me.optsearch
Case 1 'County
strRowSource = "SELECT [qry Counties].[CNTY] FROM [qry Counties]"

Case 2 'City
strRowSource = "SELECT [qry Cities].[City] FROM [qry Cities]"

Case 3 'Group
strRowSource = "SELECT [qry Groups].[Company] FROM [qry Groups]"

End Select

Me.PickCounty.RowSource = strRowSource

End Sub

This is the SQL for Combo Box 2 (DOC List)

SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC
NAME], [tblOFCTRKG].[DOC PH], [tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD],
[tblOFCTRKG].[DOC ADD2], [tblOFCTRKG].[DOC CITY], [tblOFCTRKG].St,
[tblOFCTRKG].[DOC CNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].ID,
[tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC PH],
[tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD], [tblOFCTRKG].[DOC ADD2],
[tblOFCTRKG].[DOC CITY], tblOFCTRKG].St, [tblOFCTRKG].[DOC CNTY],
[tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOC CNTY])=[forms]![frm DOC
Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC
PH];

How can I get the Doc list to list the doc from the selection of Combo Box
1? I have tried everything I can. I would appreciate any help please.
Thank you.
Click to expand...
Click to expand...
 
T

TotallyConfused

Sorry that I posted again, thought you were done answering. I don't know
what OP means. But I am trying to get it! I do understand that I have to
have 3 versions to select. Which I have. I just don't know how to write it
into the existing code. I have tried the following and it doesn't work:

[Doc List].RowSource = "SELECT [qry doc list cnty];" This only brings back
blank list. If I type in strRowSource it says it is unidentifiable. This is
all I need to complete this. I am sorry if I am being anal, but I am trying
to learn and sometimes it is so frustrating. It seems simple enough to those
that are experts. Thank you.



Klatuu said:
Jeff,
The OP has multiple posts on this problem. I gave him the solution, but I
don't think he is getting it. The code he posted is code I suggested to him.

Basically what he wants is a multi use combo (his combo1) that will allow
him to change the row source to list one of the 3 entities. All he needs to
do is build row source queries for the doctor combo (combo2) and add to the
existing code to set the row source for combo2 based on which row source was
selected for combo1.
--
Dave Hargis, Microsoft Access MVP


Jeff Boyce said:
It sounds like you are saying that your combobox1 (?[PickCounty]) may hold
counties, cities or companies. And then, after a choice in [PickCounty],
you want combobox2 to display doctors in the location (county, city or
company) picked.

First, you'll need to use the AfterUpdate event of [PickCounty] to requery
the combobox2 (?[PickDoctor]), with something like:

Me![PickDoctor].Requery

Then you'll need a query (?perhaps queries) for combobox2 that reflects the
fact that combobox1 does NOT always hold counties. The SQL statement you
provided looks like you are only looking for counties.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

TotallyConfused said:
I hope someone can please help me complete this. I have a form with an
Option Group consisting of 3 choices: Counties, Cities, Companies. 2 combo
boxes. Combo box 1 - List the names of the choice picked from the Option
Group. Combo Box 2 - should list the doctors in the choice selected from
Combox box 1. That is my problem. I can't get the Combo box 2 to work.
Below is the code for After Update in the Option Group.

Private Sub optsearch_AfterUpdate()
Dim strRowSource As String
Select Case Me.optsearch
Case 1 'County
strRowSource = "SELECT [qry Counties].[CNTY] FROM [qry Counties]"

Case 2 'City
strRowSource = "SELECT [qry Cities].[City] FROM [qry Cities]"

Case 3 'Group
strRowSource = "SELECT [qry Groups].[Company] FROM [qry Groups]"

End Select

Me.PickCounty.RowSource = strRowSource

End Sub

This is the SQL for Combo Box 2 (DOC List)

SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC
NAME], [tblOFCTRKG].[DOC PH], [tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD],
[tblOFCTRKG].[DOC ADD2], [tblOFCTRKG].[DOC CITY], [tblOFCTRKG].St,
[tblOFCTRKG].[DOC CNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].ID,
[tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC PH],
[tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD], [tblOFCTRKG].[DOC ADD2],
[tblOFCTRKG].[DOC CITY], tblOFCTRKG].St, [tblOFCTRKG].[DOC CNTY],
[tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOC CNTY])=[forms]![frm DOC
Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC
PH];

How can I get the Doc list to list the doc from the selection of Combo Box
1? I have tried everything I can. I would appreciate any help please.
Thank you.
Click to expand...
Click to expand...
Click to expand...
 
U

UpRider

Tot, if you already have the 3 SQLs for the county, city, and group, you're
almost there.
The code to set the rowsource for combo2 is very siminar to what you have
for combo1.
You can set the rowsoruce for combo2 in the aferupdate event for combo1
thusly:

Private Sub combo1_AfterUpdate()
Dim strRowSource As String
Select Case Me.optsearch
Case 1 'County
strRowSource = "whatever combo2 rowsource is for counties"
Case 2 'City
strRowSource = "whatever combo2 rowsource is for cities"
Case 3 'Group
strRowSource = "whaever comb2 rowsource is for group"
End Select
Me.combo2.RowSource = strRowSource
End Sub

UpRider
 
S

Steve Sanford

You have an option group where you can select County, City and Company. What
you want to see is a list of doctors specific to the County, City and
Company.

In the option group you select Company. To see the companies, the row source
for Combo1 has to select only company names. In your second post on 10/3, you
provided the SQL for Combo2 (doc list) to select the doctors. But the query
is filtered by county (...HAVING ((([tblOFCTRKG].[DOCCNTY])=[forms]![frm DOC
Tracking]![PickCounty]))...).

This doesn't make sense to select a company and expect to get a list that is
filtered by counties. You have to change the row source of the combo box for
the doctors also. Each time you change the option group, the doctor comb box
row source must be changed also.

It would look something like: (watch for line wrap)

'------------------------------------------------------------------
Private Sub opqList_AfterUpdate()
Dim strRowSource As String 'combo1 (County/City/Company)
Dim strCBO2RowSource As String 'combo2 (doctors)

strCBO2RowSource = "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"

Select Case Me.opgList
Case 1 'County
strRowSource = "SELECT [County] FROM CountyTable;"
strCBO2RowSource = strCBO2RowSource & " HAVING
((([tblOFCTRKG].[DOCCNTY])= [forms]![frm DOC Tracking]![combo1]))"
Case 2 'City
strRowSource = "SELECT [City] FROM CityTable;"
strCBO2RowSource = strCBO2RowSource & " HAVING
((([tblOFCTRKG].[DOCCity])= [forms]![frm DOC Tracking]![combo1]))"
Case 3 'Company
strRowSource = "SELECT [Company] FROM CompanyTable;"
strCBO2RowSource = strCBO2RowSource & " HAVING
((([tblOFCTRKG].[DOCCompany])= [forms]![frm DOC Tracking]![combo1]))"
End Select

'county, city or company combo box
Me.Comb1.RowSource = strRowSource

'doctor combo box
Me.Comb2.RowSource = strCBO2RowSource & " ORDER BY [tblOFCTRKG].[DOCNM],
[tblOFCTRKG].[DOCPH];"
End Sub
'------------------------------------------------------------------





HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


TotallyConfused said:
Sorry that I posted again, thought you were done answering. I don't know
what OP means. But I am trying to get it! I do understand that I have to
have 3 versions to select. Which I have. I just don't know how to write it
into the existing code. I have tried the following and it doesn't work:

[Doc List].RowSource = "SELECT [qry doc list cnty];" This only brings back
blank list. If I type in strRowSource it says it is unidentifiable. This is
all I need to complete this. I am sorry if I am being anal, but I am trying
to learn and sometimes it is so frustrating. It seems simple enough to those
that are experts. Thank you.



Klatuu said:
Jeff,
The OP has multiple posts on this problem. I gave him the solution, but I
don't think he is getting it. The code he posted is code I suggested to him.

Basically what he wants is a multi use combo (his combo1) that will allow
him to change the row source to list one of the 3 entities. All he needs to
do is build row source queries for the doctor combo (combo2) and add to the
existing code to set the row source for combo2 based on which row source was
selected for combo1.
--
Dave Hargis, Microsoft Access MVP


Jeff Boyce said:
It sounds like you are saying that your combobox1 (?[PickCounty]) may hold
counties, cities or companies. And then, after a choice in [PickCounty],
you want combobox2 to display doctors in the location (county, city or
company) picked.

First, you'll need to use the AfterUpdate event of [PickCounty] to requery
the combobox2 (?[PickDoctor]), with something like:

Me![PickDoctor].Requery

Then you'll need a query (?perhaps queries) for combobox2 that reflects the
fact that combobox1 does NOT always hold counties. The SQL statement you
provided looks like you are only looking for counties.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

message I hope someone can please help me complete this. I have a form with an
Option Group consisting of 3 choices: Counties, Cities, Companies. 2
combo
boxes. Combo box 1 - List the names of the choice picked from the Option
Group. Combo Box 2 - should list the doctors in the choice selected from
Combox box 1. That is my problem. I can't get the Combo box 2 to work.
Below is the code for After Update in the Option Group.

Private Sub optsearch_AfterUpdate()
Dim strRowSource As String
Select Case Me.optsearch
Case 1 'County
strRowSource = "SELECT [qry Counties].[CNTY] FROM [qry
Counties]"

Case 2 'City
strRowSource = "SELECT [qry Cities].[City] FROM [qry Cities]"

Case 3 'Group
strRowSource = "SELECT [qry Groups].[Company] FROM [qry
Groups]"

End Select

Me.PickCounty.RowSource = strRowSource

End Sub

This is the SQL for Combo Box 2 (DOC List)

SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC
NAME], [tblOFCTRKG].[DOC PH], [tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC
ADD],
[tblOFCTRKG].[DOC ADD2], [tblOFCTRKG].[DOC CITY], [tblOFCTRKG].St,
[tblOFCTRKG].[DOC CNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].ID,
[tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC PH],
[tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD], [tblOFCTRKG].[DOC ADD2],
[tblOFCTRKG].[DOC CITY], tblOFCTRKG].St, [tblOFCTRKG].[DOC CNTY],
[tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOC CNTY])=[forms]![frm DOC
Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOC NAME],
[tblOFCTRKG].[DOC
PH];

How can I get the Doc list to list the doc from the selection of Combo Box
1? I have tried everything I can. I would appreciate any help please.
Thank you.
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

That should do it.
--
Dave Hargis, Microsoft Access MVP


Steve Sanford said:
You have an option group where you can select County, City and Company. What
you want to see is a list of doctors specific to the County, City and
Company.

In the option group you select Company. To see the companies, the row source
for Combo1 has to select only company names. In your second post on 10/3, you
provided the SQL for Combo2 (doc list) to select the doctors. But the query
is filtered by county (...HAVING ((([tblOFCTRKG].[DOCCNTY])=[forms]![frm DOC
Tracking]![PickCounty]))...).

This doesn't make sense to select a company and expect to get a list that is
filtered by counties. You have to change the row source of the combo box for
the doctors also. Each time you change the option group, the doctor comb box
row source must be changed also.

It would look something like: (watch for line wrap)

'------------------------------------------------------------------
Private Sub opqList_AfterUpdate()
Dim strRowSource As String 'combo1 (County/City/Company)
Dim strCBO2RowSource As String 'combo2 (doctors)

strCBO2RowSource = "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"

Select Case Me.opgList
Case 1 'County
strRowSource = "SELECT [County] FROM CountyTable;"
strCBO2RowSource = strCBO2RowSource & " HAVING
((([tblOFCTRKG].[DOCCNTY])= [forms]![frm DOC Tracking]![combo1]))"
Case 2 'City
strRowSource = "SELECT [City] FROM CityTable;"
strCBO2RowSource = strCBO2RowSource & " HAVING
((([tblOFCTRKG].[DOCCity])= [forms]![frm DOC Tracking]![combo1]))"
Case 3 'Company
strRowSource = "SELECT [Company] FROM CompanyTable;"
strCBO2RowSource = strCBO2RowSource & " HAVING
((([tblOFCTRKG].[DOCCompany])= [forms]![frm DOC Tracking]![combo1]))"
End Select

'county, city or company combo box
Me.Comb1.RowSource = strRowSource

'doctor combo box
Me.Comb2.RowSource = strCBO2RowSource & " ORDER BY [tblOFCTRKG].[DOCNM],
[tblOFCTRKG].[DOCPH];"
End Sub
'------------------------------------------------------------------





HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


TotallyConfused said:
Sorry that I posted again, thought you were done answering. I don't know
what OP means. But I am trying to get it! I do understand that I have to
have 3 versions to select. Which I have. I just don't know how to write it
into the existing code. I have tried the following and it doesn't work:

[Doc List].RowSource = "SELECT [qry doc list cnty];" This only brings back
blank list. If I type in strRowSource it says it is unidentifiable. This is
all I need to complete this. I am sorry if I am being anal, but I am trying
to learn and sometimes it is so frustrating. It seems simple enough to those
that are experts. Thank you.



Klatuu said:
Jeff,
The OP has multiple posts on this problem. I gave him the solution, but I
don't think he is getting it. The code he posted is code I suggested to him.

Basically what he wants is a multi use combo (his combo1) that will allow
him to change the row source to list one of the 3 entities. All he needs to
do is build row source queries for the doctor combo (combo2) and add to the
existing code to set the row source for combo2 based on which row source was
selected for combo1.
--
Dave Hargis, Microsoft Access MVP


:

It sounds like you are saying that your combobox1 (?[PickCounty]) may hold
counties, cities or companies. And then, after a choice in [PickCounty],
you want combobox2 to display doctors in the location (county, city or
company) picked.

First, you'll need to use the AfterUpdate event of [PickCounty] to requery
the combobox2 (?[PickDoctor]), with something like:

Me![PickDoctor].Requery

Then you'll need a query (?perhaps queries) for combobox2 that reflects the
fact that combobox1 does NOT always hold counties. The SQL statement you
provided looks like you are only looking for counties.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

message I hope someone can please help me complete this. I have a form with an
Option Group consisting of 3 choices: Counties, Cities, Companies. 2
combo
boxes. Combo box 1 - List the names of the choice picked from the Option
Group. Combo Box 2 - should list the doctors in the choice selected from
Combox box 1. That is my problem. I can't get the Combo box 2 to work.
Below is the code for After Update in the Option Group.

Private Sub optsearch_AfterUpdate()
Dim strRowSource As String
Select Case Me.optsearch
Case 1 'County
strRowSource = "SELECT [qry Counties].[CNTY] FROM [qry
Counties]"

Case 2 'City
strRowSource = "SELECT [qry Cities].[City] FROM [qry Cities]"

Case 3 'Group
strRowSource = "SELECT [qry Groups].[Company] FROM [qry
Groups]"

End Select

Me.PickCounty.RowSource = strRowSource

End Sub

This is the SQL for Combo Box 2 (DOC List)

SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC
NAME], [tblOFCTRKG].[DOC PH], [tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC
ADD],
[tblOFCTRKG].[DOC ADD2], [tblOFCTRKG].[DOC CITY], [tblOFCTRKG].St,
[tblOFCTRKG].[DOC CNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].ID,
[tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC PH],
[tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD], [tblOFCTRKG].[DOC ADD2],
[tblOFCTRKG].[DOC CITY], tblOFCTRKG].St, [tblOFCTRKG].[DOC CNTY],
[tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOC CNTY])=[forms]![frm DOC
Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOC NAME],
[tblOFCTRKG].[DOC
PH];

How can I get the Doc list to list the doc from the selection of Combo Box
1? I have tried everything I can. I would appreciate any help please.
Thank you.
Click to expand...
Click to expand...
Click to expand...
 
T

TotallyConfused

Just want to say, THANK YOU VERY MUCH to all of you for your help and putting
me out of my misery! After much testing and tweaking, I got it to work just
now. I am glad I didn't give up on this and I do want to let you know that
I appreciate you and this forum for all of us out there trying to learn and
work with this. Please accept my apologies for annoying you - but not my
intention in doing so. Thank you once again.


UpRider said:
Tot, if you already have the 3 SQLs for the county, city, and group, you're
almost there.
The code to set the rowsource for combo2 is very siminar to what you have
for combo1.
You can set the rowsoruce for combo2 in the aferupdate event for combo1
thusly:

Private Sub combo1_AfterUpdate()
Dim strRowSource As String
Select Case Me.optsearch
Case 1 'County
strRowSource = "whatever combo2 rowsource is for counties"
Case 2 'City
strRowSource = "whatever combo2 rowsource is for cities"
Case 3 'Group
strRowSource = "whaever comb2 rowsource is for group"
End Select
Me.combo2.RowSource = strRowSource
End Sub

UpRider

TotallyConfused said:
I hope someone can please help me complete this. I have a form with an
Option Group consisting of 3 choices: Counties, Cities, Companies. 2
combo
boxes. Combo box 1 - List the names of the choice picked from the Option
Group. Combo Box 2 - should list the doctors in the choice selected from
Combox box 1. That is my problem. I can't get the Combo box 2 to work.
Below is the code for After Update in the Option Group.

Private Sub optsearch_AfterUpdate()
Dim strRowSource As String
Select Case Me.optsearch
Case 1 'County
strRowSource = "SELECT [qry Counties].[CNTY] FROM [qry
Counties]"

Case 2 'City
strRowSource = "SELECT [qry Cities].[City] FROM [qry Cities]"

Case 3 'Group
strRowSource = "SELECT [qry Groups].[Company] FROM [qry
Groups]"

End Select

Me.PickCounty.RowSource = strRowSource

End Sub

This is the SQL for Combo Box 2 (DOC List)

SELECT DISTINCT [tblOFCTRKG].ID, [tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC
NAME], [tblOFCTRKG].[DOC PH], [tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC
ADD],
[tblOFCTRKG].[DOC ADD2], [tblOFCTRKG].[DOC CITY], [tblOFCTRKG].St,
[tblOFCTRKG].[DOC CNTY] FROM [tblOFCTRKG] GROUP BY [tblOFCTRKG].ID,
[tblOFCTRKG].[DOC ID], [tblOFCTRKG].[DOC NAME], [tblOFCTRKG].[DOC PH],
[tblOFCTRKG].[DOC FX], [tblOFCTRKG].[DOC ADD], [tblOFCTRKG].[DOC ADD2],
[tblOFCTRKG].[DOC CITY], tblOFCTRKG].St, [tblOFCTRKG].[DOC CNTY],
[tblOFCTRKG].ID HAVING ((([tblOFCTRKG].[DOC CNTY])=[forms]![frm DOC
Tracking]![PickCounty])) ORDER BY [tblOFCTRKG].[DOC NAME],
[tblOFCTRKG].[DOC
PH];

How can I get the Doc list to list the doc from the selection of Combo Box
1? I have tried everything I can. I would appreciate any help please.
Thank you.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Generating Report from Form with Options 5
Need help with Combo - Box Part2? 0
Form's Combo Box contents to Report 0
Creating a Search using a combo box and then creating a report. 4
Combo box 0
Need help with Combo Box? 15
1 combo box changes per 2 other combo boxes 3
Combo or list box 0

Top