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
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