Solved

Appending primary ID

Posted on 2012-03-10
12
334 Views
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)

MsgBox (COMPLEX_ID)
Me.SUP_LOT_NUMBER = COMPLEX_ID
End Sub
screenshot.JPG
0
Comment
Question by:Scott_Kemp
12 Comments
 
LVL 84
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?
0
 

Author Comment

by:Scott_Kemp
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..
printout.jpg
0
 
LVL 22

Expert Comment

by:earth man2
ID: 37705737
You need to catch problem in data when one of the fields is null.
0
 

Author Comment

by:Scott_Kemp
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 :)
RecInsp1.09.accdb
0
 
LVL 84
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.
0
 

Author Comment

by:Scott_Kemp
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??
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

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

Open in new window

0
 

Author Comment

by:Scott_Kemp
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.

Thanks,
Scott
0
 
LVL 84
ID: 37710928
Just set the Primary Key of your table to use both fields. Access generally will take care of the rest.
0
 

Author Comment

by:Scott_Kemp
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.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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.
0
 

Author Closing Comment

by:Scott_Kemp
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.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now