Limiting Options in Combo Boxes 2

[eagle11232]

Hello All.

I am working to limit the options in a combo box based on the selection of another combo box. I found the FAQ on the topic, faq702-681, but I am having trouble working through the query builder.

The first combo box has four options and are sourced from a Value List that I manually entered as they are fixed.

The second combo box is sourced from a Table/Query.

Using the example in faq702-681 I coded into the Criteria of the query: Forms![MyFormName]![NameofComboBoxthatIamtryingtolimit].[Value]

When I try to enter the data in the form view, only the first entry in the table that holds the data for the second combo box is available. I haven't had any luck getting the other 3 options to be visible.

Thank you for any help you can provide.

 

[ProgramError]

make sure you requery in the afterupdate event of each combobox

Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[ProgramError]

http://www.techonthenet.com/access/comboboxes/linked_combos.php

may help you too

Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[TheAceMan1]

How are ya eagle11232 . . .

First and foremost, the 2nd combobox has to be [blue]unbound![/blue]

Post the [blue]value list[/blue] and the [blue]rowsource property[/blue] of the 2nd combobox . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[eagle11232]

Hello everyone. The first combobox, State, has a value list of FL;TX;OH,PA;MI;KY.The second combobox, City, is unbound, but instead of a value list I am using a Table/Query as the rowsourcetype and I am using a Query called querycities as my row source. Cities is table of cities in all of the valuelisted states. Each state is listed in its own column, and the cities table is not that large as there are no more than 5 cities per state.

I have been using a Access Manual that states that the control source should be set to the field I want to populate and the rowsource should be found using the following example code:

=SELECT DISTINCT Customers.Country FROM Customers;

which in my case would read:

=SELECT DISTINCT [My Form Name].[Combobox name] FROM Cities;

However, I keep getting an error that the Record source '~sq_My Form Name~sq_cComboboxname' specified on this form or report does not exist.

Thank you for your time

 

[ProgramError]

Remove the = from the row source statement.
Secondly, are there any spaces in your select statement because your error is showing that there aren't any.
ie My Form Name~sq_cComboboxname

Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[eagle11232]

I removed the equal sign and that seems to have resolved the error. However, now when I select a state in the first combobox, that value is copied to the second combobox instead of allowing me to select a city.

As for spaces in my select statement, it is entered in Access as:

SELECT DISTINCT [My Form Name].[City Names] FROM Cities;

Thanks

 

[ProgramError]

these links may help

http://www.datapigtechnologies.com/flashfiles/combobox2.html

http://www.datapigtechnologies.com/flashfiles/combobox3.html

Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[TheAceMan1]

[blue] . . . Cities is table of cities in all of the valuelisted states. Each state is listed in its own column, . . .[/blue]

Wooaaahh . States in your cities table should be a single column!. The table should look like:

[tt]tblCities
*********
CityID as Long PrimaryKey (can be autonumber)
City as Text
State as Text[/tt]

For updating the City combobox, the AfterUpdate event of the State combobox would look like:

[blue]   Dim SQL As String
   
   SQL = "SELECT City " & _
         "FROM tblCities " & _
         "WHERE [State] = '" & Me!City & "';"
   Me!City.RowSource = SQL[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[YoungManRiver]

All,

The first combobox, State, has a value list of FL;TX;OH,PA;MI;KY.The second combobox, City, is unbound

If the filtering is by state then wouldn't code be:

   Dim SQL As String
   SQL = "SELECT City FROM tblCities WHERE [State] = '" & Me!State & "';"
   Me!City.RowSource = SQL
   Me!City.Requery

Don't forget the .Requery of you won't see the results.

YMR

 

[TheAceMan1]

YoungManRiver . . .

Thanks for the correction!

Also note: any time you [blue]write to the rowsource[/blue] of a combobox or listbox, a [blue]requery automatically occurs![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[eagle11232]

I hope everyone had a nice Memorial Day. Thank you both for your helpful suggestions. The code that YoungManRiver provided works well, however, I am looking to refine my statement some more. I looked at faq702-3924 for help in making the statement

SQL= "SELECT City FROM tblCities WHERE [State] = '" & Me!State & "';"

a little more selective by adding another column with which to filter to obtain a unique record.


I coded:
SQL = "SELECT City FROM tblCities WHERE [State] = '" & Me!State & "' And [City] = '" & Me!City & "'"

But it seems like this is too selective and the combobox does not populate.

Thanks for any help