Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Posts
    25

    Unanswered: Primary Key needs to be broken down

    I have a form, SG Sample_Redone that must match a certified number given to us by the US Government. Due to expansions, this table needs to be broken down in the database according to our main products. I used the SG Sample_Redone form to contain basic information about our product samples. The form then opens other forms to enter specific product information. These other forms must be tracked by our certified number and have no duplicates. I have written a few versions of code (that I did not like) that attempted to notify the user if the number was in existance in another table. I have attatched a portion of the database. Please note that currently the number 6333 exists in the PeasSample table and in the LentilSamples table. I need to prevent this number from being recorded in more than one product sample table. In a sense, the certified number can exist twice in the database (once in the SG Sample_Redone table and in one product table). I have only included two product tables but we have about 15 product tables. Please excuse the mess of my coding, I am still working on creating the new forms and tables. Any advice or code would be very much appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    I've had a look to your db and the only solution to the problem I found is to look up for the last counter number entered in any table you have. Determined that number, just add 1 to it in order to 'create' a new number. I will leave the auto number (counter) option because the counter is based on every single table and even a relationship can't handle duplicate number in different tables - I think-

    Look for the dlookup function in the help menu.
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  3. #3
    Join Date
    Jul 2003
    Posts
    25
    Thanks for taking a look as it. I tried a DLookUp function but currently have it commented out it in my code. The DLookUP is located in the PeasSample1 form. It wasn't working as expected. I added watches to my variables and it seemed to be working but my If statement was never evaluated.

  4. #4
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    Did you upload the db? Could you post the code?
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  5. #5
    Join Date
    Jul 2003
    Posts
    25
    Private Sub Form_Open(Cancel As Integer)
    If (Eval((DLookup("[Key]", "[LentilSamples]") = Me![Key]))) Then
    Beep
    MsgBox "This sample number already exists as a Lentil Sample.", vbCritical, "Duplicate Sample#"
    DoCmd.Close acForm, [PeasSample1], acSaveNo
    End If
    End Sub

  6. #6
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    Stay there I will check it!
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  7. #7
    Join Date
    Jul 2003
    Posts
    25
    Because, the word 'Key' is everywhere throughout the database. I renamed the 'Key' in the underlying table to LentilKey and discovered that the lookup function is not evaluating properly

  8. #8
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    I debuged it, and your if statement is always considered. I just changed a bit the code, like this:

    Private Sub Form_Open(Cancel As Integer)
    Dim p
    p = DLookup("[Key]", "[LentilSamples]")
    If p = Me![Key] Then
    Beep
    MsgBox "This sample number already exists as a Lentil Sample.", vbCritical, "Duplicate Sample#"
    DoCmd.Close acForm, [PeasSample1], acSaveNo
    End If
    End Sub

    The problem is that I can't find a record that is duplicated. What I do is:
    open SG Sample_Redone
    move to a 'Peas' record
    and push the arrow button on top

    Am I doing something wrong?

    I don't find anything that is duplicated because the form named SG Sample_Redone is not showing duplicated records in the first place. The iten # 6333 is shown just once.

    Maybe I misunderstood something.

    Anyway you can check the code by putting a Stop Point in the code (press on the left hand side of the code point itself). This is a good method to see variable and effects. Try and put the arrow of your mouse over a variable and you see its value.

    Still here to help if needed.
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  9. #9
    Join Date
    Jul 2003
    Posts
    25
    The duplicated number of 6333 is located in the underlying LentilSamples and PeasSample tables. Both of these tables have forms which are called from SG Sample_Redone form. Within the SG Sample_Redone table the number 6333 can only exist once because it is the Primary Key. My problem occurs when someone enters the data in the SG Sample_Redone form and chooses a product such as Green Peas. The form for Green Peas is openned and the grading standards are entered. Here's the problem: A user can go back to the SG Sample Redone form and change the original entry of Green Peas to Lentils or any other Product listed. The user can then enter in grade standards into the Lentil table using the exact number that has already been recorded in the PeasSample table. I hope this provides some clarification of what I am trying to do. I will check your code...thanks for helping!

  10. #10
    Join Date
    Jul 2003
    Location
    Italy
    Posts
    139
    I understand now - maybe.

    The code we used should be put in the Afterupdate event of the corresponding radio button. Doing so, you will be able to fire a check on the number to the appropriate table. Obviously you should change the dlookup to search in the appropriate table.

    The code must be in the SG Sample_Redone not in the opened form. Whenever someone tries to change the lentil to peas the check is performed!

    Am I right?
    Marco
    ---Do not send e-mail---
    "New Big Bang Theory: God said unzip universe"

  11. #11
    Join Date
    Jul 2003
    Posts
    25
    I hope your suggestion works because I tried your code numerous times. Access quits working everytime I try to evaluate the code.

  12. #12
    Join Date
    Jul 2003
    Posts
    25
    Now I am trying to compare just the two tables with the following code:

    Dim p, k
    k = DLookup("[PeaKey]", "[PeasSample]")
    p = DLookup("[LentilKey]", "[LentilSamples]")
    If (p = k) Then
    Beep
    MsgBox "This sample number already exists as a Lentil Sample.", vbCritical, "Duplicate Sample#"
    DoCmd.Close acForm, [PeasSample1], acSaveNo
    End If
    End Sub

    I changed the names of the Key field to be more descriptive. This code works for comparing the first record in the PeasSample table to the first record in the LentilSamples table. I need it to search through all records. I tried adding criteria to the lookup function but I kept receiving the following error: Run-time error 2001 You canceled the previous operation. My help file for this error is not available.

  13. #13
    Join Date
    Jul 2003
    Posts
    25
    Thanks for your help. I looked at the code a bit more and decided to leave it alone for awhile. It will take quite a few loops of code to search all the records in the various tables. I tried this for just a few tables and it took way too long to be productive.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •