Index and Relationships in 'Sub' Table (1 Viewer)

[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

 

[Pat Hartman]

If you are using the Form's AfterUpdate event to modify a record, that is your problem. The AfterUpdate event doesn't run until AFTER the record is saved - without the generated ID. Move the code to the BeforeUpdate event so the ID will get saved with the record the first time.

 

[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

 

[Pat Hartman]

Both forms and controls have Before and After update events. I suggested using the FORM's BeforeUpdate event because you want to generate the ID as late in the process as possible to minimize the potential for generating duplicates and the FORM's BeforeUpdate event is the LAST event that runs before the record is actually saved. You do need the code you put in the Error sub to trap the unlikely situation where two users somehow generate the same ID. As I said, the earlier in the process you generate the ID the more likely you are to encounter a duplicate. UserA starts a new record and you generate an ID. UserA answers phone. UserB starts a new record and you generate the same ID as for UserA since UserA hasn't yet saved his record. UserB saves and moves on. UserA comes back to the form and when he saves, he gets a duplicate key message. That's why the ID should be generated as close to the actual save as possible.

 

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