MS Access project with SQL Server - dynamically changing selection options for a combo using row sourc

Author

dynamically changing selection options for a combo using row sourc

smarty

2006-03-13, 8:25 pm

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]

2006-03-14, 3:24 am

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: AcZG8yyrK1fzCUMSQRui
rAekvjeObw==
>X-WBNR-Posting-Host: 82.33.57.189
>From: =?Utf-8?B?c21hcnR5?= <smarty@nospam.nospam>
>Subject: dynamically changing selection options for a combo using row sourc
>Date: Mon, 13 Mar 2006 15:09:26 -0800
>Lines: 25
>Message-ID: <A9833D1B-30D3-4CC4-AD6D- B380106A523A@microso
ft.com>
>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

2006-03-14, 3:24 am

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_AfterUpda
te()

Me.cmbModel.Requery

End Sub
----

bye
--
Giorgio Rancati
[Office Access MVP]

"smarty" <smarty@nospam.nospam> ha scritto nel messaggio
news:A9833D1B-30D3-4CC4-AD6D- B380106A523A@microso
ft.com...
> 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

2006-03-14, 3:24 am

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]
"giorgio rancati" < giorgio_No_Spalmer_r
ancati@tiscali.it> ha scritto nel
messaggio news:%23VpAPH0RGHA.4688@TK2MSFTNGP11.phx.gbl...
> ----
> CREATE Procedure cmbModel
> @cmbProduct
> AS
> SELECT *
> FROM Model
> Where Product = @cmbProduct
> ----

Sponsored Links