asked on
Appending primary ID
First of all, I'd like to start out by saying I'm new to this forum. Also I am somewhat skilled in programming and dabbling in Access. Currently I have a massive database that holds manufacturing inspection data. There are multiple dimension (as in measurement) tables linked to the 'Inspection form' along with a master table that holds things like quantity, date received etc... The 'supplier lot number' is the primary key and the reference point between these mulitple dimension( part measurement) tables. The database is shared between 2 plants and the data itself is held in Oracle. Some of our multiple part suppliers use the same lot number method (such as the julian date) and deliver to both plants. I'd like to append the 'part number' to the end of the 'lot number to solve this issue. So I put in some VB code to deal with it in the 'after update' event. The problem is I'll get a cannot insert null into <whatever> table #1400. I'm sure it's something rather simple - any ideas? If I need to I can attach the database itself (of course the data will be missing as it's on the local server). Let me know.
Here's the code:
Private Sub SUP_LOT_NUMBER_AfterUpdate
Dim COMPLEX_ID As Variant
COMPLEX_ID = (Me.SUP_LOT_NUMBER & "-" & Me.ctlPartNumber)
MsgBox (COMPLEX_ID)
Me.SUP_LOT_NUMBER = COMPLEX_ID
End Sub
screenshot.JPG
Here's the code:
Private Sub SUP_LOT_NUMBER_AfterUpdate
Dim COMPLEX_ID As Variant
COMPLEX_ID = (Me.SUP_LOT_NUMBER & "-" & Me.ctlPartNumber)
MsgBox (COMPLEX_ID)
Me.SUP_LOT_NUMBER = COMPLEX_ID
End Sub
screenshot.JPG
Microsoft AccessOracle Database
Last Comment
Scott_Kemp
Your error message points to a field name "TRW_LOT_NUMBER", but your code indicates the field is named differently. Are you certain you're adding your builtup number to the right field?
ASKER
Ah - you know - that was my fault - sorry. I forgot to enter the required "TRW Lot number" when I took the screenshot. That really wasn't the problem. I'm not at work now so I can't get an official screenshot, however - I do have a printout of the error. so here it is - see attached. Also to clarify, the user enters the 'supplier lot number' and the after_update event is supposed to paste the 'part number' to the end.
See new attachment..
printout.jpg
See new attachment..
printout.jpg
You need to catch problem in data when one of the fields is null.
ASKER
I believe the problem is in the VB code itself. If I remove this event, I don't get the error. Is it possible that modifying the primary key (supplier_lot_number) with VB code is not allowing it to be modified to the related measurement tables?? There are 10 measurement tables associated with the main table. All are accessed by the 'general_form' Here is a copy of the database minus the data (the tables are linked to an Oracle server). The code in the general_form is what I'm concerned with. Thanks for all of your help so far :)
RecInsp1.09.accdb
RecInsp1.09.accdb
If this is your Primary Key, and if the record is already established, then you shouldn't be modifying that unless you also are prepared to modify all 'downstream' records which may be associated with the value.
From my view, I can't really see a need to do this. You have the data pieces that define a unique record - your SupplierLotNumber and your PartNumber - so just use those to identify a unique record. If you need to join child tables to that parent table, just use both parts of the unique data - IOW, store SupplierLotNumber AND PartNumber in all the child tables, and set those two fields as the unique index of the child tables.
Or perhaps I'm missing something.
From my view, I can't really see a need to do this. You have the data pieces that define a unique record - your SupplierLotNumber and your PartNumber - so just use those to identify a unique record. If you need to join child tables to that parent table, just use both parts of the unique data - IOW, store SupplierLotNumber AND PartNumber in all the child tables, and set those two fields as the unique index of the child tables.
Or perhaps I'm missing something.
ASKER
I agree with what your saying. I guess what I'm after is a way to add the PartNumber to the end of the SupplierLotNumber and save this combined unit as the primary key. At first when using this in one plant only - it wasn't a problem to use the SupplierLotNumber only as the primary key. But now with two plants using this, we've ran into some duplicates. This is caused by different suppliers using the same method for lot tracability. So to fix it, I suggested adding the part number to the end to differentiate the two similar numbers. Currently the users are manually entering the SupplierLotNumber and a dash and then the PartNumber - but this can lead to inconsistancies such as added spaces etc.. So is there a way to keep this data entry in consistant form??
I think you would be better off with using a multi-field key, or (if possible) ensuring that users enter the SupplierLotNumber and PartNumber correctly (possibly by replacing manual entry with a function to create the next valid value). Here is some typical code to automatically create a complex key field value:
Private Sub datAdmitDate_AfterUpdate()
'Created by Helen Feddema 8-13-2002
'Last modified 8-13-2002
On Error GoTo ErrorHandler
Dim strHospStayID As String
Dim lngHospID As Long
Dim varHospID As Variant
Dim dteAdmit As Date
dteAdmit = Me![datAdmitDate]
varHospID = DLookup("[HospNo]", "qmaxHospNo")
If IsNull(varHospID) Then
lngHospID = 1
Else
lngHospID = DLookup("[HospNo]", "qmaxHospNo") + 1
End If
strHospStayID = Format(dteAdmit, "ddmmyy") & Format(lngHospID, "00")
Debug.Print strHospStayID
Me![priHospStayNumber] = strHospStayID
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
'Another variation
Public Function NewID() As Long
'Created by Helen Feddema 8-Jan-2006
'Modified by Helen Feddema 27-Jan-2009
On Error Resume Next
Dim strDateCode As String
Dim strKeyCode As String
Dim intNumber As Integer
Dim lngID As Long
On Error GoTo ErrorHandler
'Auto assignment is based on todays date YYYYMM format and an integer count
'Starting at 10000 for each day
'Filter on the first 6 characters (yyyymm) from User generated ID
strDateCode = Format(Date, "yyyymm")
strFilter = "[DateCode] = " & Chr$(39) & strDateCode & Chr$(39)
strQuery = "qryTemp"
Set dbs = CurrentDb
strSQL = "SELECT * FROM qryAssignmentNumber WHERE " & strFilter & ";"
lngCount = CreateAndTestQuery(strQuery, strSQL)
'Debug.Print "No. of items found: " & lngCount
If lngCount = 0 Then
'No records for today; start numbering
intNumber = 0
Else
intNumber = DMax("[IncNumber]", "qryTemp") + 1
Debug.Print "Max number: " & intNumber
End If
strKeyCode = Format(intNumber, "10000")
strUserAID = strDateCode & "-" & strKeyCode
Debug.Print "New User AID: " & strUserAID
'Add new record to table with new UserAID
Set rst = dbs.OpenRecordset("tbl_assignment")
rst.AddNew
rst![UserAID] = strUserAID
lngID = rst![Id]
Debug.Print "New ID: " & lngID
rst.Update
rst.Close
NewID = lngID
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Function
ASKER
To All :
I think the last solution will work, but I won't know until later this week when I get time to mess with it again. I'll let you know.
Thanks,
Scott
I think the last solution will work, but I won't know until later this week when I get time to mess with it again. I'll let you know.
Thanks,
Scott
Just set the Primary Key of your table to use both fields. Access generally will take care of the rest.
ASKER
I moved all of the data to Access, so I don't have to be at my desk to work on it. While I was at it I enforced referential integrity (not sure why I didn't do this to begin with - and that made me have to scrub the data. I tried the multiple primary key thing and it didn't work -yet. Tomorrow and Saturday I'll have time to try all of the above.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for the idea of the multiple key fields. I understand how it's done now after talking to the local DBA. I finally implemented it (today). It works great.