Autogenerate Numbers with Text

[Yam84]

I am curious to know if I can create variables in access, similar to
the way the autonumber is generated. I have 3 types of hardware items,
Panel, Loose and Field. I have values P1-P76, L1-L76, and F1-F76 that
belong to each HW type. when a user goes to order Panel HW, I would
like for the drop down to populate P1-P76. As opposed to having these
228 values in my table, I wanted to know if there was some way to have
them autogenerated for the user.

In excel, I can say for instance:
A1=P1
B1=A1+1 (would generate P2)
C1=B1+1, (would generate P3 etc...)

Is there a way to accomplish this in access?

 

[Jeff Boyce]

Instead of trying to jam those multiple "facts" into a single field (a poor
design), why not use separate fields for each data element, then use a query
to concatenate them into the "number" you wish to see in forms, reports,
etc.?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pete D.]

I sounds like your trying to use Access as a multi page spreadsheet. You
might read up on relational database before starting. Here is some reading
material.

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
http://www.allenbrowne.com/casu-06.html

 

[Ken Sheridan]

It could be done the way you propose, but it most definitely should not be.
The values are data. In a relational database all data should, without
exception, be stored as values at column positions in rows in tables. Its
called the 'information principle', viz:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date - Introduction to Database Systems; 7th Edition; 2000

228 rows in a table is after all chicken-feed, and its hardly an onerous
task to enter that many rows. Inserting the rows into the table could be
automated with some simple VBA code, but for the number of rows involved its
frankly not worth the effort.

A correct model here would be a table HardWareTypes with three rows
containing P,L and F in the primary key column, HardWareTypeCode say. Other
columns would represent other attributes of the hardware type, e.g.
HardwareType with values Panel, Loose and Field.

A second table, HardwareItems would have a foreign key column
HardWareTypeCode and a column ItemNumber. These would together be the
composite primary key of the table. Other non-key columns, if any, would
represent other attributes of the items.

In the relationship between HardWareTypes and HardWareItems referential
integrity and cascade updates should be enforced.

For a combo box of panels the RowSource would be:

SELECT HardWareTypes & ItemNumber FROM HardwareItems ORDER BY ItemNumber;

Note that while you do not need to include the HardWareTypes table in the
RowSource properties for the three combo boxes it is nevertheless essential
to the model as it controls the integrity of the data by the enforcement of
referential integrity.

Ken Sheridan
Stafford, England