Link to home
Create AccountLog in
Avatar of Scott_Kemp

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)

End Sub
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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?
Avatar of Scott_Kemp


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..
You need to catch problem in data when one of the fields is null.
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 :)
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.
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
      lngHospID = DLookup("[HospNo]", "qmaxHospNo") + 1
   End If
   strHospStayID = Format(dteAdmit, "ddmmyy") & Format(lngHospID, "00")
   Debug.Print strHospStayID
   Me![priHospStayNumber] = strHospStayID
   Exit Sub

   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
      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![UserAID] = strUserAID
   lngID = rst![Id]
   Debug.Print "New ID: " & lngID
   NewID = lngID
   Exit Function

   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

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.

Just set the Primary Key of your table to use both fields. Access generally will take care of the rest.
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.
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.