Unique ID issue (1 Viewer)

Rowey

Registered User.
Local time
Tomorrow, 05:19
Joined
Oct 18, 2018
Messages
20
Hi All - I hope I am in the right area for this.
I have a parts database for which the initial data was was originally held in an excel spreadsheet. The existing parts ID had a text(alpha) prefix (eg FW2327).
In order to maintain the sequence which was insisted upon, I built an unbound form to collect the new part details and used a lookup query to establish the next PartID.
This lookup is collected when the save button event is triggered.(Ithought that as there were only two cataloguer users in the small business at the time that this would probably never trigger a duplicate record save attempt.
They now have additional cataloguers and occassionaly they get a clash and have to restart the save.
I cant now implement an autonumber field because of the many thousands of related records.

below is the code for the save button -
Private Sub btnSave_Click()
' Comments:
' Params :
' Modified:

'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

Dim varHProd
Dim varHPid
Dim varHPpc
Dim varHPprefix
Dim varX
Dim rstTrSet As DAO.Recordset

If Me!Category = "none" Then
MsgBox "You must enter a Category"
Me!Category.SetFocus
Exit Sub
End If

If Me!Units = "none" Then
MsgBox "You must enter a unit"
Me!Units.SetFocus
Exit Sub
End If

If Me!ManLN = "none" Then
MsgBox "You must enter a Manufacturer"
Me!ManLN.SetFocus
Exit Sub
End If

If Me!Description = "none" Then
MsgBox "You must enter a Description"
Me!Description.SetFocus
Exit Sub
End If

Set rstTrSet = CurrentDb.OpenRecordset("Parts_tbl", dbOpenDynaset)
With rstTrSet
.AddNew
!PartID = DLookup("Maxofpartid", "maxpartid") + 1
!ProdCode = DLookup("maxofprodcode", "maxprodcode") + 1
!Prefix = Format(Me!Prefix, ">")
!ProdCode = Me!ProdCode
!ComboCode = !Prefix & !ProdCode
varHProd = !ComboCode
!PartNo = Me!PartNo
!UPC_EAN = Me!UPC_EAN
!PType = Me!PType
!Category = Me!Category
!Description = Me!Description
!Units = Me!Units
!ManLN = Me!ManLN
!Man = Me!Man
!ManSeries = Me!ManSeries
!Weight = Me!Weight
!Height = Me!Height
!Length = Me!Length
!Width = Me!Width
!Show = True
varHPid = !PartID
varHPpc = !ProdCode
varHPprefix = !ComboCode
.Update
.Close
End With
Forms!mainfrm!HoldKey = varHProd
Set rstTrSet = CurrentDb.OpenRecordset("log_tbl", dbOpenDynaset)
With rstTrSet
.AddNew
!MyKey = varHPid
!MyKeyName = "Add New Part"
!frmname = "PartsAddFrm"
!fldname = "All New fields"
!UserId = Forms!mainfrm!User
!oldval = "Nothing"
!NewVal = "New Part Added " & varHPpc
!logkey = Forms!mainfrm!HoldKey
.Update
.Close
End With
Set rstTrSet = Nothing
Me!Prefix = Null
Me!ProdCode = Null
Me!PartNo = Null
Me!UPC_EAN = Null
Me!PType = Null
Me!Category = Null
Me!Description = Null
Me!Units = Null
Me!ManLN = Null
Me!Man = Null
Me!ManSeries = Null
Me!Weight = Null
Me!Height = Null
Me!Length = Null
Me!Width = Null
Forms!partsinventoryfrm.Form.RecordSource = "MainQuery"
Set rstTrSet = Forms!partsinventoryfrm.Form.RecordsetClone
rstTrSet.FindFirst ("[partid]=" & varHPid)
Forms!partsinventoryfrm.Bookmark = rstTrSet.Bookmark
Set rstTrSet = Nothing
MsgBox "Part " & varHPprefix & " added succesfully!"
varX = Cform([Form].[Name], [Form].[OpenArgs])

'TVCodeTools ErrorHandlerStart
PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox Err.Description, vbCritical, Me.Name & ".btnSave_Click"
Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd

End Sub

Does anyone have a suggestion on how I may be able to retain the current data structure or alternatively any other suggestions
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:19
Joined
Oct 29, 2018
Messages
21,358
Hi. I didn't read all your code, but with regards to using an Autonumber field, "thousands of related records" shouldn't stop you from using it. An UPDATE query can take care of it pretty fast for you. Just my 2 cents...
 
Last edited:

Rowey

Registered User.
Local time
Tomorrow, 05:19
Joined
Oct 18, 2018
Messages
20
Thanks theDBguy
The problem is that they have all there parts labeled in three stores and the unique Part ID sequence also needs to be maintained. Creating an autonumber field will mean a change to all the existing part numbers.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:19
Joined
Oct 29, 2018
Messages
21,358
Hi. If the Part ID is something like FW2327, I don't see how you can't convert that into an Autonumber field without loosing any data. What does the FW stand for? If it's a constant, then you don't even need it. If so, then it should be possible to convert the 2327 piece into an Autonumber. The only issue is, you may have to update your table relationships.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:19
Joined
May 21, 2018
Messages
8,463
Somewhere someone did a pretty slick procedure for generating PK and checking for concurrent user issues. I do not know if it is FAQ here. I tried to search for it. I remember it had a table to store the next available key and everyone was pulling the key from that table, but I cannot remember the rest of the logic.
 

Rowey

Registered User.
Local time
Tomorrow, 05:19
Joined
Oct 18, 2018
Messages
20
you cant convert an existing number field to an autonumber field. The FW is a concatted text field. The existing number field is the unique ID and all the related tables use this number field.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:19
Joined
Oct 29, 2018
Messages
21,358
you cant convert an existing number field to an autonumber field. The FW is a concatted text field. The existing number field is the unique ID and all the related tables use this number field.
I was thinking more like moving the data to a table with an Autonumber field when I was talking about converting it.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:19
Joined
May 21, 2018
Messages
8,463
The technique is pretty simple. Add an autonumber field. So now you have a new id. In all the related tables simply update the
New ID Old ID
1 FV234

Now update all related tables create New_FK since you are using a numeric
Set New_FK = new ID where old_FK = Old ID
Should take a few minutes even if you have several related tables.
 

Rowey

Registered User.
Local time
Tomorrow, 05:19
Joined
Oct 18, 2018
Messages
20
Thats it MajP!! The DMax method with error trapping solves the problem - thanks very much. I love a quick and simple solution.
 

Rowey

Registered User.
Local time
Tomorrow, 05:19
Joined
Oct 18, 2018
Messages
20
Rogers Access Blog is a great site too.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:19
Joined
Oct 29, 2018
Messages
21,358
Thats it MajP!! The DMax method with error trapping solves the problem - thanks very much. I love a quick and simple solution.
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:19
Joined
Feb 19, 2002
Messages
42,976
It was not necessary to create an unbound form. All you need to know is how to use the FORM's BeforeUpdate event for validation to give you all the control you need.
 

Users who are viewing this thread

Top Bottom