Dynamic # of fields

[PsyberFox]

Hi there,

I hope that I'm explaining this problem properly, but if not, please ask
more questions and I'll try and explain the problem I'm having to the best of
my abilities.
I am trying to do the following on MSAccess 2007:
I have a table where u user has to enter information against a maximum of
185 products. All products however does not always have information that need
to be entered, so I was thinking about creating the input form with a place
where the user can select the product from a drop-down box. When information
for a product is entered, I want another field to become available
automatically beneath that field, up until such time that no information is
to be captured anymore. Is this possible. Obviously, for each record,
different products will have information that other records.

Hope I'm making sense in my question and appreciate any help!

Ciao
W

 

[Jeff Boyce]

I'm not quite visualizing your data/structure yet. It sounds like you are
working on the form first, rather than the data. In Access, it all starts
with the data.

If you'll post back a bit more specific description of your data, folks here
may be able to offer more specific suggestions.

More info, please...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Tom van Stiphout]

On Sat, 20 Mar 2010 03:53:01 -0700, PsyberFox

I could follow along until you write: "Obviously, for each record,
different products will have information that other records."
That's not an English sentence, and I don't understand what you are
trying to say.

Also a summary of your data structure will help.

-Tom.
Microsoft Access MVP

 

[Steve]

Why not create your form with all the fields of the underlying table. Then
put code in the Current event of the form to either make the fields you
don't need for the current product not visible or disable the fields you
don't need for the current product.

Steve
(e-mail address removed)

 

[PsyberFox]

Hi Jeff / Tim,

My apologies for the lack of English grammar... kinda got lost on me there
too, lol.

Anyways, let me explain in more detail:
1. I have information that need to be captured by store (about 21 stores).
2. Each store has roughly 250 products connected to that store.
3. I want to capture survey information by product by store, but at the time
of the survey not all stores had all the products. I want to set up the
database, and thereafter the form, in such a way that when the person
captures information for a particular store, he/she selects a product from a
drop-down box and enters the information for that product. If information is
therefore captured, another drop-down box below that should be available for
the next product, etc. etc. until such time as all the products for that
store has been entered (i.e. the last drop-down either be left blank, or a
product called "End" is selected).
4. The reason why I asked this question in such a way is that I'm not even
100% sure of how I want to structure the tables yet. The complexities around
this is as follows:
4.a. There are about 18 product categories, each of which have around 200
products, and each store has a retail division and a wholesale division - you
see how big this thing is going to get?
4.b. Each store falls within a particular region as well as area so I have
already split the stores by those distinctions.

Hope this sheds more light on the complexities I am faced with.

Regards,
W

 

[Tom van Stiphout]

On Sat, 20 Mar 2010 08:47:01 -0700, PsyberFox

I go by Tom
I would recommend downloading the Northwind sample application and
look at the Order form. You will see the Order Details subform which
allows the user to enter an arbitrary number of rows, each time
selecting from the Products dropdown. This is the classic way of
implementing a many-to-many (between Orders and Products in this case)
relationship in Access.

Indeed the db design is the most important part of your project, and
it is where beginners struggle the most. One possible solution is to
check out this book
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470525746.html
written by some fellow MVPs. Another one is to hire professional help
for this part of the project. Once the database design is in place,
you can likely build the forms and reports on top of it.

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

Anyways, let me explain in more detail:
1. I have information that need to be captured by store (about 21 stores).
2. Each store has roughly 250 products connected to that store.
3. I want to capture survey information by product by store, but at the time
of the survey not all stores had all the products. I want to set up the
database, and thereafter the form, in such a way that when the person
captures information for a particular store, he/she selects a product from a
drop-down box and enters the information for that product. If information is
therefore captured, another drop-down box below that should be available for
the next product, etc. etc. until such time as all the products for that
store has been entered (i.e. the last drop-down either be left blank, or a
product called "End" is selected).
4. The reason why I asked this question in such a way is that I'm not even
100% sure of how I want to structure the tables yet. The complexities around
this is as follows:
4.a. There are about 18 product categories, each of which have around 200
products, and each store has a retail division and a wholesale division - you
see how big this thing is going to get?
4.b. Each store falls within a particular region as well as area so I have
already split the stores by those distinctions.

Avoid the temptation to build wide tables: "Fields are expensive, records are
cheap". You should certainly NOT have (shudder) one field for each product!
You should instead have tables in relationships:

- a 21-row table of Stores with a primary key StoreID (and a Region field to
indicate where the store is, though this plays no role in stocking products in
the stores)
- a table of Products, one row per product, all the products you deal with,
with ProductID as its primary key
- a table of StoreStock with a field for the StoreID (which store you're
surveying), ProductID (what's carried in that store), and fields for any
information about *this* product at *this* store

A form based on Stores with a Subform based on StoreStock would let you pick
any number of products for that store, each going into a new row in the
StoreStock table.