Re: Cascading Combo boxes using CASE command

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Yeah thats correct.
Its working now ....thanks a lot.
Another typo error ...i am just learning but thanks for ur help and prompt
response

"Douglas J. Steele" wrote:

Just to confirm, College and City are the names of combo boxes, both on the
same form (the form being used as the subform), correct?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Agnelo Fernandes" <AgneloFernandes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:2F58BF46-06AD-49D3-891D-780AF4DEF20A@xxxxxxxxxxxxxxxx
I ran the 2nd code which u gave me:

Private Sub City_AfterUpdate()

Me.College.RowSource = "SELECT tblAll.College " & _
"FROM tblAll " & _
"WHERE City = '" & Me.City.Value & "' " & _
"AND Country = '" & Me.Parent.Form.Country.Value & "' " & __
"ORDER BY tblAll.College"

End Sub

An Error comes up stating: Compile Error Invalid Qualifier.
It highlights College at the very first line of the code.

?

"Douglas J. Steele" wrote:

If both CIty and College are on the same form, then all you should need
is

Private Sub City_AfterUpdate()

Me.College.RowSource = "SELECT tblAll.College " & _
"FROM tblAll " & _
"WHERE City = '" & Me.City.Value & "' " & _
"AND Country = '" & Me.Country.Value & "' " & __
"ORDER BY tblAll.College"

End Sub

If Country is on the parent form, make that

Private Sub City_AfterUpdate()

Me.College.RowSource = "SELECT tblAll.College " & _
"FROM tblAll " & _
"WHERE City = '" & Me.City.Value & "' " & _
"AND Country = '" & Me.Parent.Form.Country.Value & "' " & __
"ORDER BY tblAll.College"

End Sub

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Agnelo Fernandes" <AgneloFernandes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:862CB40E-9A9D-4F42-B8BA-5B7D316F958F@xxxxxxxxxxxxxxxx
Sorry..there was a typo error and on its rectification..it works fine.
However, i am getting an error while cascading Cities to Colleges, both
of
which are in Subform.
I give below the code I ran in 'after update' in City:

Private Sub City_AfterUpdate()
Me.[College Subform].Form.College.RowSource = "SELECT tblAll.College "
& _
"FROM tblAll " & _
"WHERE tblAll.City = '" & Me.[College Subform].City.Value & "' " &
_
"AND tblALL.Country = '" & Country.Value & "' " & __
"ORDER BY tblAll.College;"
End Sub

* College Subform - name of subform control in main form
* Country - 1st field in tblAll
* City - 2nd field in tblAll
* College - 3rd field in tblAll

I get the following error:

Run-time error '2465'

Microsoft Access can't find the field '|' referred to in your
expression

Any help?

"Douglas J. Steele" wrote:

What does "did not work" mean in this context? First thing to do is
remove
the On Error Resume Next to see whether there's an error. If there is,
what's the error?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Agnelo Fernandes" <AgneloFernandes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in
message news:23E03D59-339D-4D1C-87A0-E6FD6DD48006@xxxxxxxxxxxxxxxx
Douglas,

Based on your suggestion I have designed a Table containing all
information:
Countries, Cities and Colleges ( tblAll ).

The Country combo is in Main form and Cities and Colleges combo are
in
Subform.
The idea is based on the country i select the appropriate cities
need
to
display and based on the cities I select the appropriate colleges
need
to
display.

I ran the following code in 'after update' of country but it did not
work.
I
guess the error is in addressing the combo in Subform. Please do
have
look
at
the code below:

Private Sub Country_AfterUpdate()
On Error Resume Next
Me.[College Subform].[Form].City.RowSource = "Select tblAll.City "
&
_
"FROM tblAll " & _
"WHERE tblAll.Country = '" & Country.Value & "' " & _
"ORDER BY tblAll.City;"
End Sub

Note: College Subform is the control name of Subform in my Main
form.

Any help?


"Douglas J. Steele" wrote:

Did you try it? Did it work?

I may be reading too much into your variable names, but if you've
actually
got tables named "Tbl_London_college", "Tbl_Amsterdam_college" and
"Tbl_Sydney_college", odds are your design is incorrect.

You should have one table that contains all of the colleges, with a
field
indicating the city to which the college is linked. Your RowSource
would
then be a query with an appropriate WHERE clause, and you wouldn't
have
to
go through this machinations.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Agnelo Fernandes" <AgneloFernandes@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote
in
message news:0A5F54D5-0861-48B5-9F21-06EF0821614E@xxxxxxxxxxxxxxxx
Hi Jeanette,

I have sorted that out - combo cascading from 'countries'[main
form]
to
'cities' [subform] based on the code u gave below.

Now I need to combo cascade 'cities' [sub form] to 'Colleges'
[sub
form].
Could I use the same code below as long as I indicate that
'cities'
and
'colleges' are Sub Form eg:

Private Sub cboCity_AfterUpdate()
On Error Resume Next
Dim strTableName as String
Select Case cboCity.Value
Case "London"
strTableName = "Tbl_London_college"
Case "Amsterdam"
strTableName = "Tbl_Amsterdam_college"
Case "Sydney"
strTableName = "Tbl_Sydney_college"
End Select

Me.[NameOfSubformControl].Form.cboCollege.RowSource =
strTableName

End Sub

Will this work?

"Jeanette Cunningham" wrote:

Hi Agnelo
you need the correct syntax to 'talk' to the subform.
The name of the subform control is important.
A subform is always inside a subform control on the main form.
The name of the subform control can be, and often is, different
from
the
name of the subform inside it.

To talk to cboCity when it is on the subform you use syntax like
this:
Me.[NameOfSubformControl].Form.cboCity
instead of just cboCity

Code something like this:

Private Sub cboCountry_AfterUpdate()
On Error Resume Next
Dim strTableName as String

Select Case cboCountry.Value
Case "France"
strTableName = "tblFrance"
Case "United Kingdom"
strTableName = "tblUnitedKingdom"
Case "United States"
strTableName = "tblUnitedStates"
End Select

Me.[NameOfSubformControl].Form.cboCity.RowSource = strTableName

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria
Australia


"Agnelo Fernandes" <AgneloFernandes@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote
in
message
news:9BD4E04B-3FB8-4255-9E92-7147806BD166@xxxxxxxxxxxxxxxx
I tried setting up a cascading combo box between countries and
cities
i.e
based on the country u select , you can view the appropriate
cities
in
the
other combo box.
I tried using CASE command in 'after update' of country combo
box:

Private Sub cboCountry_AfterUpdate()
On Error Resume Next
Select Case cboCountry.Value
Case "France"
cboCity.RowSource = "tblFrance"
Case "United Kingdom"
cboCity.RowSource = "tblUnitedKingdom"
Case "United States"
cboCity.RowSource = "tblUnitedStates"
End Select
End Sub

This works fine but now I need 'cities' combo box to be in
SubForm.
So
based
on the option I select in Countries [on MainForm] I should get
the
appropriate cities [on SubForm] by using CASE function...Any
help










.

Relevant Pages

  • Re: Cascading Combo boxes using CASE command
    ... Private Sub City_AfterUpdate ... However, i am getting an error while cascading Cities to Colleges, ... which are in Subform. ...
    (microsoft.public.access.forms)
  • Re: Cascading Combo boxes using CASE command
    ... Private Sub City_AfterUpdate ... College Subform - name of subform control in main form ... Countries, Cities and Colleges. ...
    (microsoft.public.access.forms)
  • Re: Cascading Combo boxes using CASE command
    ... Countries, Cities and Colleges. ... guess the error is in addressing the combo in Subform. ... Private Sub Country_AfterUpdate ... Dim strTableName as String ...
    (microsoft.public.access.forms)
  • Re: Cascading Combo boxes using CASE command
    ... 'cities' based on the code u gave below. ... Dim strTableName as String ... you need the correct syntax to 'talk' to the subform. ... Private Sub cboCountry_AfterUpdate ...
    (microsoft.public.access.forms)
  • Re: Form not closing properly
    ... Earlier today I installed the pop up calendar utility from ... Private Sub Form_Unload ... My guess is that it's something to do with the subform. ... screenshots to illustrate what is going on. ...
    (microsoft.public.access.modulesdaovba)