dynamically changing selection options for a combo using row sourc

[smarty]

Hi,

I am using forms bound to stored procedures using the row source property
that allow users to enter values by selecting combo boxes based on
table/stored procedure lookups.

I now need to be able to reduce the available selection option in my second
combo based on the selected value in the first.

As and example I have two combo boxes producs and models. The first would
be populated using the products table below. The second would be populated
using the models table below. If the user selected television on the
products combo the the models selection option would be reduced to colour and
black n white. How would this be acheived?

Products table
Television
Radio

Model table
Colour Television
Black n White Television
DAB Radio
Analogue Radio

 

[Peter Yang [MSFT]]

Hello,

You could try to create a new parameter query and use the first combobox
value as the parameter. For example:

SELECT *
FROM models
WHERE (((models.productname)=[forms]![comb]![combo0]));

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

 

[smarty]

This sound like what I want but I it returns server filter errors. 'Invalid
SQL Statement check the server filter on the form record source. I am not
sure if switching this property on will allow the it to work but I get errors
if I do because I am using forms bound to stored procedures.

Any ideas?

Hello,

You could try to create a new parameter query and use the first combobox
value as the parameter. For example:

SELECT *
FROM models
WHERE (((models.productname)=[forms]![comb]![combo0]));

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

Thread-Topic: dynamically changing selection options for a combo using row sourc
thread-index: AcZG8yyrK1fzCUMSQRuirAekvjeObw==
X-WBNR-Posting-Host: 82.33.57.189
From: =?Utf-8?B?c21hcnR5?= <[email protected]>
Subject: dynamically changing selection options for a combo using row sourc
Date: Mon, 13 Mar 2006 15:09:26 -0800
Lines: 25
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Newsgroups: microsoft.public.access.adp.sqlserver
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.access.adp.sqlserver:24033
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
X-Tomcat-NG: microsoft.public.access.adp.sqlserver

Hi,

I am using forms bound to stored procedures using the row source property
that allow users to enter values by selecting combo boxes based on
table/stored procedure lookups.

I now need to be able to reduce the available selection option in my second
combo based on the selected value in the first.

As and example I have two combo boxes producs and models. The first would
be populated using the products table below. The second would be populated
using the models table below. If the user selected television on the
products combo the the models selection option would be reduced to colour and
black n white. How would this be acheived?

Products table
Television
Radio

Model table
Colour Television
Black n White Television
DAB Radio
Analogue Radio

 

[giorgio rancati]

Hi Smarty,

if the name of first combobox is cmbProduct set the parameter of cmbModel's
stored procedure whit the same name of cmbProduct control
----
CREATE Procedure cmbModel
@cmbProduct
AS
SELECT *
FROM Model
Where Product = @cmbProduct
----

in cmbProduct afterupdate event requery the cmbModel combobox.
----
Private Sub cmbProduct_AfterUpdate()

Me.cmbModel.Requery

End Sub

 

[giorgio rancati]

ops, I forgot the parameter's datatype

----
CREATE Procedure cmbModel
@cmbProduct varchar(20)
AS
SELECT *
FROM Model
Where Product = @cmbProduct

 

[smarty]

Thanks, that did work.

I also have a few subforms displayed as a data sheet that I use in a similar
way to the combo boxes but they also allow editing of the records. is there
any way for one of these subforms to reference text boxs on one of the other
subforms to determine what records are retrieved?

To clarrify each subform will act in a similar way to the product, model
relationship where selecting a product will then determine which models are
displayed but will also allow a user to add models or products.

ops, I forgot the parameter's datatype

----
CREATE Procedure cmbModel
@cmbProduct varchar(20)
AS
SELECT *
FROM Model
Where Product = @cmbProduct
----

--
Giorgio Rancati
[Office Access MVP]

----
CREATE Procedure cmbModel
@cmbProduct
AS
SELECT *
FROM Model
Where Product = @cmbProduct
----

 

[privatenews]

Hello,

You may want to refer to the following article to set inputparameter
property of the forms:

ACC2000: Implementing Query-by-Form in an Access Project
http://support.microsoft.com/?kbid=235359

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

Thread-Topic: dynamically changing selection options for a combo using row s
thread-index: AcZIZp35CVPyYkHZSpC0E6iCTW9efQ==
X-WBNR-Posting-Host: 82.33.57.189
From: =?Utf-8?B?c21hcnR5?= <[email protected]>
References: <[email protected]>

Subject: Re: dynamically changing selection options for a combo using row s
Date: Wed, 15 Mar 2006 11:28:20 -0800
Lines: 41
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Newsgroups: microsoft.public.access.adp.sqlserver
Path: TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.access.adp.sqlserver:24050
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
X-Tomcat-NG: microsoft.public.access.adp.sqlserver

Thanks, that did work.

I also have a few subforms displayed as a data sheet that I use in a similar
way to the combo boxes but they also allow editing of the records. is there
any way for one of these subforms to reference text boxs on one of the other
subforms to determine what records are retrieved?

To clarrify each subform will act in a similar way to the product, model
relationship where selecting a product will then determine which models are
displayed but will also allow a user to add models or products.

ops, I forgot the parameter's datatype

----
CREATE Procedure cmbModel
@cmbProduct varchar(20)
AS
SELECT *
FROM Model
Where Product = @cmbProduct
----

--
Giorgio Rancati
[Office Access MVP]

----
CREATE Procedure cmbModel
@cmbProduct
AS
SELECT *
FROM Model
Where Product = @cmbProduct
----

 

[giorgio rancati]

Hi smarty,

A) assumed you have
1 mainForm
2 subForm, the first named subProducts and the second named subModel
The both subforms RecordSource are a view o a select....

Put a unbound control named txtProduct in mainForm

write in the subModel LinkMasterFields txtProduct
write in the subModel LinkChildFields Product.

write this in subProducts Current event
----
Private Sub Form_Current()

Me.Parent!txtProduct = Me!Product

End Sub
----
there is another way in NorthWindCS Project, see the "Customer Orders" form


B) assumed you have
1 mainForm
2 subForm, the first named subProducts and the second named subModel
The subModel RecordSource is a stored procedure
----
create procedure usp_model
@txtProduct varchar(20)
AS
Select *
From Model
Where Product = @txtProduct
----
Put a unbound control named txtProduct in mainForm (with the same stored
parameter name)
(with a stored procedure the LinkMasterFields and LinkChildFields are
ignored)

write this in subProducts Current event
----
Private Sub Form_Current()

Me.Parent!txtProduct = Me!Product

End Sub
----

write this in subModels BeforeUpdate event
----
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord = True Then
Me!Product = Me.Parent!txtProduct
End If

End Sub