Changing rowsource when dropdown the combobox

[Nicolae Fieraru]

Hi All,

I am trying to change the rowsource of a combobox when I click on it. I
played with many events, associated with the form and the combobox, but
still haven't figured out what is the way of doing it.
I have a table with products, tblProducts, some of them are Active while
others are Inactive.
The form shows all the products purchased by a customer, both Active and
Inactive in a ComboBox, cbProducts.
My client wants to view all the products purchased by a customer in this
combobox. But when he clicks on a combobox, in the dropdown should show the
active or inactive products, depending on the state of a checkbox.
So, I set the rowsource of the combobox to tblProducts and the combobox
shows all the products purchased in the continuous form. When I click on the
combobox and the dropdown shows up, if the checkbox is set to "show only
active products in dropdown", I want to change the rowsource to a query,
qryActiveProducts. When I leave the combobox, I want to show again in the
combobox all the active and inactive products

Any help appreciated

Regards,
Nicolae

[Mike Preston]

On Mon, 12 Jul 2004 13:25:38 +1000, "Nicolae Fieraru"
<no****@please.cxm> wrote:

Hi All,

I am trying to change the rowsource of a combobox when I click on it. I
played with many events, associated with the form and the combobox, but
still haven't figured out what is the way of doing it.
I have a table with products, tblProducts, some of them are Active while
others are Inactive.
The form shows all the products purchased by a customer, both Active and
Inactive in a ComboBox, cbProducts.
My client wants to view all the products purchased by a customer in this
combobox. But when he clicks on a combobox, in the dropdown should show the
active or inactive products, depending on the state of a checkbox.
So, I set the rowsource of the combobox to tblProducts and the combobox
shows all the products purchased in the continuous form. When I click on the
combobox and the dropdown shows up, if the checkbox is set to "show only
active products in dropdown", I want to change the rowsource to a query,
qryActiveProducts. When I leave the combobox, I want to show again in the
combobox all the active and inactive products

Nicolae,

Clarify something for me. If every time you enter the combobox you
want the drop down list to reflect the value of the checkbox, why
would you go through the effort of resetting the combobox dropdown so
that it no longer accurately reflects the value of the checkbox?
Wouldn't that just be extra work that is transparent because when you
reenter the combobox you will again reset the rowsource to accurately
reflect the checkbox?

Maybe that question will help you figure out what events to use.

My thinking is that you take the AfterUpdate event of the checkbox and
you do something like:

Private Sub chkActiveOnly_AfterUpdate()
Dim strAllProductsSQL As String
' Set the rowsourse SQL to use if chkActiveOnly is not checked
' watch word wrap
strAllProductsSQL = "Select tblProductsID, tblProductsName from " & _
"tblProducts order by tblProductsName"

If chkActiveOnly = -1 Then ' We want only the actives
Dim db As DAO.Database
Set db = CurrentDb()
Dim qryDefs As DAO.QueryDefs
Dim qryDef As DAO.QueryDef
For Each qryDef In db.QueryDefs
If qryDef.Name = "qryActiveProducts" Then
Me.cbProducts.RowSource = qryDef.SQL
End If
Next qryDef
db.Close
Set db = Nothing
Else
cbProducts.RowSource = strAllProductsSQL
End If

End Sub

Try that.

mike

[Stewart Allen]

Hi Nicolae,
Place this code behind the form:

Private Sub ToggleCombo(fCheckState as Boolean)
If fCheckState = True Then
Me!cbProducts.RowSource = "qryActiveProducts"
Else
Me!cbProducts.RowSource = "tblProducts"
End If
End Sub

Behind the AfterUpdate event of the check box copy this code:
ToggleCombo Me!YourCheckBoxName

In the Exit Event of your combo box use this code:
Me!YourCheckBoxName = False
ToggleCombo Me!YourCheckBoxName

The first is a sub routine that changes the row source of your combo box
depending on the state of the check box. This is called whenever you click
the checkbox or when you leave the combo box. Also when you leave the combo
box the check box is set back to False so the sub routine can call the
appropriate row source.

What I do in this type of situation is don't set the combo's rowsource using
the properties but call the ToggleCombo with the form's On Current event.

Stewart
"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...

Hi All,

I am trying to change the rowsource of a combobox when I click on it. I
played with many events, associated with the form and the combobox, but
still haven't figured out what is the way of doing it.
I have a table with products, tblProducts, some of them are Active while
others are Inactive.
The form shows all the products purchased by a customer, both Active and
Inactive in a ComboBox, cbProducts.
My client wants to view all the products purchased by a customer in this
combobox. But when he clicks on a combobox, in the dropdown should show the active or inactive products, depending on the state of a checkbox.
So, I set the rowsource of the combobox to tblProducts and the combobox
shows all the products purchased in the continuous form. When I click on the combobox and the dropdown shows up, if the checkbox is set to "show only
active products in dropdown", I want to change the rowsource to a query,
qryActiveProducts. When I leave the combobox, I want to show again in the
combobox all the active and inactive products

Any help appreciated

Regards,
Nicolae

[Nicolae Fieraru]

Hi Mike,

The problem is like this: there is a form which can navigate through the
customers. A continous subform shows all the products bought by the
customers, both active and inactive. There is a navigator for the customers
and one navigator for the products purchased. The user of the database can
add in the same form and subform either a new user or a new product
purchased (or both). When somebody wants to add a new product purchased,
they fill in the details in the subform and in the combobox they want to see
only the active products (there are lots of inactive products and it is
pointless to display them when adding a new product). But sometimes the same
main form can be used to check what products have been purchased over the
time by customer X. And my client doesn't want to see blank comboboxes
corresponding to inactive products.
I can't use the event of the checkbox, because when I open the form, the
combobox is set by default on "show only active products". If my combobox
rowsource is set to the query, it shows only active products, all the
inactive products will be shown as blank. If I have the rowsource as
tblProducts, when I dropdown the combobox, the event on the checkbox won't
be triggered and the dropdown will contain all products.

Thanks for your help

Regards,
Nicolae

"Mike Preston" <mb**************@pacbell.net> wrote in message
news:40****************@news.INDIVIDUAL.NET...

On Mon, 12 Jul 2004 13:25:38 +1000, "Nicolae Fieraru"
<no****@please.cxm> wrote:

Hi All,

I am trying to change the rowsource of a combobox when I click on it. I
played with many events, associated with the form and the combobox, but
still haven't figured out what is the way of doing it.
I have a table with products, tblProducts, some of them are Active while
others are Inactive.
The form shows all the products purchased by a customer, both Active and
Inactive in a ComboBox, cbProducts.
My client wants to view all the products purchased by a customer in this
combobox. But when he clicks on a combobox, in the dropdown should show theactive or inactive products, depending on the state of a checkbox.
So, I set the rowsource of the combobox to tblProducts and the combobox
shows all the products purchased in the continuous form. When I click on thecombobox and the dropdown shows up, if the checkbox is set to "show only
active products in dropdown", I want to change the rowsource to a query,
qryActiveProducts. When I leave the combobox, I want to show again in the
combobox all the active and inactive products

Nicolae,

Clarify something for me. If every time you enter the combobox you
want the drop down list to reflect the value of the checkbox, why
would you go through the effort of resetting the combobox dropdown so
that it no longer accurately reflects the value of the checkbox?
Wouldn't that just be extra work that is transparent because when you
reenter the combobox you will again reset the rowsource to accurately
reflect the checkbox?

Maybe that question will help you figure out what events to use.

My thinking is that you take the AfterUpdate event of the checkbox and
you do something like:

Private Sub chkActiveOnly_AfterUpdate()
Dim strAllProductsSQL As String
' Set the rowsourse SQL to use if chkActiveOnly is not checked
' watch word wrap
strAllProductsSQL = "Select tblProductsID, tblProductsName from " & _
"tblProducts order by tblProductsName"

If chkActiveOnly = -1 Then ' We want only the actives
Dim db As DAO.Database
Set db = CurrentDb()
Dim qryDefs As DAO.QueryDefs
Dim qryDef As DAO.QueryDef
For Each qryDef In db.QueryDefs
If qryDef.Name = "qryActiveProducts" Then
Me.cbProducts.RowSource = qryDef.SQL
End If
Next qryDef
db.Close
Set db = Nothing
Else
cbProducts.RowSource = strAllProductsSQL
End If

End Sub

Try that.

mike

[Mike Preston]

On Mon, 12 Jul 2004 16:18:00 +1000, "Nicolae Fieraru"
<no****@please.cxm> wrote:

Hi Mike,

The problem is like this: there is a form which can navigate through the
customers. A continous subform shows all the products bought by the
customers, both active and inactive.
What is the annotation on the subform's information that identifies
whether a product is active or inactive?
There is a navigator for the customers
and one navigator for the products purchased.
By "navigator" I assume you mean a combobox that, when selected,
brings up certain information that is specific to the selection,
right? Why would you have a navigator on the subform with respect to
the customers? Wouldn't that be on the main form? Suffice it to say
that I just am having a hard time trying to understand what is going
on.
The user of the database can
add in the same form and subform either a new user or a new product
purchased (or both).
The form both displays information with respect to an existing
customer and allows the user the ability to add a new customer? The
subform both displays information on existing products and it allows
the user to add new products?

This is a mighty powerful form and subform you have there.
When somebody wants to add a new product purchased,
they fill in the details in the subform and in the combobox they want to see
only the active products (there are lots of inactive products and it is
pointless to display them when adding a new product).
I would certainly agree with that.

It sounds like the subform has a recordsource that you need to modify
to exclude the inactive products. Is that right?
But sometimes the same
main form can be used to check what products have been purchased over the
time by customer X.
Unfortunately, this doesn't make too much sense to me. Is there a
second sub-form that identifies the historical purchases?
And my client doesn't want to see blank comboboxes
corresponding to inactive products.
This would be rectified by eliminating the inactive products from the
subforms recordsource.
I can't use the event of the checkbox, because when I open the form, the
combobox is set by default on "show only active products".
But you yourself said you wanted to use the checkbox's selection as
the driving force. I don't think the fact that the checkbox is in one
state or another modifies the efficacy of checking the checkbox for a
change in value and then modifying your rowsource (or record source in
the case of a subform) appropriately.
If my combobox
rowsource is set to the query, it shows only active products, all the
inactive products will be shown as blank. If I have the rowsource as
tblProducts, when I dropdown the combobox, the event on the checkbox won't
be triggered and the dropdown will contain all products.
I'm sorry, but I haven't the foggiest idea what you are driving at
here. Maybe somebody else can pick up where you left off (trying to
get a single check box to control a single combobox's rowsource), add
in the above and come up with something.

Good luck.

mike

"Mike Preston" <mb**************@pacbell.net> wrote in message
news:40****************@news.INDIVIDUAL.NET...

On Mon, 12 Jul 2004 13:25:38 +1000, "Nicolae Fieraru"
<no****@please.cxm> wrote:

>Hi All,
>
>I am trying to change the rowsource of a combobox when I click on it. I
>played with many events, associated with the form and the combobox, but
>still haven't figured out what is the way of doing it.
>I have a table with products, tblProducts, some of them are Active while
>others are Inactive.
>The form shows all the products purchased by a customer, both Active and
>Inactive in a ComboBox, cbProducts.
>My client wants to view all the products purchased by a customer in this
>combobox. But when he clicks on a combobox, in the dropdown should showthe >active or inactive products, depending on the state of a checkbox.
>So, I set the rowsource of the combobox to tblProducts and the combobox
>shows all the products purchased in the continuous form. When I click onthe >combobox and the dropdown shows up, if the checkbox is set to "show only
>active products in dropdown", I want to change the rowsource to a query,
>qryActiveProducts. When I leave the combobox, I want to show again in the
>combobox all the active and inactive products

Nicolae,

Clarify something for me. If every time you enter the combobox you
want the drop down list to reflect the value of the checkbox, why
would you go through the effort of resetting the combobox dropdown so
that it no longer accurately reflects the value of the checkbox?
Wouldn't that just be extra work that is transparent because when you
reenter the combobox you will again reset the rowsource to accurately
reflect the checkbox?

Maybe that question will help you figure out what events to use.

My thinking is that you take the AfterUpdate event of the checkbox and
you do something like:

Private Sub chkActiveOnly_AfterUpdate()
Dim strAllProductsSQL As String
' Set the rowsourse SQL to use if chkActiveOnly is not checked
' watch word wrap
strAllProductsSQL = "Select tblProductsID, tblProductsName from " & _
"tblProducts order by tblProductsName"

If chkActiveOnly = -1 Then ' We want only the actives
Dim db As DAO.Database
Set db = CurrentDb()
Dim qryDefs As DAO.QueryDefs
Dim qryDef As DAO.QueryDef
For Each qryDef In db.QueryDefs
If qryDef.Name = "qryActiveProducts" Then
Me.cbProducts.RowSource = qryDef.SQL
End If
Next qryDef
db.Close
Set db = Nothing
Else
cbProducts.RowSource = strAllProductsSQL
End If

End Sub

Try that.

mike

[Nicolae Fieraru]

Hi Mike,
"Mike Preston" <mb**************@pacbell.net> wrote in message
news:40****************@news.INDIVIDUAL.NET...

On Mon, 12 Jul 2004 16:18:00 +1000, "Nicolae Fieraru"
<no****@please.cxm> wrote:

Hi Mike,

The problem is like this: there is a form which can navigate through the
customers. A continous subform shows all the products bought by the
customers, both active and inactive.
What is the annotation on the subform's information that identifies
whether a product is active or inactive?

In tblProductDetails there is a code for products. If the code = 55 then
that is an inactive products. All the other numbers are for valid prooducts.

There is a navigator for the customers
and one navigator for the products purchased.
By "navigator" I assume you mean a combobox that, when selected,
brings up certain information that is specific to the selection,
right? Why would you have a navigator on the subform with respect to
the customers? Wouldn't that be on the main form? Suffice it to say
that I just am having a hard time trying to understand what is going
on.

No, I was referring at the form and subform. Each of them has a navigator,
which allows to move through records.
The navigator on the form is for customers and the navigator on the subform
is for products.

The user of the database can
add in the same form and subform either a new user or a new product
purchased (or both).

The form both displays information with respect to an existing
customer and allows the user the ability to add a new customer? The
subform both displays information on existing products and it allows
the user to add new products?

No, the main form is for customers. You can add a new customer there. The
subform allows you to add a new purchase. This is a mighty powerful form and subform you have there.

When somebody wants to add a new product purchased,
they fill in the details in the subform and in the combobox they want to seeonly the active products (there are lots of inactive products and it is
pointless to display them when adding a new product).
I would certainly agree with that.

It sounds like the subform has a recordsource that you need to modify
to exclude the inactive products. Is that right?

No, it isn't right. They want to view the inactive products when the
combobox is in its normal (not dropped down state)
Only when the combobox is dropped down, the recordsource has to contain only
the active products.

But sometimes the same
main form can be used to check what products have been purchased over the
time by customer X.

Unfortunately, this doesn't make too much sense to me. Is there a
second sub-form that identifies the historical purchases?

Both form and subform display the information in continous mode.

And my client doesn't want to see blank comboboxes
corresponding to inactive products.
This would be rectified by eliminating the inactive products from the
subforms recordsource.

Here is where my problem begins, because if I remove the inactive products
from there, I won't see the inactive products which have been purchased by
customers before...

I can't use the event of the checkbox, because when I open the form, the
combobox is set by default on "show only active products".
But you yourself said you wanted to use the checkbox's selection as
the driving force. I don't think the fact that the checkbox is in one
state or another modifies the efficacy of checking the checkbox for a
change in value and then modifying your rowsource (or record source in
the case of a subform) appropriately.

Yes, when I drop down the combobox, it has to check the state of the
checkbox and if the checkbox is true, it should show all products, and if
not, it should show only active products (in dropdown)

If my combobox
rowsource is set to the query, it shows only active products, all the
inactive products will be shown as blank. If I have the rowsource as
tblProducts, when I dropdown the combobox, the event on the checkbox won'tbe triggered and the dropdown will contain all products.

I'm sorry, but I haven't the foggiest idea what you are driving at
here. Maybe somebody else can pick up where you left off (trying to
get a single check box to control a single combobox's rowsource), add
in the above and come up with something.

Good luck.

mike

Thank you for your help. I might not have explained too well what the
situation is and what I require. If you want to have a further look, I could
send you a tiny database, to see for yourself what my problem is.

Regards,
Nicolae

[Mike Preston]

On Fri, 16 Jul 2004 09:26:56 +1000, "Nicolae Fieraru"
<no****@please.cxm> wrote:

But you yourself said you wanted to use the checkbox's selection as
the driving force. I don't think the fact that the checkbox is in one
state or another modifies the efficacy of checking the checkbox for a
change in value and then modifying your rowsource (or record source in
the case of a subform) appropriately.

Yes, when I drop down the combobox, it has to check the state of the
checkbox and if the checkbox is true, it should show all products, and if
not, it should show only active products (in dropdown)
That is what the original suggestion I made would do. Have you tried
it?
Thank you for your help. I might not have explained too well what the
situation is and what I require. If you want to have a further look, I could
send you a tiny database, to see for yourself what my problem is.

If you can post it somewhere for downloading, that would be better.

mike

[Bob Quintal]

"Nicolae Fieraru" <no****@please.cxm> wrote in
news:40******@duster.adelaide.on.net:

Hi Mike,

The problem is like this: there is a form which can navigate
through the customers. A continous subform shows all the
products bought by the customers, both active and inactive.
There is a navigator for the customers and one navigator for
the products purchased. The user of the database can add in
the same form and subform either a new user or a new product
purchased (or both). When somebody wants to add a new product
purchased, they fill in the details in the subform and in the
combobox they want to see only the active products (there are
lots of inactive products and it is pointless to display them
when adding a new product). But sometimes the same main form
can be used to check what products have been purchased over
the time by customer X. And my client doesn't want to see
blank comboboxes corresponding to inactive products.
I can't use the event of the checkbox, because when I open the
form, the combobox is set by default on "show only active
products". If my combobox rowsource is set to the query, it
shows only active products, all the inactive products will be
shown as blank. If I have the rowsource as tblProducts, when I
dropdown the combobox, the event on the checkbox won't be
triggered and the dropdown will contain all products.

Thanks for your help

Regards,
Nicolae

Sounds to me that you don't need the checkbox at all. What you
want is to have the combobox display the active items only on a
new record, I'd use the form's .NewRecord to change the
..rowsource, putting the code in the OnCurrent event.

Bob Quintal

"Mike Preston" <mb**************@pacbell.net> wrote in message
news:40****************@news.INDIVIDUAL.NET...

On Mon, 12 Jul 2004 13:25:38 +1000, "Nicolae Fieraru"
<no****@please.cxm> wrote:

>Hi All,
>
>I am trying to change the rowsource of a combobox when I
>click on it. I played with many events, associated with the
>form and the combobox, but still haven't figured out what is
>the way of doing it. I have a table with products,
>tblProducts, some of them are Active while others are
>Inactive. The form shows all the products purchased by a
>customer, both Active and Inactive in a ComboBox,
>cbProducts. My client wants to view all the products
>purchased by a customer in this combobox. But when he clicks
>on a combobox, in the dropdown should show the >active or inactive products, depending on the state of a
>checkbox. So, I set the rowsource of the combobox to
>tblProducts and the combobox shows all the products
>purchased in the continuous form. When I click on the >combobox and the dropdown shows up, if the checkbox is set
>to "show only active products in dropdown", I want to change
>the rowsource to a query, qryActiveProducts. When I leave
>the combobox, I want to show again in the combobox all the
>active and inactive products

Nicolae,

Clarify something for me. If every time you enter the
combobox you want the drop down list to reflect the value of
the checkbox, why would you go through the effort of
resetting the combobox dropdown so that it no longer
accurately reflects the value of the checkbox? Wouldn't that
just be extra work that is transparent because when you
reenter the combobox you will again reset the rowsource to
accurately reflect the checkbox?

Maybe that question will help you figure out what events to
use.

My thinking is that you take the AfterUpdate event of the
checkbox and you do something like:

Private Sub chkActiveOnly_AfterUpdate()
Dim strAllProductsSQL As String
' Set the rowsourse SQL to use if chkActiveOnly is not
checked ' watch word wrap
strAllProductsSQL = "Select tblProductsID, tblProductsName
from " & _
"tblProducts order by
tblProductsName"

If chkActiveOnly = -1 Then ' We want only the actives
Dim db As DAO.Database
Set db = CurrentDb()
Dim qryDefs As DAO.QueryDefs
Dim qryDef As DAO.QueryDef
For Each qryDef In db.QueryDefs
If qryDef.Name = "qryActiveProducts" Then
Me.cbProducts.RowSource = qryDef.SQL
End If
Next qryDef
db.Close
Set db = Nothing
Else
cbProducts.RowSource = strAllProductsSQL
End If

End Sub

Try that.

mike