Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Appending primary ID

Posted on 2012-03-10
Medium Priority
Last Modified: 2012-03-24
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
Question by:Scott_Kemp
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 85
ID: 37705625
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?

Author Comment

ID: 37705693
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..
LVL 22

Expert Comment

by:earth man2
ID: 37705737
You need to catch problem in data when one of the fields is null.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Author Comment

ID: 37705975
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 :)
LVL 85
ID: 37709652
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.

Author Comment

ID: 37710184
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??
LVL 31

Expert Comment

by:Helen Feddema
ID: 37710219
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


Author Comment

ID: 37710811
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.

LVL 85
ID: 37710928
Just set the Primary Key of your table to use both fields. Access generally will take care of the rest.

Author Comment

ID: 37727283
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.
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 37729063
If you need more help with the "didn't work" part, you'll have to give us an idea of what that means. From what you've posted, it seems that a multi-key field is what you need to uniquely identify an item in your incoming data.

Author Closing Comment

ID: 37760543
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question