Multi Value Selection issues

[Nurse Nancy]

Hi
I have a mult-value Selection field and i am using it a form to run a query
to find matches from another table.

I know this is bad,, but it's too late,, i wish MS had never done this to
us!!!!!

PLEASE HELP

it works ok except 2 major problems

1. There are over 100 values to select and I want to be able to select All,
or leave the form field blank in order to bypass this filter
But if i leave it blank, i get no matches and if I add Or Is Null in design
view I still get no matches

I tried adding a value of 'ALL' to the selection lits, but i have no idea
how i would code this.



2. If I want to clear the checkboxes of all that are selected, I don't know
how to do this


Here's the SQL for the query

Target Audience ID].Value)

SELECT DISTINCTROW CP.[Campaign ID], CP.[Customer ID], CP.ProductName,
RS.[Station Call Letters], RS.[Market ID], RS.Rank, Rank.Rank, RS.[Format
ID], CP.[Target Audience ID], CP.[Flight Start Date], CP.[Spot Length],
CP.[Employee ID]
FROM (([FORMATS TABLE] AS F INNER JOIN ([RADIO STATIONS TABLE] AS RS INNER
JOIN ([MARKETS TABLE] AS M INNER JOIN ([TARGET AUDIENCE TABLE] AS T INNER
JOIN ([Rank Table] AS Rank INNER JOIN [CAMPAIGN PRODUCT TABLE] AS CP ON
Rank.ID=CP.Rank) ON T.[Target Audience ID]=CP.[Target Audience ID].Value) ON
M.[Market ID]=CP.[Market ID].Value) ON RS.[Market ID]=M.[Market ID]) ON
(F.[Format Abbreviation]=T.Format.Value) AND (F.[Format
Abbreviation]=RS.[Format ID])) INNER JOIN [PRODUCT CATEGORY TABLE] ON
CP.[Product Category ID]=[PRODUCT CATEGORY TABLE].[Product Category ID])
INNER JOIN [PRODUCTS TABLE] ON CP.ProductName=[PRODUCTS TABLE].ProductName
WHERE (((CP.[Campaign ID])=Forms![Maintain Products Form]![Campaign ID]) And
((RS.Rank)<=RANK!Rank))
ORDER BY RS.[Station Call Letters];



Nancy

 

[Kipp Woodard]

If your CheckBoxes are bound to a field in a table, then you might have
buttons on your form for SelectAll and DeselectAll.

Use the button to run SQL that updates the appropriate table.

Update MyTable Set MyYesNoField = True

or

Update MyTable Set MyYesNoField = False

There is a RunSQL macro action, or you can do it in VBA.

You will probably have to requery your form with the Requery action (or VBA
method).

 

[Nurse Nancy]

hi, I am very eager to try this, but have no idea where to start,, since this
is a multivalue select field there are a hundred check boxes when i select
the drop down, but only one field name

the form is called - Maintain Products Form
The Combo Box is called - MarketIDCB
The field name is - Market ID the values come from the Market Table
After selecting them from the Multiselect Drop down, they are bound to the
Campaign Product Table


The Row Source in the Form for the field is:
SELECT [MARKETS TABLE].[Market ID], [MARKETS TABLE].Market, [MARKETS
TABLE].[State ID], [MARKET REGION TABLE].[Market Region] FROM [MARKETS TABLE]
INNER JOIN [MARKET REGION TABLE] ON [MARKETS TABLE].[Market Region ID] =
[MARKET REGION TABLE].[Market Region ID] ORDER BY [MARKETS TABLE].Market;

so what is 'mytable'
and what is 'myyesnofield'
Also i don't see the RunSQL in the macro actions,, i see RUNCode for VB
which i dont' know...

thanks
Nurse Nancy

If your CheckBoxes are bound to a field in a table, then you might have
buttons on your form for SelectAll and DeselectAll.

Use the button to run SQL that updates the appropriate table.

Update MyTable Set MyYesNoField = True

or

Update MyTable Set MyYesNoField = False

There is a RunSQL macro action, or you can do it in VBA.

You will probably have to requery your form with the Requery action (or VBA
method).

Hi
I have a mult-value Selection field and i am using it a form to run a query
to find matches from another table.

I know this is bad,, but it's too late,, i wish MS had never done this to
us!!!!!

PLEASE HELP

it works ok except 2 major problems

1. There are over 100 values to select and I want to be able to select All,
or leave the form field blank in order to bypass this filter
But if i leave it blank, i get no matches and if I add Or Is Null in design
view I still get no matches

I tried adding a value of 'ALL' to the selection lits, but i have no idea
how i would code this.



2. If I want to clear the checkboxes of all that are selected, I don't know
how to do this


Here's the SQL for the query

Target Audience ID].Value)

SELECT DISTINCTROW CP.[Campaign ID], CP.[Customer ID], CP.ProductName,
RS.[Station Call Letters], RS.[Market ID], RS.Rank, Rank.Rank, RS.[Format
ID], CP.[Target Audience ID], CP.[Flight Start Date], CP.[Spot Length],
CP.[Employee ID]
FROM (([FORMATS TABLE] AS F INNER JOIN ([RADIO STATIONS TABLE] AS RS INNER
JOIN ([MARKETS TABLE] AS M INNER JOIN ([TARGET AUDIENCE TABLE] AS T INNER
JOIN ([Rank Table] AS Rank INNER JOIN [CAMPAIGN PRODUCT TABLE] AS CP ON
Rank.ID=CP.Rank) ON T.[Target Audience ID]=CP.[Target Audience ID].Value) ON
M.[Market ID]=CP.[Market ID].Value) ON RS.[Market ID]=M.[Market ID]) ON
(F.[Format Abbreviation]=T.Format.Value) AND (F.[Format
Abbreviation]=RS.[Format ID])) INNER JOIN [PRODUCT CATEGORY TABLE] ON
CP.[Product Category ID]=[PRODUCT CATEGORY TABLE].[Product Category ID])
INNER JOIN [PRODUCTS TABLE] ON CP.ProductName=[PRODUCTS TABLE].ProductName
WHERE (((CP.[Campaign ID])=Forms![Maintain Products Form]![Campaign ID]) And
((RS.Rank)<=RANK!Rank))
ORDER BY RS.[Station Call Letters];



Nancy

 

[Nurse Nancy]

can anyone help with this, i have been able to use code I found on this
discussion group for real checkbox fields but not for the checkboxes in the
dropdown of my multiselect field
--
Nancy

hi, I am very eager to try this, but have no idea where to start,, since this
is a multivalue select field there are a hundred check boxes when i select
the drop down, but only one field name

the form is called - Maintain Products Form
The Combo Box is called - MarketIDCB
The field name is - Market ID the values come from the Market Table
After selecting them from the Multiselect Drop down, they are bound to the
Campaign Product Table


The Row Source in the Form for the field is:
SELECT [MARKETS TABLE].[Market ID], [MARKETS TABLE].Market, [MARKETS
TABLE].[State ID], [MARKET REGION TABLE].[Market Region] FROM [MARKETS TABLE]
INNER JOIN [MARKET REGION TABLE] ON [MARKETS TABLE].[Market Region ID] =
[MARKET REGION TABLE].[Market Region ID] ORDER BY [MARKETS TABLE].Market;

so what is 'mytable'
and what is 'myyesnofield'
Also i don't see the RunSQL in the macro actions,, i see RUNCode for VB
which i dont' know...

thanks
Nurse Nancy

If your CheckBoxes are bound to a field in a table, then you might have
buttons on your form for SelectAll and DeselectAll.

Use the button to run SQL that updates the appropriate table.

Update MyTable Set MyYesNoField = True

or

Update MyTable Set MyYesNoField = False

There is a RunSQL macro action, or you can do it in VBA.

You will probably have to requery your form with the Requery action (or VBA
method).

Hi
I have a mult-value Selection field and i am using it a form to run a query
to find matches from another table.

I know this is bad,, but it's too late,, i wish MS had never done this to
us!!!!!

PLEASE HELP

it works ok except 2 major problems

1. There are over 100 values to select and I want to be able to select All,
or leave the form field blank in order to bypass this filter
But if i leave it blank, i get no matches and if I add Or Is Null in design
view I still get no matches

I tried adding a value of 'ALL' to the selection lits, but i have no idea
how i would code this.



2. If I want to clear the checkboxes of all that are selected, I don't know
how to do this


Here's the SQL for the query

Target Audience ID].Value)

SELECT DISTINCTROW CP.[Campaign ID], CP.[Customer ID], CP.ProductName,
RS.[Station Call Letters], RS.[Market ID], RS.Rank, Rank.Rank, RS.[Format
ID], CP.[Target Audience ID], CP.[Flight Start Date], CP.[Spot Length],
CP.[Employee ID]
FROM (([FORMATS TABLE] AS F INNER JOIN ([RADIO STATIONS TABLE] AS RS INNER
JOIN ([MARKETS TABLE] AS M INNER JOIN ([TARGET AUDIENCE TABLE] AS T INNER
JOIN ([Rank Table] AS Rank INNER JOIN [CAMPAIGN PRODUCT TABLE] AS CP ON
Rank.ID=CP.Rank) ON T.[Target Audience ID]=CP.[Target Audience ID].Value) ON
M.[Market ID]=CP.[Market ID].Value) ON RS.[Market ID]=M.[Market ID]) ON
(F.[Format Abbreviation]=T.Format.Value) AND (F.[Format
Abbreviation]=RS.[Format ID])) INNER JOIN [PRODUCT CATEGORY TABLE] ON
CP.[Product Category ID]=[PRODUCT CATEGORY TABLE].[Product Category ID])
INNER JOIN [PRODUCTS TABLE] ON CP.ProductName=[PRODUCTS TABLE].ProductName
WHERE (((CP.[Campaign ID])=Forms![Maintain Products Form]![Campaign ID]) And
((RS.Rank)<=RANK!Rank))
ORDER BY RS.[Station Call Letters];



Nancy

 

[roccogrand]

Hi Nancy,

Did you ever figure this one out?

I commonyly use checkboxes in my apps and I need to provide a way to clear
them. I start with a simple Select Query to ID the Yes/No fields. Then I
convert the Select Qusery into an Update Query and place False in the Yes/No
field. If False doesn't work in your app try using the numeric vaue for
False.

As for using Multivalue fields, I love them. They are a little tricky at
first but once you understand them they can save you lots of time and effort.
I am now waiting for A2010 to see what new innovations the Development Team
has come up with in the past four years. But I am not a purist.

HTH

David

can anyone help with this, i have been able to use code I found on this
discussion group for real checkbox fields but not for the checkboxes in the
dropdown of my multiselect field
--
Nancy

hi, I am very eager to try this, but have no idea where to start,, since this
is a multivalue select field there are a hundred check boxes when i select
the drop down, but only one field name

the form is called - Maintain Products Form
The Combo Box is called - MarketIDCB
The field name is - Market ID the values come from the Market Table
After selecting them from the Multiselect Drop down, they are bound to the
Campaign Product Table


The Row Source in the Form for the field is:
SELECT [MARKETS TABLE].[Market ID], [MARKETS TABLE].Market, [MARKETS
TABLE].[State ID], [MARKET REGION TABLE].[Market Region] FROM [MARKETS TABLE]
INNER JOIN [MARKET REGION TABLE] ON [MARKETS TABLE].[Market Region ID] =
[MARKET REGION TABLE].[Market Region ID] ORDER BY [MARKETS TABLE].Market;

so what is 'mytable'
and what is 'myyesnofield'
Also i don't see the RunSQL in the macro actions,, i see RUNCode for VB
which i dont' know...

thanks
Nurse Nancy

If your CheckBoxes are bound to a field in a table, then you might have
buttons on your form for SelectAll and DeselectAll.

Use the button to run SQL that updates the appropriate table.

Update MyTable Set MyYesNoField = True

or

Update MyTable Set MyYesNoField = False

There is a RunSQL macro action, or you can do it in VBA.

You will probably have to requery your form with the Requery action (or VBA
method).

:

Hi
I have a mult-value Selection field and i am using it a form to run a query
to find matches from another table.

I know this is bad,, but it's too late,, i wish MS had never done this to
us!!!!!

PLEASE HELP

it works ok except 2 major problems

1. There are over 100 values to select and I want to be able to select All,
or leave the form field blank in order to bypass this filter
But if i leave it blank, i get no matches and if I add Or Is Null in design
view I still get no matches

I tried adding a value of 'ALL' to the selection lits, but i have no idea
how i would code this.



2. If I want to clear the checkboxes of all that are selected, I don't know
how to do this


Here's the SQL for the query

Target Audience ID].Value)

SELECT DISTINCTROW CP.[Campaign ID], CP.[Customer ID], CP.ProductName,
RS.[Station Call Letters], RS.[Market ID], RS.Rank, Rank.Rank, RS.[Format
ID], CP.[Target Audience ID], CP.[Flight Start Date], CP.[Spot Length],
CP.[Employee ID]
FROM (([FORMATS TABLE] AS F INNER JOIN ([RADIO STATIONS TABLE] AS RS INNER
JOIN ([MARKETS TABLE] AS M INNER JOIN ([TARGET AUDIENCE TABLE] AS T INNER
JOIN ([Rank Table] AS Rank INNER JOIN [CAMPAIGN PRODUCT TABLE] AS CP ON
Rank.ID=CP.Rank) ON T.[Target Audience ID]=CP.[Target Audience ID].Value) ON
M.[Market ID]=CP.[Market ID].Value) ON RS.[Market ID]=M.[Market ID]) ON
(F.[Format Abbreviation]=T.Format.Value) AND (F.[Format
Abbreviation]=RS.[Format ID])) INNER JOIN [PRODUCT CATEGORY TABLE] ON
CP.[Product Category ID]=[PRODUCT CATEGORY TABLE].[Product Category ID])
INNER JOIN [PRODUCTS TABLE] ON CP.ProductName=[PRODUCTS TABLE].ProductName
WHERE (((CP.[Campaign ID])=Forms![Maintain Products Form]![Campaign ID]) And
((RS.Rank)<=RANK!Rank))
ORDER BY RS.[Station Call Letters];



Nancy