Selecting only one active record from multiple records

[M.L. Abram]

Hello all,

I do not know if this question is regarding table design, queries, or
programming. Below, I have given a table design using Access 2003.
Fields 'Product' and 'Color' are primary keys and 'Active'
is a Boolean data type. What I am trying to accomplish is having only
one record selected active out of the 'Product' field for each
type. If possible, have the user be able to do this task while viewing
the data within a continuous form.
Product Color Active
-----------------------------------------------
Shirt Blue
Shirt Yellow
Shirt Green True
Shirt Red
Shirt Black
Shirt Pink
Shirt White
Pants Blue
Pants Black True
Pants White
....

Thoughts or ideas?

TIA

M.L. Abram

[salad]

M.L. Abram wrote:

Hello all,

I do not know if this question is regarding table design, queries, or
programming. Below, I have given a table design using Access 2003.
Fields 'Product' and 'Color' are primary keys and 'Active'
is a Boolean data type. What I am trying to accomplish is having only
one record selected active out of the 'Product' field for each
type. If possible, have the user be able to do this task while viewing
the data within a continuous form.
Product Color Active
-----------------------------------------------
Shirt Blue
Shirt Yellow
Shirt Green True
Shirt Red
Shirt Black
Shirt Pink
Shirt White
Pants Blue
Pants Black True
Pants White
...

Thoughts or ideas?

TIA

M.L. Abram

Well, you could create a form with your three fields. Save it to
something, perhaps Subform

Then I'd create an unbound form. Call it MainForm.

I would drop in the form Subform into the form MainForm.

At the top would be three combos. Product, Color, Active. Product
combo would select distinct product from the table and union with an All
record. Color would select distinct colors for a product and union with
all colors for the product selected in the combo for Products. Then I'd
set another combo to -1;Yes/0;No/1;All for active. In the Afterupdate
event for each combo I'd call a routine to filter records.
The routine might look something like this aircode
sub setfilter
Dim strF As String
If Me.ComboProduct <> "ALL" then
strF = "ProductID = " & Me.ComboProduct & " And "
Endif
If Me.ComboColor <> "ALL" then
strF = strf & "ColorID = " & Me.ComboColor & " And "
Endif
If Me.ComboActive <> 1 then
strF = strf & "Active = " & Me.ComboActive & " And "
Endif
If strF > "" Then strF = Left(strF,len(strF)-5) 'remove And
Forms!MainForm!Subform.form.filter = strF
Forms!MainForm!Subform.form.filteron = true
end sub

This really doesn't answer your question but with some tweaking it will
get you started.

[Bob Quintal]

"M.L. Abram" <mi***********@gmail.com> wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:

Hello all,

I do not know if this question is regarding table design,
queries, or programming. Below, I have given a table design
using Access 2003. Fields 'Product' and 'Color' are primary
keys and 'Active' is a Boolean data type. What I am trying to
accomplish is having only one record selected active out of
the 'Product' field for each type. If possible, have the user
be able to do this task while viewing the data within a
continuous form.
Product Color Active
-----------------------------------------------
Shirt Blue
Shirt Yellow
Shirt Green True
Shirt Red
Shirt Black
Shirt Pink
Shirt White
Pants Blue
Pants Black True
Pants White
...

Thoughts or ideas?

TIA

M.L. Abram

To rephrase your question, "How do I reset Active on the other
colors of the same product when I set active on one color of a
product (if that's not what you are asking, please resubmit the
question so we can guess again).

In the active.AfterUpdate event, run a query that sets active =
false WHERE product = 'shirt' and color <> 'green' Create the
query to use the form control values instead of the literal
values..

--
Bob Quintal

PA is y I've altered my email address.

[M.L. Abram]

Thank you for rephrasing the question. This is exactly what I am
attempting to do. I was thinking that I would need an ADO connection
to the table to change the values. I will work with a query then.

Cheers!

M.L. Abram