Unique ID field based on other fields entered in a record

[JAccess1706]

Hi,

Firstly, thanks for taking the time. I am extremely new to Access, so hopefully this makes some sense.

I have a table with some address details and I want to generate a Unique ID (either in the ID field or a new field) which relates back to the initials entered into the "State" field.

This is what I currently have:

This is the sort of thing I want to do:

I have no idea how to make the "ID" column automatically assign the state as a prefix and assign the next avaliable nubmer. Is this even possible?

Thanks

 

[welshgasman]

I would have a tblSates that holds all the States.
Then I would use the ID for the respective state in wherever the state is required.
So an address table would have a foreign key (FK) of the ID from the states table for whatever state it was in.

 

[JAccess1706]

I would have a tblSates that holds all the States.
Then I would use the ID for the respective state in wherever the state is required.
So an address table would have a foreign key (FK) of the ID from the states table for whatever state it was in.

Thanks for the reply. I just need a little bit extra clarification.

Point 1 - got it. New table created

A bit lost after this. FK are new to me.

Thanks

 

[XPS35]

It can be done by writing some vba code. You put the code in the before insert event on the form where you enter the data.
But........... why do you need such an ID? What is wrong with what you have?

 

[JAccess1706]

It can be done by writing some vba code. You put the code in the before insert event on the form where you enter the data.
But........... why do you need such an ID? What is wrong with what you have?

Could you show/teach me how?

The purpose is essentially creating a unique product code which can easily be referenced and interpreted. Eventually it will be printed out and assigned to a physical item. Just by looking at the code, someone can identify that this is item 0001 from NY or item 0023 from FL.

This "product code" doesn't need to be the ID column. But it does need to be unique, so I thought it may as well be the ID, instead of having two unique numbers associated to each record.

 

[XPS35]

Sorry, I was wrong about the event. You cannot use the even before insert in this case because the state needs to be inputted before you can determine the unique field. Therefor the unique field cannot be the primary key either.
You need to use the after update event on the state field to determine the unique field. This event could look like:

Private Sub State_AfterUpdate()
Dim tMax As Variant
Dim tNum As Integer

Me.Refresh
tMax = DMax("UniqueField", "YourTable", "Left(UniqueField,2) = '" & Me.State.Column(1) & "'")
If IsNull(tMax) Then
    Me.UniqueField = Me.State.Column(1) & "0001"
Else
    tNum = Val(Right(tMax, 4))
    Me.UniqueField = Me.State.Column(1) & Format(tNum + 1, "0000")
End If

End Sub

I made a simplified demo of this. You can download it using this link:
OneDrive

 

[jackd]

?? Why not just have the unique ID for a Supplier, and use the State, a separate field, as part of a search value for the user or whoever.
table record
SupplierID.... State
100020 FL and then concatenate the field values for search/find/?? "100020FL"

 

[JAccess1706]

Sorry, I was wrong about the event. You cannot use the even before insert in this case because the state needs to be inputted before you can determine the unique field. Therefor the unique field cannot be the primary key either.
You need to use the after update event on the state field to determine the unique field. This event could look like:

Private Sub State_AfterUpdate()
Dim tMax As Variant
Dim tNum As Integer

Me.Refresh
tMax = DMax("UniqueField", "YourTable", "Left(UniqueField,2) = '" & Me.State.Column(1) & "'")
If IsNull(tMax) Then
    Me.UniqueField = Me.State.Column(1) & "0001"
Else
    tNum = Val(Right(tMax, 4))
    Me.UniqueField = Me.State.Column(1) & Format(tNum + 1, "0000")
End If

End Sub

I made a simplified demo of this. You can download it using this link:
OneDrive

Sorry for the delay in replying. Thank you very much for this. It works perfectly and is exactly what I was looking for.