Creating custom forms with a form...

[Robin S.]

This is more of a structural question (me: newbie) as opposed to specific
coding. Please be patient...

I'm making a database for the small company I work for. It will be to add
and edit products listed on our e-commerce site.

I would like to have a customized form for each group of similar products
("drills, for instance, would be a similar group of products - although
there are further groups within the group of "drills"...). Every product
will share standard fields like "product number" and "price" but there are
many product specifications that are specific to only some products (hence
the "similar groups"). These specs includes "volts," "amps," "horsepower,"
etc.

So, I would like to be able to design these custom forms using an easy to
use form.

I was thinking of using a table listing all available specifications (volts,
amps, horsepower, etc.). I would then choose each specification (taken from
the "available specs" table) in a drop down menu. This would create a
*group* form. To clarify, some product groups would list "volts" and "amps,"
but not "horsepower" whereas others would have "horsepower" but not "volts"
or "amps."

The above example is an oversimplification. The number of available specs
could be in the 150-200 range, the number of product groups could be in the
300-400 range and the number of products could go as high as about 30,000.

Is it possible to define the fields in a form using a table, query or
report?

If someone could list some key words that I could look up in my book, "How
to do everything with Access 2002" or on the internet, that would be
excellent. I don't mind fiddling around with something, but I need a place
to start.

Unfortunately, my boss is not really inclined to hire someone to do this.
Moreover, I'd like to learn how to do it myself.

Thanks for any suggestions.

Regards,

Robin

[Pieter Linden]

Robin,
Sounds like you want a bit of subclassing... you should read this
article and play around with the sample database before going too far.
Should save you a few headaches, I hope.

http://www.mvps.org/access/tables/tbl0013.htm

Your form may get a bit ugly, but I'm not sure how to get around that
part...

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The way I'd do it would be to have a table of Product Attributes that
would be designed something like this:

ProductAttributes
ProductID Long - foreign key to the Products table
Attribute Text - name of the attribute, Watt, Amp, KW, etc.
AttributeValue Text - the actual value of the attribute

Data would look like this:

ProductID Attribute AttributeValue
1 Watt 25
2 Horse Power 350
1 Amps 5

To find an engine that has a horse power rating between 200 and 500
you'd create a query like this:

SELECT ProductName
FROM Products As P INNER JOIN ProductAttributes AS PA
ON P.ProductID = PA.ProductID
WHERE PA.Attribute = "Horse Power"
AND PA.AttributeValue Between "200" And "500"

You have to use quotes around the AttributeValue values 'cuz the
column data type is Text.

HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9KnKIechKqOuFEgEQLhXgCg+4lCpbRncHAzNPy54O4RIE uUnQ4AoKTc
2TtWoJ9Hjpko7M7CVzk2O1NV
=7R9Y
-----END PGP SIGNATURE-----

Robin S. wrote:

This is more of a structural question (me: newbie) as opposed to specific
coding. Please be patient...

I'm making a database for the small company I work for. It will be to add
and edit products listed on our e-commerce site.

I would like to have a customized form for each group of similar products
("drills, for instance, would be a similar group of products - although
there are further groups within the group of "drills"...). Every product
will share standard fields like "product number" and "price" but there are
many product specifications that are specific to only some products (hence
the "similar groups"). These specs includes "volts," "amps," "horsepower,"
etc.

So, I would like to be able to design these custom forms using an easy to
use form.

I was thinking of using a table listing all available specifications (volts,
amps, horsepower, etc.). I would then choose each specification (taken from
the "available specs" table) in a drop down menu. This would create a
*group* form. To clarify, some product groups would list "volts" and "amps,"
but not "horsepower" whereas others would have "horsepower" but not "volts"
or "amps."

The above example is an oversimplification. The number of available specs
could be in the 150-200 range, the number of product groups could be in the
300-400 range and the number of products could go as high as about 30,000.

Is it possible to define the fields in a form using a table, query or
report?

If someone could list some key words that I could look up in my book, "How
to do everything with Access 2002" or on the internet, that would be
excellent. I don't mind fiddling around with something, but I need a place
to start.

Unfortunately, my boss is not really inclined to hire someone to do this.
Moreover, I'd like to learn how to do it myself.

Thanks for any suggestions.

Regards,

Robin

[Robin S.]

Pieter and MGFoster,

I don't think I made myself clear (my question probably got lost in a sea of
extraneousness...)

I need to define the fields of a form, using another form. This is the end
goal. The available fields need to be stored (I'd assume in a table) and be
available for selection.

The real issue is that I don't want to have to go into a form and manually
reconfigure it. Perhaps I could get a macro to copy a previously created
template form, rename it and define which fields were to be used, but that
seems like a lot of work....

Thanks for your answers though. Although they didn't answer my question,
they did provide further insight into the whole design problem.

Thanks again.

Regards,

Robin