microsoft access 2016 - autonumber value with text prefix- relationship problems

ok but for instance if I need a customerId and id want it CI1,CI2 instead of just 1,2 in the primary key field and be able to build a relationship to order, invoice tables etc. there's no way to do this?

thanks

You aren't understanding Tom's response. An autonumber has ONE purpose and ONE purpose only; to uniquely identify a record. It will not be sequential and cannot include any other characters.

I really see no value in displaying a customer ID as CI1, CI2, etc. Nor do I see the need for sequentially numbering a Customer ID. But that is easily done by using the Expression:  "CI" & [CustomerID], whenever you want to display the identifier.

On the other hand, I do see a value in sequentially numbering invoices. But using an autonumber for the Invoice Number would not be appropriate. Also relationships are behind the scenes, the user never sees them. So there is no need to jump through hoops to create relationships on a concatenated value. If you want use a Sequential Number for the Invoice Number see my blog on Sequential Numbering. But you should use an Autonumber as your key field for relationship purposes.

I'm trying to achieve a autonumber with a text prefx I can build relationships between tables with eg INV00001 for invoice 00001 etc

You don't need to store the INV prefix or the leading zeros in the column.  If you use an autonumber, then instead of showing the bound control in a form or report use an unbound control with a ControlSource property of:

="INV" & Format(InvoiceID,"00000")

However, as Tom points out, an autonumber does not guarantee sequential values, and you can't completely rule out the possibility of it arbitrarily inserting a value which exceeds the five digits.  To avoid that, and provide sequential numbers,  you'd use a straightforward long integer number data type as the primary key InvoiceID, and compute its value when a row is inserted via a form.  You'll find an example as CustomNumber.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the 'Sequential Numbering' option illustrates this, and includes error handling to cater for conflicts where two or more users in multi-user environment are inserting a new row simultaneously.  It also includes the facility to seed the next number to be used, though that is probably not relevant in your case.  Note that the primary key, ProductID in my demo, does need to have a control in the form bound to it, but this could be hidden by setting its Visible property to False (No) and the formatted value shown in an unbound control

Whether you use an autonumber or compute the InvoiceID primary key, a foreign key in any referencing table would be a straightforward long integer number data type.  As with the primary key this could be hidden and the formatted value displayed as described above.

ok but for instance if I need a customerId and id want it CI1,CI2 instead of just 1,2 in the primary key field and be able to build a relationship to order, invoice tables etc. there's no way to do this?

thanks

No, and you are abusing the autonumber. It is always numeric, and the only thing you can say about it is that it's unique, NOT that it is sequential without gaps. It is mostly used to build relationships between tables behind the scenes. If you need an invoice number with a certain pattern, you have to create an additional field and populate it, perhaps using the DMax+1 technique. Should be easy to find references online, because this is a FAQ.

When creating such invoice number, be sure not to violate other database design rules such as that a value cannot be dependent on another value. For example Cust123-Year2016-001 is not a good invoice number.