Auto Labelling

[sshah]

Hi,
I have created a database in access to build an inventory. The structure is very similar to the "Home Contents Inventory" template from microsoft.

I want now to generate auto-labels for all contents in the inventory in the format XXX-XX-999 (XXX=Room, XX=Device, 999=number of devices in room) so for example if I have three PC's in the OfficeSpace room the label should be OFS-PC-003

I have created fixed value columns for Room and Device with the abbreviations stored all I need now is to somehow concatenate these two columns and assign an autoUpdate number so that the Label remains unique throughout (OSF-PC-001, OSF-PC-002, PSF-PC-003, BOR-PC-001 etcetc)

Can anyone help me sort this out?

best regards

 

[llkhoutx]

Me!Room & "-" & me!Devise & "-" & me!Number, if bound to a form
or
[Room] & "-" & [Devise] & "-" & [Number], is columns in a query.

Don't store this value, just calculate it whenever required.

 

[sshah]

Thankyou for your reply llkhoutx

I have tried both options and they throw an exception whenever I compile the vba.

What I am trying to achieve is:
I have two text fields (1) Category (2) Room on the main form
These fields are populated by seperate tables. The category table contains a column with all abbreviations stored. Same is the case for the Room.

To get values in the Category and Room I have set them as combo boxes and select their values from the list which are (either Server, Switch etc or Office Room, Kitchen etc)

Is there a way to have a third field that gets populated automatically when values in the above two fields are selected on the form. So for instance if i select Category = Personal Computer and Room = Kitchen the third field should automatically get values of PC and KTN and return the value KTN-PC-001

I appreciate your time

regards,

 

[llkhoutx]

Use the after update event of each field, checking for both being non Null. Your autonumber field may be visible or hidden on your form, but it has to be there to be used in a formula. The autonumber field gets automatically populated when the first bound field is populated.

 

[sshah]

Use the after update event of each field, checking for both being non Null. Your autonumber field may be visible or hidden on your form, but it has to be there to be used in a formula. The autonumber field gets automatically populated when the first bound field is populated.

Thankyou for your support so far. What if I want to save the lables for each item in the inventory and impose a restrictions so that once a label is issued it can not be modified/deleted etc in any way? also can you suggest as to what would be the best way to achieve the labelling. Do you think it shouldbe implemented in a report rather than the actual form. All I need the labels for is to be printed and stuck to every item.
What is the best way to achieve this in your opinion?

best regards and thanks in advance!