linked comboboxes change on delete

D

drabbacs

I'm designing a form with 2 comboboxes that are linked. For an easy
example, let's say I want ComboBox1 to list all the names of US states
and ComboBox2 to list all of the US cities.

Upon form opening, the RowSource property on ComboBox2 lists all
cities through the use of Query1 (assigned at design time)

Query1 = "Select US.City from US;"

table US is 2 fields: State and City

I have successfully tied/altered the content of ComboBox2 to the
selection in ComboBox1 by using the following code

Private Sub cbx_EditExisting_ShipNo_AfterUpdate()
Me!ComboBox2.RowSourceType = "Table/Query"
Me!ComboBox2.RowSource = "Query2"
End Sub

Query2 = "Select US.City from US where US.State = Forms!ThisForm!
ComboBox1;"

The behavior I'm trying to design is that a user can open the form, go
to ComboBox2 and see *all* the cities, then goto ComboBox1 and select
a state, and go back to ComboBox2 and they'll now see only cities
within the state they've selected.

All of this I have accomplished.

What I haven't figured out how to do, is reverse the change if they
click in the editable area of ComboBox1 and delete the selection. If
this occurs I would like the filter/ restriction removed from the list
of cities. That is, I'd like to list all cities again, or change the
RowSource back to Query1.

I have attempted to capture the condition of deleting the selection in
ComboBox1 in several ways

if Me!ComboBox1 Is Nothing Then
if Me!ComboBox1 Is Null Then
if Me!ComboBox1 = "" Then
if Me!ComboBox1 = 0 Then
Me!ComboBox2.RowSourceType = "Table/Query"
Me!ComboBox2.RowSource = "Query1"
end if

These are apparently incorrect since I can't even get a simple MsgBox
to appear within the if.

I have tried placing this logic in an the event procedures for
AfterUpdate, GotFocus, LostFocus on ComboBox1.


Can someone help me out please?

Thanks in advance.

(e-mail address removed)
 

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

Top