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

Scott McDaniel (EE MVE )

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?

Scott_Kemp

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

earth man2

You need to catch problem in data when one of the fields is null.

Scott_Kemp

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

Scott McDaniel (EE MVE )

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.

Scott_Kemp

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

Helen Feddema

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

Scott_Kemp

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

Scott McDaniel (EE MVE )

Just set the Primary Key of your table to use both fields. Access generally will take care of the rest.

Scott_Kemp

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

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott_Kemp

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.