cascading combo box

[ace]

Urgent reply needed!

I am trying to implement cascading combo box where selection in one combo
box determines the selections in another combo box. I can see the selections
in the first combo box. When I make the selection, I see nothing in the other
combo box! It is suppose to list the items based on the selection from the
first combo box.

Please let me know if you have any general tips and/or look at the details
of the setup below and let me know what the problem might be!

Thanks,
AC Erdal

Application is real estate. There are two primary tables “Property†and
Transactionâ€. Here is a summary of fields for both tables;

Property Transaction
ID-Properties ID-Transaction
Property Address Transaction Status
ID-Properties

Other table is the PropertyStatus
1 all
2 listed
3 active
4 pending
5 closed

On a form (Project Details-project), I created two combo boxes;

cboTransactionStatus
control source: unbound
row source: SELECT tblPropertyStatus.Status FROM tblPropertyStatus;

and,

cboPropertyFilter
control source: =[Properties]![ID-Properties] «Expr» [Properties]![Property
Address]
row source: SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN [Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
(((Transaction.[Transaction Status])=[Forms]![Project
Details-project]![cboTransactionStatus]));

 

[Ken Snell [MVP]]

Try changing your RowSource property for cboPropertyFilter combo box to
this:

SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
Transaction.[Transaction Status]=[cboTransactionStatus];


As an additional test, make sure that the above query will return a value
for an item that you might select in the cboTransactionStatus combo box.


Your ControlSource for the cboPropertyFilter combo box also will prevent you
from selecting anything in the second combo box:

=[Properties]![ID-Properties] «Expr» [Properties]![Property Address]

When a combo box is bound to an expression, you cannot change anything in
the combo box, including not being able to select anything. Is this second
combo box supposed to be bound to a field in the form's RecordSource query?
--

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

Ken,
I actually did add the following code to the first combo box after update
line but I am still getting blank line on the second box!

Thanks,
AC


Option Compare Database

Private Sub cboTransactionStatus_AfterUpdate()
Me!cboPropertyFilter.Requery
End Sub

See this article:
http://www.mvps.org/access/forms/frm0028.htm

You need to use VBA code to requery the second combobox after you make a
selection in the first combobox.
--

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

Urgent reply needed!

I am trying to implement cascading combo box where selection in one
combo
box determines the selections in another combo box. I can see the
selections
in the first combo box. When I make the selection, I see nothing in the
other
combo box! It is suppose to list the items based on the selection from
the
first combo box.

Please let me know if you have any general tips and/or look at the
details
of the setup below and let me know what the problem might be!

Thanks,
AC Erdal

Application is real estate. There are two primary tables "Property"
and
Transaction". Here is a summary of fields for both tables;

Property Transaction
ID-Properties ID-Transaction
Property Address Transaction Status
ID-Properties

Other table is the PropertyStatus
1 all
2 listed
3 active
4 pending
5 closed

On a form (Project Details-project), I created two combo boxes;

cboTransactionStatus
control source: unbound
row source: SELECT tblPropertyStatus.Status FROM tblPropertyStatus;

and,

cboPropertyFilter
control source: =[Properties]![ID-Properties] «Expr»
[Properties]![Property
Address]
row source: SELECT Transaction.[Transaction Status],
Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
(((Transaction.[Transaction Status])=[Forms]![Project
Details-project]![cboTransactionStatus]));

 

[June7 via AccessMonster.com]

Use AfterUpdate event of first combobox to set the RowSource string of second
combobox with code:
Select Case combobox1
Case "item1"
combobox2.RowSource = "item list or table/query field"
combobox2.SourceType = "Value List or Table/Query, whichever is
appropriate"
...
End Select
combobox2.Requery

Urgent reply needed!

I am trying to implement cascading combo box where selection in one combo
box determines the selections in another combo box. I can see the selections
in the first combo box. When I make the selection, I see nothing in the other
combo box! It is suppose to list the items based on the selection from the
first combo box.

Please let me know if you have any general tips and/or look at the details
of the setup below and let me know what the problem might be!

Thanks,
AC Erdal

Application is real estate. There are two primary tables “Property†and
Transactionâ€. Here is a summary of fields for both tables;

Property Transaction
ID-Properties ID-Transaction
Property Address Transaction Status
ID-Properties

Other table is the PropertyStatus
1 all
2 listed
3 active
4 pending
5 closed

On a form (Project Details-project), I created two combo boxes;

cboTransactionStatus
control source: unbound
row source: SELECT tblPropertyStatus.Status FROM tblPropertyStatus;

and,

cboPropertyFilter
control source: =[Properties]![ID-Properties] «Expr» [Properties]![Property
Address]
row source: SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN [Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
(((Transaction.[Transaction Status])=[Forms]![Project
Details-project]![cboTransactionStatus]));

 

[tina]

cboPropertyFilter

control source: =[Properties]![ID-Properties] «Expr» [Properties]![Property
Address]

well, i'm surprised you're not seeing #Error in the second combobox control
in Form view. remove the ControlSource property setting, and leave the
control unbound, or set the ControlSource to the name of a field in the
form's RecordSource. if you find you're still having a problem with the 2nd
combo's RowSource, then suggest you continue working with MVP Ken Snell to
troubleshoot that issue.

hth

Urgent reply needed!

I am trying to implement cascading combo box where selection in one combo
box determines the selections in another combo box. I can see the selections
in the first combo box. When I make the selection, I see nothing in the other
combo box! It is suppose to list the items based on the selection from the
first combo box.

Please let me know if you have any general tips and/or look at the details
of the setup below and let me know what the problem might be!

Thanks,
AC Erdal

Application is real estate. There are two primary tables "Property" and
Transaction". Here is a summary of fields for both tables;

Property Transaction
ID-Properties ID-Transaction
Property Address Transaction Status
ID-Properties

Other table is the PropertyStatus
1 all
2 listed
3 active
4 pending
5 closed

On a form (Project Details-project), I created two combo boxes;

cboTransactionStatus
control source: unbound
row source: SELECT tblPropertyStatus.Status FROM tblPropertyStatus;

and,

cboPropertyFilter
control source: =[Properties]![ID-Properties] «Expr» [Properties]![Property
Address]
row source: SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN [Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
(((Transaction.[Transaction Status])=[Forms]![Project
Details-project]![cboTransactionStatus]));

 

[ace]

Hi Ken,

I did change the RowSource prooerty for cboPropertyFilter combo box as you
have requested, but still I am getting blank line in the second combo box.

you asked me to do something but I am not sure if there is anything else you
wanted me to do with your statement;
"As an additional test, make sure that the above query will return a value
for an item that you might select in the cboTransactionStatus combo box."

Let me know what else I need to do again!

The second combo box is suppose to provide a list of proprty address based
on the proprety status. For example, if the "active" status is selected in
the first combo box, the second box is suppose provide a list address of
properties that are active.

Just to test it, I did remove the ControlSource expression for the
cboPropertyFilter combo box, and it also did not help!

Thanks,
AC

Try changing your RowSource property for cboPropertyFilter combo box to
this:

SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
Transaction.[Transaction Status]=[cboTransactionStatus];


As an additional test, make sure that the above query will return a value
for an item that you might select in the cboTransactionStatus combo box.


Your ControlSource for the cboPropertyFilter combo box also will prevent you
from selecting anything in the second combo box:

=[Properties]![ID-Properties] «Expr» [Properties]![Property Address]

When a combo box is bound to an expression, you cannot change anything in
the combo box, including not being able to select anything. Is this second
combo box supposed to be bound to a field in the form's RecordSource query?
--

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

Ken,
I actually did add the following code to the first combo box after update
line but I am still getting blank line on the second box!

Thanks,
AC


Option Compare Database

Private Sub cboTransactionStatus_AfterUpdate()
Me!cboPropertyFilter.Requery
End Sub

See this article:
http://www.mvps.org/access/forms/frm0028.htm

You need to use VBA code to requery the second combobox after you make a
selection in the first combobox.
--

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




Urgent reply needed!

I am trying to implement cascading combo box where selection in one
combo
box determines the selections in another combo box. I can see the
selections
in the first combo box. When I make the selection, I see nothing in the
other
combo box! It is suppose to list the items based on the selection from
the
first combo box.

Please let me know if you have any general tips and/or look at the
details
of the setup below and let me know what the problem might be!

Thanks,
AC Erdal

Application is real estate. There are two primary tables "Property"
and
Transaction". Here is a summary of fields for both tables;

Property Transaction
ID-Properties ID-Transaction
Property Address Transaction Status
ID-Properties

Other table is the PropertyStatus
1 all
2 listed
3 active
4 pending
5 closed

On a form (Project Details-project), I created two combo boxes;

cboTransactionStatus
control source: unbound
row source: SELECT tblPropertyStatus.Status FROM tblPropertyStatus;

and,

cboPropertyFilter
control source: =[Properties]![ID-Properties] «Expr»
[Properties]![Property
Address]
row source: SELECT Transaction.[Transaction Status],
Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
(((Transaction.[Transaction Status])=[Forms]![Project
Details-project]![cboTransactionStatus]));

 

[ace]

What is "item1" suppose to be?

Also, I assume I will need to use
combobox2.RowSource = Properties.[PropertyAddress]

It would be great if you can write it with the actual code based on my
tables to eliminate any problems!

Thanks,
AC Erdal

Use AfterUpdate event of first combobox to set the RowSource string of second
combobox with code:
Select Case combobox1
Case "item1"
combobox2.RowSource = "item list or table/query field"
combobox2.SourceType = "Value List or Table/Query, whichever is
appropriate"
...
End Select
combobox2.Requery

Urgent reply needed!

I am trying to implement cascading combo box where selection in one combo
box determines the selections in another combo box. I can see the selections
in the first combo box. When I make the selection, I see nothing in the other
combo box! It is suppose to list the items based on the selection from the
first combo box.

Please let me know if you have any general tips and/or look at the details
of the setup below and let me know what the problem might be!

Thanks,
AC Erdal

Application is real estate. There are two primary tables “Property†and
Transactionâ€. Here is a summary of fields for both tables;

Property Transaction
ID-Properties ID-Transaction
Property Address Transaction Status
ID-Properties

Other table is the PropertyStatus
1 all
2 listed
3 active
4 pending
5 closed

On a form (Project Details-project), I created two combo boxes;

cboTransactionStatus
control source: unbound
row source: SELECT tblPropertyStatus.Status FROM tblPropertyStatus;

and,

cboPropertyFilter
control source: =[Properties]![ID-Properties] «Expr» [Properties]![Property
Address]
row source: SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN [Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
(((Transaction.[Transaction Status])=[Forms]![Project
Details-project]![cboTransactionStatus]));

 

[Ken Snell [MVP]]

OK. Let's check a few properties before we go further.

For the first combo box (cboTransactionStatus), what are the values of the
following properties:
BoundColumn
ColumnCount
ColumnWidths
RowSource
RowSourceType
ControlSource
Name
OnAfterUpdate

For the second combo box (cboPropertyFilter), what are the values of the
following properties:
BoundColumn
ColumnCount
ColumnWidths
RowSource
RowSourceType
ControlSource
Name


With regard to my suggestion about making sure the query for the
cboPropertyFilter combo box will return a record for a value that you might
select in the first combo box, create a new query in design view, do not
select any table in the popup window, and close that popup window. On the
upper left end of the toolbar, select the SQL View option in the View icon
button. Paste this SQL statement into the window that appears:

SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties]

Change the view back to Design (same button on toolbar). You'll now see an
unfiltered version of the query that you're using in the cboPropertyFilter
combobox.

In the design grid, under the Transaction Status field, enter a value in the
Where: box that would be found in the Status field of the tblPropertyStatus
table. Run the query to see if any record(s) is returned.


--

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

Hi Ken,

I did change the RowSource prooerty for cboPropertyFilter combo box as you
have requested, but still I am getting blank line in the second combo box.

you asked me to do something but I am not sure if there is anything else
you
wanted me to do with your statement;
"As an additional test, make sure that the above query will return a value
for an item that you might select in the cboTransactionStatus combo box."

Let me know what else I need to do again!

The second combo box is suppose to provide a list of proprty address based
on the proprety status. For example, if the "active" status is selected in
the first combo box, the second box is suppose provide a list address of
properties that are active.

Just to test it, I did remove the ControlSource expression for the
cboPropertyFilter combo box, and it also did not help!

Thanks,
AC

Try changing your RowSource property for cboPropertyFilter combo box to
this:

SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
Transaction.[Transaction Status]=[cboTransactionStatus];


As an additional test, make sure that the above query will return a value
for an item that you might select in the cboTransactionStatus combo box.


Your ControlSource for the cboPropertyFilter combo box also will prevent
you
from selecting anything in the second combo box:

=[Properties]![ID-Properties] «Expr» [Properties]![Property Address]

When a combo box is bound to an expression, you cannot change anything in
the combo box, including not being able to select anything. Is this
second
combo box supposed to be bound to a field in the form's RecordSource
query?
--

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

Ken,
I actually did add the following code to the first combo box after
update
line but I am still getting blank line on the second box!

Thanks,
AC


Option Compare Database

Private Sub cboTransactionStatus_AfterUpdate()
Me!cboPropertyFilter.Requery
End Sub



:

See this article:
http://www.mvps.org/access/forms/frm0028.htm

You need to use VBA code to requery the second combobox after you make
a
selection in the first combobox.
--

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




Urgent reply needed!

I am trying to implement cascading combo box where selection in one
combo
box determines the selections in another combo box. I can see the
selections
in the first combo box. When I make the selection, I see nothing in
the
other
combo box! It is suppose to list the items based on the selection
from
the
first combo box.

Please let me know if you have any general tips and/or look at the
details
of the setup below and let me know what the problem might be!

Thanks,
AC Erdal

Application is real estate. There are two primary tables "Property"
and
Transaction". Here is a summary of fields for both tables;

Property Transaction
ID-Properties ID-Transaction
Property Address Transaction Status
ID-Properties

Other table is the PropertyStatus
1 all
2 listed
3 active
4 pending
5 closed

On a form (Project Details-project), I created two combo boxes;

cboTransactionStatus
control source: unbound
row source: SELECT tblPropertyStatus.Status FROM tblPropertyStatus;

and,

cboPropertyFilter
control source: =[Properties]![ID-Properties] «Expr»
[Properties]![Property
Address]
row source: SELECT Transaction.[Transaction Status],
Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
(((Transaction.[Transaction Status])=[Forms]![Project
Details-project]![cboTransactionStatus]));

 

[ace]

Ken,

I entered the corresponding values below for the properties you asked.

I also did the new query. However I am not sure where I should be enteing
the value in the design grid? Below "criteria" and "or" statments?

When I tried to run the query, it asked me parameter for
properties.property. I am not sure what this was! I just entered "active"
and it took me to a datasheet view of the query. I tried to enter the
"active" there again in the status field and it did not accept it! it just
gave a audio sound!

Thanks,
AC

OK. Let's check a few properties before we go further.

For the first combo box (cboTransactionStatus), what are the values of the
following properties:
BoundColumn 1
ColumnCount 2
ColumnWidths 1";1"
RowSource SELECT tblPropertyStatus.Status FROM tblPropertyStatus;
RowSourceType Table/Query
ControlSource blank
Name cboTransactionStatus
OnAfterUpdate

For the second combo box (cboPropertyFilter), what are the values of the
following properties:
BoundColumn 1
ColumnCount 2
ColumnWidths 0"
RowSource SELECT Transaction.[Transaction Status], Properties.[Property Address], Properties.[ID-Properties] FROM Properties INNER JOIN [Transaction] ON Properties.[ID-Properties] = Transaction.[ID-Properties] WHERE (((Transaction.[Transaction Status])=[cboTransactionStatus]));
RowSourceType Table/Query
ControlSource blank
Name cboPropertyFilter


With regard to my suggestion about making sure the query for the
cboPropertyFilter combo box will return a record for a value that you might
select in the first combo box, create a new query in design view, do not
select any table in the popup window, and close that popup window. On the
upper left end of the toolbar, select the SQL View option in the View icon
button. Paste this SQL statement into the window that appears:

SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties]

Change the view back to Design (same button on toolbar). You'll now see an
unfiltered version of the query that you're using in the cboPropertyFilter
combobox.

In the design grid, under the Transaction Status field, enter a value in the
Where: box that would be found in the Status field of the tblPropertyStatus
table. Run the query to see if any record(s) is returned.


--

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

Hi Ken,

I did change the RowSource prooerty for cboPropertyFilter combo box as you
have requested, but still I am getting blank line in the second combo box.

you asked me to do something but I am not sure if there is anything else
you
wanted me to do with your statement;
"As an additional test, make sure that the above query will return a value
for an item that you might select in the cboTransactionStatus combo box."

Let me know what else I need to do again!

The second combo box is suppose to provide a list of proprty address based
on the proprety status. For example, if the "active" status is selected in
the first combo box, the second box is suppose provide a list address of
properties that are active.

Just to test it, I did remove the ControlSource expression for the
cboPropertyFilter combo box, and it also did not help!

Thanks,
AC

Try changing your RowSource property for cboPropertyFilter combo box to
this:

SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
Transaction.[Transaction Status]=[cboTransactionStatus];


As an additional test, make sure that the above query will return a value
for an item that you might select in the cboTransactionStatus combo box.


Your ControlSource for the cboPropertyFilter combo box also will prevent
you
from selecting anything in the second combo box:

=[Properties]![ID-Properties] «Expr» [Properties]![Property Address]

When a combo box is bound to an expression, you cannot change anything in
the combo box, including not being able to select anything. Is this
second
combo box supposed to be bound to a field in the form's RecordSource
query?
--

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





Ken,
I actually did add the following code to the first combo box after
update
line but I am still getting blank line on the second box!

Thanks,
AC


Option Compare Database

Private Sub cboTransactionStatus_AfterUpdate()
Me!cboPropertyFilter.Requery
End Sub



:

See this article:
http://www.mvps.org/access/forms/frm0028.htm

You need to use VBA code to requery the second combobox after you make
a
selection in the first combobox.
--

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




Urgent reply needed!

I am trying to implement cascading combo box where selection in one
combo
box determines the selections in another combo box. I can see the
selections
in the first combo box. When I make the selection, I see nothing in
the
other
combo box! It is suppose to list the items based on the selection
from
the
first combo box.

Please let me know if you have any general tips and/or look at the
details
of the setup below and let me know what the problem might be!

Thanks,
AC Erdal

Application is real estate. There are two primary tables "Property"
and
Transaction". Here is a summary of fields for both tables;

Property Transaction
ID-Properties ID-Transaction
Property Address Transaction Status
ID-Properties

Other table is the PropertyStatus
1 all
2 listed
3 active
4 pending
5 closed

On a form (Project Details-project), I created two combo boxes;

cboTransactionStatus
control source: unbound
row source: SELECT tblPropertyStatus.Status FROM tblPropertyStatus;

and,

cboPropertyFilter
control source: =[Properties]![ID-Properties] «Expr»
[Properties]![Property
Address]
row source: SELECT Transaction.[Transaction Status],
Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
(((Transaction.[Transaction Status])=[Forms]![Project
Details-project]![cboTransactionStatus]));

 

[Ken Snell [MVP]]

Sorry, when I typed "Where:" I meant to type "Criteria:".

The fact that you're being asked for a value for Properties.[ID-Properties]
indicates that your Properties table does not contain a field named
ID-Properties. That means that your second combo box cannot return any
records. This likely is the cause of the empty combo box.

You need to fix the query before we can do any more debugging. Build a query
that will return records WITHOUT any WHERE clause in it. Post that query
here, and then we'll continue with the fix.

--

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

Ken,

I entered the corresponding values below for the properties you asked.

I also did the new query. However I am not sure where I should be enteing
the value in the design grid? Below "criteria" and "or" statments?

When I tried to run the query, it asked me parameter for
properties.property. I am not sure what this was! I just entered "active"
and it took me to a datasheet view of the query. I tried to enter the
"active" there again in the status field and it did not accept it! it just
gave a audio sound!

Thanks,
AC

OK. Let's check a few properties before we go further.

For the first combo box (cboTransactionStatus), what are the values of
the
following properties:
BoundColumn 1
ColumnCount 2
ColumnWidths 1";1"
RowSource SELECT tblPropertyStatus.Status FROM
tblPropertyStatus;
RowSourceType Table/Query
ControlSource blank
Name cboTransactionStatus
OnAfterUpdate

For the second combo box (cboPropertyFilter), what are the values of the
following properties:
BoundColumn 1
ColumnCount 2
ColumnWidths 0"
RowSource SELECT Transaction.[Transaction Status],
Properties.[Property Address], Properties.[ID-Properties] FROM Properties
INNER JOIN [Transaction] ON Properties.[ID-Properties] =
Transaction.[ID-Properties] WHERE (((Transaction.[Transaction
Status])=[cboTransactionStatus]));
RowSourceType Table/Query
ControlSource blank
Name cboPropertyFilter


With regard to my suggestion about making sure the query for the
cboPropertyFilter combo box will return a record for a value that you
might
select in the first combo box, create a new query in design view, do not
select any table in the popup window, and close that popup window. On the
upper left end of the toolbar, select the SQL View option in the View
icon
button. Paste this SQL statement into the window that appears:

SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties]

Change the view back to Design (same button on toolbar). You'll now see
an
unfiltered version of the query that you're using in the
cboPropertyFilter
combobox.

In the design grid, under the Transaction Status field, enter a value in
the
Where: box that would be found in the Status field of the
tblPropertyStatus
table. Run the query to see if any record(s) is returned.


--

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

Hi Ken,

I did change the RowSource prooerty for cboPropertyFilter combo box as
you
have requested, but still I am getting blank line in the second combo
box.

you asked me to do something but I am not sure if there is anything
else
you
wanted me to do with your statement;
"As an additional test, make sure that the above query will return a
value
for an item that you might select in the cboTransactionStatus combo
box."

Let me know what else I need to do again!

The second combo box is suppose to provide a list of proprty address
based
on the proprety status. For example, if the "active" status is selected
in
the first combo box, the second box is suppose provide a list address
of
properties that are active.

Just to test it, I did remove the ControlSource expression for the
cboPropertyFilter combo box, and it also did not help!

Thanks,
AC

:

Try changing your RowSource property for cboPropertyFilter combo box
to
this:

SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
Transaction.[Transaction Status]=[cboTransactionStatus];


As an additional test, make sure that the above query will return a
value
for an item that you might select in the cboTransactionStatus combo
box.


Your ControlSource for the cboPropertyFilter combo box also will
prevent
you
from selecting anything in the second combo box:

=[Properties]![ID-Properties] «Expr» [Properties]![Property Address]

When a combo box is bound to an expression, you cannot change anything
in
the combo box, including not being able to select anything. Is this
second
combo box supposed to be bound to a field in the form's RecordSource
query?
--

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





Ken,
I actually did add the following code to the first combo box after
update
line but I am still getting blank line on the second box!

Thanks,
AC


Option Compare Database

Private Sub cboTransactionStatus_AfterUpdate()
Me!cboPropertyFilter.Requery
End Sub



:

See this article:
http://www.mvps.org/access/forms/frm0028.htm

You need to use VBA code to requery the second combobox after you
make
a
selection in the first combobox.
--

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




Urgent reply needed!

I am trying to implement cascading combo box where selection in
one
combo
box determines the selections in another combo box. I can see
the
selections
in the first combo box. When I make the selection, I see nothing
in
the
other
combo box! It is suppose to list the items based on the selection
from
the
first combo box.

Please let me know if you have any general tips and/or look at
the
details
of the setup below and let me know what the problem might be!

Thanks,
AC Erdal

Application is real estate. There are two primary tables
"Property"
and
Transaction". Here is a summary of fields for both tables;

Property Transaction
ID-Properties ID-Transaction
Property Address Transaction Status
ID-Properties

Other table is the PropertyStatus
1 all
2 listed
3 active
4 pending
5 closed

On a form (Project Details-project), I created two combo boxes;

cboTransactionStatus
control source: unbound
row source: SELECT tblPropertyStatus.Status FROM
tblPropertyStatus;

and,

cboPropertyFilter
control source: =[Properties]![ID-Properties] «Expr»
[Properties]![Property
Address]
row source: SELECT Transaction.[Transaction Status],
Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
(((Transaction.[Transaction Status])=[Forms]![Project
Details-project]![cboTransactionStatus]));

 

[ace]

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

Sorry, when I typed "Where:" I meant to type "Criteria:".

The fact that you're being asked for a value for Properties.[ID-Properties]
indicates that your Properties table does not contain a field named
ID-Properties. That means that your second combo box cannot return any
records. This likely is the cause of the empty combo box.

You need to fix the query before we can do any more debugging. Build a query
that will return records WITHOUT any WHERE clause in it. Post that query
here, and then we'll continue with the fix.

--

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

Ken,

I entered the corresponding values below for the properties you asked.

I also did the new query. However I am not sure where I should be enteing
the value in the design grid? Below "criteria" and "or" statments?

When I tried to run the query, it asked me parameter for
properties.property. I am not sure what this was! I just entered "active"
and it took me to a datasheet view of the query. I tried to enter the
"active" there again in the status field and it did not accept it! it just
gave a audio sound!

Thanks,
AC

OK. Let's check a few properties before we go further.

For the first combo box (cboTransactionStatus), what are the values of
the
following properties:
BoundColumn 1
ColumnCount 2
ColumnWidths 1";1"
RowSource SELECT tblPropertyStatus.Status FROM
tblPropertyStatus;
RowSourceType Table/Query
ControlSource blank
Name cboTransactionStatus
OnAfterUpdate

For the second combo box (cboPropertyFilter), what are the values of the
following properties:
BoundColumn 1
ColumnCount 2
ColumnWidths 0"
RowSource SELECT Transaction.[Transaction Status],
Properties.[Property Address], Properties.[ID-Properties] FROM Properties
INNER JOIN [Transaction] ON Properties.[ID-Properties] =
Transaction.[ID-Properties] WHERE (((Transaction.[Transaction
Status])=[cboTransactionStatus]));
RowSourceType Table/Query
ControlSource blank
Name cboPropertyFilter


With regard to my suggestion about making sure the query for the
cboPropertyFilter combo box will return a record for a value that you
might
select in the first combo box, create a new query in design view, do not
select any table in the popup window, and close that popup window. On the
upper left end of the toolbar, select the SQL View option in the View
icon
button. Paste this SQL statement into the window that appears:

SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties]

Change the view back to Design (same button on toolbar). You'll now see
an
unfiltered version of the query that you're using in the
cboPropertyFilter
combobox.

In the design grid, under the Transaction Status field, enter a value in
the
Where: box that would be found in the Status field of the
tblPropertyStatus
table. Run the query to see if any record(s) is returned.


--

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




Hi Ken,

I did change the RowSource prooerty for cboPropertyFilter combo box as
you
have requested, but still I am getting blank line in the second combo
box.

you asked me to do something but I am not sure if there is anything
else
you
wanted me to do with your statement;
"As an additional test, make sure that the above query will return a
value
for an item that you might select in the cboTransactionStatus combo
box."

Let me know what else I need to do again!

The second combo box is suppose to provide a list of proprty address
based
on the proprety status. For example, if the "active" status is selected
in
the first combo box, the second box is suppose provide a list address
of
properties that are active.

Just to test it, I did remove the ControlSource expression for the
cboPropertyFilter combo box, and it also did not help!

Thanks,
AC

:

Try changing your RowSource property for cboPropertyFilter combo box
to
this:

SELECT Transaction.[Transaction Status], Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
Transaction.[Transaction Status]=[cboTransactionStatus];


As an additional test, make sure that the above query will return a
value
for an item that you might select in the cboTransactionStatus combo
box.


Your ControlSource for the cboPropertyFilter combo box also will
prevent
you
from selecting anything in the second combo box:

=[Properties]![ID-Properties] «Expr» [Properties]![Property Address]

When a combo box is bound to an expression, you cannot change anything
in
the combo box, including not being able to select anything. Is this
second
combo box supposed to be bound to a field in the form's RecordSource
query?
--

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





Ken,
I actually did add the following code to the first combo box after
update
line but I am still getting blank line on the second box!

Thanks,
AC


Option Compare Database

Private Sub cboTransactionStatus_AfterUpdate()
Me!cboPropertyFilter.Requery
End Sub



:

See this article:
http://www.mvps.org/access/forms/frm0028.htm

You need to use VBA code to requery the second combobox after you
make
a
selection in the first combobox.
--

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




Urgent reply needed!

I am trying to implement cascading combo box where selection in
one
combo
box determines the selections in another combo box. I can see
the
selections
in the first combo box. When I make the selection, I see nothing
in
the
other
combo box! It is suppose to list the items based on the selection
from
the
first combo box.

Please let me know if you have any general tips and/or look at
the
details
of the setup below and let me know what the problem might be!

Thanks,
AC Erdal

Application is real estate. There are two primary tables
"Property"
and
Transaction". Here is a summary of fields for both tables;

Property Transaction
ID-Properties ID-Transaction
Property Address Transaction Status
ID-Properties

Other table is the PropertyStatus
1 all
2 listed
3 active
4 pending
5 closed

On a form (Project Details-project), I created two combo boxes;

cboTransactionStatus
control source: unbound
row source: SELECT tblPropertyStatus.Status FROM
tblPropertyStatus;

and,

cboPropertyFilter
control source: =[Properties]![ID-Properties] «Expr»
[Properties]![Property
Address]
row source: SELECT Transaction.[Transaction Status],
Properties.[Property
Address], Properties.[ID-Properties] FROM Properties INNER JOIN
[Transaction]
ON Properties.[ID-Properties]=Transaction.[ID-Properties] WHERE
(((Transaction.[Transaction Status])=[Forms]![Project
Details-project]![cboTransactionStatus]));

 

[Ken Snell [MVP]]

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.

 

[Jeff Boyce]

Which version of Access are you using?

Have you applied all the updates/patches/service packs?

There was an issue with comboboxes that was resolved with an SP, as I
recall.

Search on-line for "combobox" and "MS Access"...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Ken Snell [MVP]]

Also, my apologies that I didn't respond to the properties that you posted
for the two combo boxes. Many of the properties that you have are incorrect.
I've listed below the correct values that you need for the combo boxes'
properties:

For the first combo box (cboTransactionStatus):
ColumnCount 1 ( not 2 )
ColumnWidths 1" ( not 1";1" )
OnAfterUpdate [Event Procedure]

The fact that you have no value for the OnAfterUpdate property indicates
that your VBA code never got attached to the combo box, so it's never
running. Click in the empty box for that property, select [Event Procedure]
from the dropdown list, then click on the three-dot box at right edge of the
textbox. That will connect your code to the combo box.

For the second combo box (cboPropertyFilter):
BoundColumn 3 ( I am assuming that the second combo box
is supposed to store a property ID
value, which
is the third column in your query )
ColumnCount 3 ( not 2 )
ColumnWidths 0"; 2"; 0" ( not 0" )

Your current setting of 0" for ColumnWidths tells ACCESS to display NOTHING
in the combo box dropdown list nor in the textbox portion of the combo box.

You need to make these changes to your properties. However, these changes
will not fix your problem completely. As we've already discussed, it's
necessary that your query for this second combo box returns records when
there is no filtering on it. When we have a good query, then we can finish
the fix.
--

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


--

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

 

[ace]

Ken,

I created a totally new database with two tables;

PropertyTest
ID-Properties
Property Address
TransactionTest
ID-Transactions
Transaction Status
ID-Properties

Then I created a query using the query design tool. The problem is I cannot
get it display both the "Property Address" as well as the "Transaction
Status" at the same time. I can either display the status or the address, not
both. This display is based on the type of relationship.

If I use the join properties selection #1, no data shows. If #2, only
address shows, and #3 only status shows.

I think this is the first fundamental issue that we need to resolve.

How do I get both info to show?

Thanks,
AC

Also, my apologies that I didn't respond to the properties that you posted
for the two combo boxes. Many of the properties that you have are incorrect.
I've listed below the correct values that you need for the combo boxes'
properties:

For the first combo box (cboTransactionStatus):
ColumnCount 1 ( not 2 )
ColumnWidths 1" ( not 1";1" )
OnAfterUpdate [Event Procedure]

The fact that you have no value for the OnAfterUpdate property indicates
that your VBA code never got attached to the combo box, so it's never
running. Click in the empty box for that property, select [Event Procedure]
from the dropdown list, then click on the three-dot box at right edge of the
textbox. That will connect your code to the combo box.

For the second combo box (cboPropertyFilter):
BoundColumn 3 ( I am assuming that the second combo box
is supposed to store a property ID
value, which
is the third column in your query )
ColumnCount 3 ( not 2 )
ColumnWidths 0"; 2"; 0" ( not 0" )

Your current setting of 0" for ColumnWidths tells ACCESS to display NOTHING
in the combo box dropdown list nor in the textbox portion of the combo box.

You need to make these changes to your properties. However, these changes
will not fix your problem completely. As we've already discussed, it's
necessary that your query for this second combo box returns records when
there is no filtering on it. When we have a good query, then we can finish
the fix.
--

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


--

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

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/

 

[ace]

Ken,
Is there a way I can send attachment to You?
AC

Also, my apologies that I didn't respond to the properties that you posted
for the two combo boxes. Many of the properties that you have are incorrect.
I've listed below the correct values that you need for the combo boxes'
properties:

For the first combo box (cboTransactionStatus):
ColumnCount 1 ( not 2 )
ColumnWidths 1" ( not 1";1" )
OnAfterUpdate [Event Procedure]

The fact that you have no value for the OnAfterUpdate property indicates
that your VBA code never got attached to the combo box, so it's never
running. Click in the empty box for that property, select [Event Procedure]
from the dropdown list, then click on the three-dot box at right edge of the
textbox. That will connect your code to the combo box.

For the second combo box (cboPropertyFilter):
BoundColumn 3 ( I am assuming that the second combo box
is supposed to store a property ID
value, which
is the third column in your query )
ColumnCount 3 ( not 2 )
ColumnWidths 0"; 2"; 0" ( not 0" )

Your current setting of 0" for ColumnWidths tells ACCESS to display NOTHING
in the combo box dropdown list nor in the textbox portion of the combo box.

You need to make these changes to your properties. However, these changes
will not fix your problem completely. As we've already discussed, it's
necessary that your query for this second combo box returns records when
there is no filtering on it. When we have a good query, then we can finish
the fix.
--

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


--

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

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 Snell [MVP]]

The query that you'd need (SQL statement) is this, if ID-Properties is the
foreign key in the TransactionTest table so that a record relates to a
record in the PropertyTest table. This query will return records ONLY when a
record in the PropertyTest table has a matching record or records in the
TransactionTest table:

SELECT PropertyTest.[Property Address],
TransactionTest.Transaction Status,
TransactionTest.[ID-Properties]
FROM PropertyTest INNER JOIN
TransactionTest ON
PropertyTest.[ID-Properties] = TransactionTest.[ID-Properties];



This query will return all records from the PropertyTest table regardless of
whether there is one ore matching records in the TransactionTest table:

SELECT PropertyTest.[Property Address],
TransactionTest.Transaction Status,
TransactionTest.[ID-Properties]
FROM PropertyTest LEFT JOIN
TransactionTest ON
PropertyTest.[ID-Properties] = TransactionTest.[ID-Properties];



This query will return all records from the TransactionTest table regardless
of whether there is one ore matching records in the PropertyTest table:

SELECT PropertyTest.[Property Address],
TransactionTest.Transaction Status,
TransactionTest.[ID-Properties]
FROM PropertyTest RIGHT JOIN
TransactionTest ON
PropertyTest.[ID-Properties] = TransactionTest.[ID-Properties];


For the above queries to work, the values in the
TransactionTest.[ID-Properties] field must match values that are in the
PropertyTest.[ID-Properties] field. Do they match in your sample data?

--

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

Ken,

I created a totally new database with two tables;

PropertyTest
ID-Properties
Property Address
TransactionTest
ID-Transactions
Transaction Status
ID-Properties

Then I created a query using the query design tool. The problem is I
cannot
get it display both the "Property Address" as well as the "Transaction
Status" at the same time. I can either display the status or the address,
not
both. This display is based on the type of relationship.

If I use the join properties selection #1, no data shows. If #2, only
address shows, and #3 only status shows.

I think this is the first fundamental issue that we need to resolve.

How do I get both info to show?

Thanks,
AC

Also, my apologies that I didn't respond to the properties that you
posted
for the two combo boxes. Many of the properties that you have are
incorrect.
I've listed below the correct values that you need for the combo boxes'
properties:

For the first combo box (cboTransactionStatus):
ColumnCount 1 ( not 2 )
ColumnWidths 1" ( not 1";1" )
OnAfterUpdate [Event Procedure]

The fact that you have no value for the OnAfterUpdate property indicates
that your VBA code never got attached to the combo box, so it's never
running. Click in the empty box for that property, select [Event
Procedure]
from the dropdown list, then click on the three-dot box at right edge of
the
textbox. That will connect your code to the combo box.

For the second combo box (cboPropertyFilter):
BoundColumn 3 ( I am assuming that the second combo box
is supposed to store a property
ID
value, which
is the third column in your
query )
ColumnCount 3 ( not 2 )
ColumnWidths 0"; 2"; 0" ( not 0" )

Your current setting of 0" for ColumnWidths tells ACCESS to display
NOTHING
in the combo box dropdown list nor in the textbox portion of the combo
box.

You need to make these changes to your properties. However, these changes
will not fix your problem completely. As we've already discussed, it's
necessary that your query for this second combo box returns records when
there is no filtering on it. When we have a good query, then we can
finish
the fix.
--

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


--

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

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

 

[ace]

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

 

[Ken Snell [MVP]]

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/

 

[ace]

Ken,
Thanks for your help and being patient!

I have one more critical question. It is regarding my mistake not to enter
manually the ID-Properties code in the records in Transaction table. I
understand that primary key code is generated automatically for any table.
However, I do not understand how the foreign keys are suppose to be
generated! for example the Transaction table has five foreign keys. How do I
keep these foreign keys updated as the new records are generated with a new
primary key? I am hoping this can be done automatically but do not understand
how?

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

 

[ace]

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