Index and Relationships in 'Sub' Table

[Nevsky78]

Hi all,

I have attached a simplified version of the issue I am having at the moment.

I have tbl_orders which records orders. I have a linked table called tbl_order_lines which details the items against each order.

In a nutshell, an item can only appear once in an order. However, I do not know how to create a relationship or code the frm_order in order to reflect this in my DB.

Can anyone help shed some light on this for me please?

Thanks in advance.
Nick

 

[Cronk]

Create a new index in tbl_order_lines, call it say OrderLine, and include the fields for orderID and the Item ID. Make the index unique. Then you cannot have more than one occurrence for the combination.

 

[jdraw]

Here are some excellent video tutorials dealing with
Customer, Order, OrderDetails, Items.


http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Good luck.

 

[Nevsky78]

Hi guys,

I thought I posted a thank you yesterday but I think I had an epic fail somewhere and failed to actually post it!

Thank you both for your help - I have gone with your suggestion Cronk which is working fine in not allowing duplicate values.

My only issue with it is that I am using the AfterUpdate event to create the unique ID so I am getting the standard duplicate error message.

Is there anyway I can catch this error with a custom message box and code that has an automatic undo of that record on clicking OK (vbOKonly)?

Thanks,
Nick

 

[Nevsky78]

Hi Pat,

Thanks for your response. The only way I can see to perform the custom primary key is after a selection has been made in the combo box. The ID is a combination of 2 other fields therefore those fields need to have been added for the current record to create the custom Primary Key.

I have added the following code though which is a bit untidy but I think my best option.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
  Const REQUIREDFIELD_VIOLATION = 3314
  Const INPUTMASK_VIOLATION = 2279
  Const DUPLICATEKEY_VIOLATION = 3022
  If DataErr = DUPLICATEKEY_VIOLATION Then
     MsgBox "This Brand already has a record within this activity. Please edit the amount in the existing record. Click OK and then press the ESC key"
    Response = acDataErrContinue
  End If
End Sub

Nick

 

[Cronk]

A way to avoid duplicate IDs is to store the next ID value in another table.

If UserA starts a new record, the ID lookup table is locked, the next ID is retrieved and incremented in the table and the table is unlocked. UserB cannot get the same ID.

Should UserID not proceed with saving a record, the ID lookup table is locked, the next ID is checked and if not used, the old ID is put back in the lookup table.

The downside with this method is that if UserB has commenced a new record, the UserA ID if not saved, will be discarded.