Row Source Data on a Form

[Benj]

I have created a form containing combo boxes that displays fields from a table/query.<br>
<br>
Is it possible to set one combo box to read the other and then display fields from a relevent table/query depending upon what is in the other combo-box.<br>
<br>
i.e. One Combo box contains &quot;Countries&quot; and another contains &quot;Cities&quot;.<br>
Tables are set up with Cities from England and France.<br>
Now if someone selects the Country as England in the 1st combo-box, I would want the second combo-box to read the England Cities table, Otherwise read the French Cities.<br>
<br>
Thanks<br>
<p>Ben Smith<br><a href=mailto:Ben.Smith@nswl-tr.nwest.nhs.uk>Ben.Smith@nswl-tr.nwest.nhs.uk</a><br><a href= > </a><br>

 

[WP]

Yes,<br>
<br>
Here's the syntax:<br>
<br>
in After-Update of Combo1 put code<br>
<br>
Combo2.RowSource=&quot;SELECT FROM tblCities WHERE Country='&quot; & Combo1 & &quot;';&quot;<br>
<br>
Note, I've assumed your table of cities is called tblCities <br>
<br>
WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[DougP]

sure thing you got Access it can do anything.<br>
Ha<br>
there are 2 things here the first goes in the forms load event<br>
---------------------------------------<br>
Private Sub Form_Load()<br>
'this is the countries combo box<br>
Dim db As Database, rst As Recordset<br>
Dim SQL As String<br>
Set db = CurrentDb<br>
'your SQL will say Select Countries From YourconuntryTable<br>
SQL = &quot;Select Name From Employee&quot;<br>
Set rst = db.OpenRecordset(SQL)<br>
Combo5.RowSource = SQL<br>
Combo5.Requery<br>
<br>
End Sub<br>
<br>
this goes in the countries combo box after update event<br>
------------------------------------------<br>
Private Sub Combo5_AfterUpdate()<br>
'this is the still the countries combo box<br>
Dim db As Database, rst As Recordset<br>
Dim SQL2 As String<br>
Set db = CurrentDb<br>
SQL2 = &quot;Select Cites From YourconuntryTable Where Country = &quot; & Me!Combo5<br>
Set rst = db.OpenRecordset(SQL2)<br>
' but this is the cities combo box<br>
Combo3.RowSource = SQL2<br>
Combo3.Requery<br>
End Sub<br>
<br>
DougP<br>

 

[elizabeth]

Also page back to see a couple of similar answers to a Combo Box question posted on Oct 26th by Geekette.

 

[DougP]

I forgot the Single quotes around combo5<br>
Select Cites From YourconuntryTable Where Country = '&quot; & Me!Combo5 & &quot;';&quot;

 

[WP]

DougP,<br>
<br>
Why all the code ??<br>
<br>
Why do you define and set the recordset rst ?<br>
<br>
WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[DougP]

Cause I thought he wanted to look up the city based on the country picked.<br>
In which case the cities are only known when you pick a country.<br>
But you are right I should have used the Recordsource property in the countries.<br>
And now that I think of it you could propabably do the same in the cities.<br>
Well that just goes to show you there is more than one way to do anything in Access.<br>
Thats's why it is so difficult for the new comer.