Create ID Value from Record Data

[Todd H]

Here is what I am trying to do...I am entering products based on Area,
Location, Item, and Style...I have set the Primary Key field as TAG
and want to take a concatenation of AREA, LOCATION, ITEM and STYLE and
create the TAG value.

I can't seem to phrase my search right to get the information.

Thanks,

Todd

 

[Douglas J. Steele]

Why? As in "why do you want to duplicate data in the table?"

A primary key is simply an special type of index, and an index can have up
to 10 separate fields in it. Make your primary key the combination of the
four existing fields, rather than doing the concatenation.

 

[Todd H]

I am trying to create a unique ID to each item...This is only
duplicated in the Item Entry Form...it will be the main identifier
after entry. For example, I have a Desk Chair, type A which will go
in the Office of the Clubhouse...I want to create an ID along the
lines of CBOFC-DeskChair-A...

 

[Todd H]

Your solution sounds great, how do I do that in my form?

 

[Douglas J. Steele]

You don't do it in your form: you do it in your table. Access 2003 and
earlier, you do this by opening the table in Design view, highlight the
fields in question by clicking in the gray to the left of the field name
while holding down the Ctrl key, then clicking on the Key icon (or selecting
Primary Key from the Edit menu). I don't have Access 2007 installed on this
machine, and I forget the precise sequence there.

Note that you can always create a query that concatenates the four fields
into one for display purposes and use that query anywhere you would
otherwise have used the table. You do this by typing something like the
following into an empty column on the Fields row of the grid:

Tag: [AREA] & "-" & [LOCATION] & + "-" & [ITEM] & "-" & [STYLE]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Your solution sounds great, how do I do that in my form?

 

[Todd H]

You don't do it in your form: you do it in your table. Access 2003 and
earlier, you do this by opening the table in Design view, highlight the
fields in question by clicking in the gray to the left of the field name
while holding down the Ctrl key, then clicking on the Key icon (or selecting
Primary Key from the Edit menu). I don't have Access 2007 installed on this
machine, and I forget the precise sequence there.

Note that you can always create a query that concatenates the four fields
into one for display purposes and use that query anywhere you would
otherwise have used the table. You do this by typing something like the
following into an empty column on the Fields row of the grid:

Tag: [AREA] & "-" & [LOCATION] & + "-" & [ITEM] & "-" & [STYLE]

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


Your solution sounds great, how do I do that in my form?

I am trying to create a unique ID to each item...This is only
duplicated in the Item Entry Form...it will be the main identifier
after entry. For example, I have a Desk Chair, type A which will go
in the Office of the Clubhouse...I want to create an ID along the
lines of CBOFC-DeskChair-A...

Thanks! I ended up changing the table to have a hidden primary key,
and then used your idea about using a query to create the ID.