Search Results shown in Subform

[Gary]

Search Fields to filter results

I have a fairly simple form/db created to store information for programming
a fireworks show. The DB is created, and the form to enter the data is
created.

Now I want to develop a “Show Builder†form to do what the database was
created to do. (plan and program shows based on the stock of shells that
have been purchased)

For each record, there are three color fields (Color1, Color2, Color3)
Because for each shell there are usually up to three colors, 2 effects, and 2
different sounds. I need to have a form with a combo box titled color when
dropped to “blue†lists the records in a sub-form below of all records with
blue as Color1, Color2, or Color 3) Same thing with effects, and sounds. I
have tables created for all colors, effects, and sounds and the data-entry
aspect is a combo box on the main form, so the answers are all standardized.
I want to be able to assign a cue number from the sub-form, and set the
record as used, so it wont show up for any other searches.

Is this possible without some heavy duty coding?

 

[Jeff Boyce]

Gary

?..."three color fields (Color1, Color2, Color3)" ...?!

That's how you'd do it with a spreadsheet, but Access is a relational
database. You're creating a maintenance nightmare for yourself (or your
successor) by using 'repeating fields'. What happens to all your tables,
forms, queries, reports, etc when a fourth color gets added?

Instead, if the relationship is one-to-many, use a table to record that,
rather than repeating fields.

If you'll post a description of your table structure, folks here may be able
to offer more specific suggestions for how you can optimize your use of
Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Gary]

There wont be a successor this is strictly for my own use. And the reason
for the multiple colors, is there is an infinite possibilities as far as
combinations of colors, as so far there are over 20 specific colors in the
table. So seeing as there are and will only be three main colors in any given
shell, there wont be a need to add a fourth column.

 

[BruceM]

Some of my earlier databases were built with the idea of unchanging
circumstances. Unfortunately, some of those circusmstances changed.

If you are confident that your 3,2,2 arrangement will never change you can
take the spreadsheet approach, but even so it is more a hindrance than a
help.

You can build a SQL string to set the Record Source for the subform's source
object, maybe something like:

Dim strSQL as string

strSQL = "SELECT * FROM YourTable " & _
"WHERE [Color1] = 'Blue' Or [Color2] = 'Blue' OR [Color3] =
'Blue'"

Me.SubformControlName.Form.RecordSource = strSQL

If you had posted some information about your table structure it would be
possible to be a little more specific, but this is the general idea.

 

[BruceM]

One more thing I meant to say is that I think Jeff is right. You may be
able to force the existing design to conform somewhat to your expectations.
There really is no advantage to storing data in field names, which is what
you are doing with Color1, etc.

 

[Jeff Boyce]

Gary

By "successor", I also mean YOU, six months from now when you don't remember
exactly how you decided to do that.

And if you have three different fields (only three, ever only three), how do
you know if ONE of the colors was, say, "red"? Won't you have to look in
each of the "three" fields? Absolutely possible to do, but why not take
advantage of the tools Access offers, instead of having to build
work-arounds so Access acts like a spreadsheet?

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP