If This, Then That

[Guest]

I have a form that pulls data into a subform. Two of the fields that are
pulled into the subform are RFQType (choices are only R or C), and Product
Number (generally 1, 2, or 3) .

I need to create some code that will look at these two fields on the subform
and then search data from a table(s) and input the found data into the
subform (or another subform if necessary). If I was in Excel, I'd have a
formula (if statement) for each cell I needed to fill up. Is this the same
for Access?

Additionally, there are several categories, and I'm thinking about making
several tables, one for each category. i.e.:

Table Name 1 (Category): TM
Field 1: Product Number
Field 2: Percent
Field 3: Description
Field 4: RFQType

Table Name 2 (Category): PMO
Field 1: Product Number
Field 2: Percent
Field 3: Description
Field 4: RFQType

I'm thinking this is some sort of loop, but I'm a beginner and just
beginning to understand and work with VBA. If I was in Excel, I'd create an
if statement. If this is possible using an IIf statement, I'd be very happy.
Otherwise, any help on the coding would be greatly appreciated.

 

[Marshall Barton]

I have a form that pulls data into a subform. Two of the fields that are
pulled into the subform are RFQType (choices are only R or C), and Product
Number (generally 1, 2, or 3) .

I need to create some code that will look at these two fields on the subform
and then search data from a table(s) and input the found data into the
subform (or another subform if necessary). If I was in Excel, I'd have a
formula (if statement) for each cell I needed to fill up. Is this the same
for Access?

Additionally, there are several categories, and I'm thinking about making
several tables, one for each category. i.e.:

Table Name 1 (Category): TM
Field 1: Product Number
Field 2: Percent
Field 3: Description
Field 4: RFQType

Table Name 2 (Category): PMO
Field 1: Product Number
Field 2: Percent
Field 3: Description
Field 4: RFQType

I'm thinking this is some sort of loop, but I'm a beginner and just
beginning to understand and work with VBA. If I was in Excel, I'd create an
if statement. If this is possible using an IIf statement, I'd be very happy.
Otherwise, any help on the coding would be greatly appreciated.

No, no, don't create a table for each category. Access is a
database sytem, not a fancy spreadsheet. The first rule of
relational databases (not just Access/Jet) is to design the
tables by following the rules of Normalization, at least the
first three Normal Forms. This is essential to your
designing a workable application, so do some homework to
understand how to decide when a table is or is not
normalized. Start here:
http://support.microsoft.com/kb/283878/en-us

You should quickly understand why the tables should be more
like:

Table Categories
CatID AutoNumber (Primary key)
CatName Text
. . .

Tables Products
ProductNumber ??? (Primary key)
Category Long (Foreign Key)
Percent ???
Descr Text
RFQType ???

A simple query can but together to get the same kind of
thing as you were thinking.

As you may by now surmise, when it comes to manipulating
data, code is a last resort and queries rule.

I need more information about what your forms are doing, but
with properly normalized tables the subform should be able
to display the desired data by using a relatively
straightforward record source query and the appropriate Link
Master/Child Fields properties.

 

[Guest]

Thanks for getting me thinking straight Marshall,

I'm gonna work on creating the tables and then I'll get back in here with
additional questions, if you don't mind.

 

[Marshall Barton]

Post a question any time. If its not a direct followup to
this question, start a new thread.

You will probably be better served if you ask table design
questions in the table design newsgroup.