cascading combo box

[Ken Snell [MVP]]

Add a step to your first combo box's AfterUpdate event procedure to set the
value of the second combo box to NULL:

Me.cboPropertyFilter.Value = NULL

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

One more quick question.

When I make a selection in the first box, for example for "active', the
second box retains the previous value, which could be "sold' until I click
on
the drop down arrow and make a selection. So, when I make the selection in
the first box, how can I force the second box show a blank until a
selection
is made in the second box?

Thanks,
AC

It's not good practice to have the ColumnCount have a value that differs
from the number of fields in the Row Source query. I've seen some strange
behavior with combo boxes in those situations, though the strange
behavior
is not consistently noted. So, yes, I strongly recommend that you change
the
properties of the second combo box to match what I posted.

I assume that the status of "all" is an item in the first combo box's Row
Source query? If yes, then the second combo box's Row Source query would
change to this:

SELECT tblPropertyStatus.Status, Properties.[Property Address],
Transaction.[Transaction Status]
FROM Properties LEFT JOIN (tblPropertyStatus RIGHT JOIN [Transaction] ON
tblPropertyStatus.Status = Transaction.[Transaction Status]) ON
Properties.[ID-Properties] = Transaction.[ID-Properties]
WHERE (((Transaction.[Transaction Status])=[cboTransactionStatus])
OR (([cboTransactionStatus]) = "all"));


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken,

I did find the problem. I created couple of sample data on transaction
and
properties tables but I forgot to include the ID-Properties code into
the
transaction table! So, it was not able to make the relationship!
Following
is
the code for the query that I build that even works with the WHEN
clause;

SELECT tblPropertyStatus.Status, Properties.[Property Address],
Transaction.[Transaction Status]
FROM Properties LEFT JOIN (tblPropertyStatus RIGHT JOIN [Transaction]
ON
tblPropertyStatus.Status = Transaction.[Transaction Status]) ON
Properties.[ID-Properties] = Transaction.[ID-Properties]
WHERE (((Transaction.[Transaction Status])="active"));

I made the changes on the first combo box as you have outlined, however
on
the second combo box I am not sure of the meanings of the numbers you
suggested;

Currently I have the following;

BoundColumn 1
ColumnCount 2
ColumnWidths 1

These settings seems to be working. Should I change them as you have
suggested before and please let me know the reasons for the change.

Also, when the status is "all", I would like to include all properties
in
the list as if there is no criteria. How do I do that?

thanks,
AC

:

Create a new query in Design view. In the Select Tables popup window,
select
the Transaction and the Properties tables, and add them to the query
grid.
Close the Select Table window.

In the query grid view, make a join between the appropriate fields in
the
two tables. Drag to the grid the fields that you want the query to
show
for
the second combo box, and make sure they're in the right order for the
combo
box.

Run the query to be sure that it brings back records. If it doesn't,
this
is
the problem. Your query is not selecting any records BEFORE you add a
criterion, so adding a WHERE clause will not improve that situation.
You
need to design the basic query correctly before we can fix the combo
box
issue.

If the query returns records, click on the Query View icon button on
the
left end of the toolbar, and select SQL View. Copy the entire SQL
statement
that you see in that window, and paste it into your reply to this
post.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Ken,

I found the mistake I made when I was copying the sql statement you
wrote
in
your previoius email. Id no longer ask for a parameter. However,
when I
ran
the query it sitll does not return anything and takes me back to the
datasheet view. I can click on the transaction status field and
select,
for
example "active" and it does give that audio sound again and I am
not
sure
what it means. It just canot place the "active" value into the
field!
I
think this is where the problem is!

You also asked me to build a query without WHERE clause. but I am
not
sure
what this means! What kind of query you are asking?

Thanks,
AC

 

[Ken Snell [MVP]]

The best way to do this in ACCESS is to use a form/subform setup for
entering data. The main form is used to enter the parent table's data, and
the subform is used to enter the child table's data. You link the main form
and subform by the primary/foreign keys, and ACCESS automatically enters the
values into the foreign key fields for you.

Take a look at forms/subforms in Help. Also, I recommend that you obtain a
good ACCESS book that will discuss these and other helpful topics; see
www.viescas.com for some good book recommendations, or visit your local
bookstore or online store (www.Amaxon.com or others).