Lookup New Item Code

[MissyNae]

Hi,

I'm new to Access and have been trawling the internet/youtube trying to figure out how to do this (if it's even possible).

We have a small scale asset register that keeps track of our assets and their engraving codes (all assets valued over certain $ amount must be engraved with a unique code). What I'm wanting to do is have a form that can be filled out by our volunteers/staff for when a new asset is acquired that will auto populate a new Engraving Code from a specified list (eg. BCA1001 - BCA9999). Obviously I don't want any duplicates to occur. Is there a way to do a lookup of some sort, or a way to have a list of the codes that we will be using stored somewhere that 1. if there is no data stored beside it, will select that code (and enter data next to it), 2. if there is data stored beside it it will go to the next available code?

Another idea I had was I saw an example of a pop out box view of the table inserted into the form so that a running record of data could be viewed as you enter it into the form (but I obviously want this to be protected) so someone could just see what the next code would be and manually enter it in the field.

As I said, I'm new to access (and this forum) so I'm not 100% sure what the programs limitations are. I'm open to any and all suggestions (in newbie language please!)

EXAMPLE:

Jane Smith has recently acquired a new fridge for the office. A new asset needs to be added to the register that will give the new engraving code.

Item Description: Fridge_Drinks


Brand: Quirks
Purchase Date: 29/06/2021
Category: Electrical_Appliances (this is a Lookup field to another table)
Location: Kitchen
Engraving Code: (this is what I'm wanting)

[moke123]

Take a look at the DMax() https://www.techonthenet.com/access/...omain/dmax.php

Just look up the max and add one.

Will the prefix always be "BCA"? You may want to store just the number and then once you get the max value concatenate the "BCA" to the front of it for display.

[Micron]

You have a table of codes with an autonumber pk field? If not you could do that but eventually might run out so the other option is to auto generate but that's slightly more difficult. Either is certainly doable.

If you have the table of 'seed' codes here is one way:
To get the next available code you find the Min of the ID field where descriptor is null and you get the corresponding code value for that record. This would be done in one query along the lines of SELECT Min(TableName.IDField) As MinOfID, Description, Code WHERE Description Is Null; - using your own object names of course.

Your descriptor field should be required (by way of the form you'd use - not by setting the required property in table design). There are other things to consider though, such as is this a multi-user db,
is it properly split where each user has their own front end (fe),
should you assign this code at the last possible split second so that 2 users don't try to create a record with the same code?
Obviously some of these points are of no concern if it is a single user db. Should still be split though.

If you would rather auto generate, then there are additional considerations and much of that won't apply but for me, one example is enough for now.
'Nuff for now. I think you wanted a simplified explanation so I avoided tech talk as much as possible.

[MissyNae]

Thanks I really like this idea! I'm just trying to enter it in, but not sure what I should put for the [criteria]. I've got:

=DMAX(Engraving Code, Table Name, [?])

Any further suggestions would be great.

[MissyNae]

Thanks I really like this idea! I'm just trying to enter it in, but not sure what I should put for the [criteria]. I've got:

=DMAX(Engraving Code, Table Name, [?])

Any further suggestions would be great.

[CJ_London]

if your field names have spaces, enclose with square brackets (spaces in names are a bad idea as you will eventually appreciate), you don't need a criteria if your 'numbers' all start with 'BCA'

=DMAX([Engraving Code], [Table Name])