Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    no duplicates (Access 2000)

    I have a problem with the no duplicates in the table,since in certain cases it evokes mistakes in my code.
    In my subform, in the combo box productid sometimes the user chooses by mistake one and the same product a second time.
    Since the productid in the table is set to No Duplicates, the user is not allwoed to issue an invoice, however my code stucks and cannot
    open the combo box to choose another product.Therefore i want to put a code that exits the sub.How can i do that ?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no duplicates (Access 2000)

    If your productid may only be used once, then you should rework the recordsource of the combo and make that only the not used productid's are available in it.
    Francois

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: no duplicates (Access 2000)

    This is just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> contribution.

    If the DB is correctly normalised and your relationships are enforced, there should not be a need to have code control this procedure. The relationships will pick up that you have a duplicate and disallow the user to choose the product a second time in the same order. It might be wise to check the design of the underlying tables and relashionships and fix any design flaws. This will prevent any other issues developing at a later stage.

    If you search the lounge for normalization and relashionships...there will be sufficient data to assist in sorting out design problems...elas post back for more details!
    Regards,
    Rudi

  4. #4
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no duplicates (Access 2000)

    Thank you for your reply.The problem is not with the record source of the combo box.The problem is with my code.I presume that my code in the afterupdate event is not "bulletproof" and therefore sometimes it sticks in some cases.One of these cases is that the user , when issuing an invoice,
    chooses again a product, that is already chosen.For example i want to issue an invoice for the product X. I am choosing the product X, then the quantity,
    than i choose again the same product X. Of course this is not allowed becasue of the No Duplicates condition, and then my code sticks.I call this No duplicates condition a "Hard coding".But it creates problems.I want to use in its place some "soft coding",if i may call it. I want to exit the sub and discontinue the execution of my code.So i want a condition like that: If a product is chosen a second time then give a warning message and exit the sub.
    Is it possible ?

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no duplicates (Access 2000)

    In the Before update of the combo box, you should do a dlookup to find if the productid already have been used in this invoice.
    If the dlookup find the item, then you should display a message and set cancel to true. This will prevent the user to go to the quantity textbox until he enter a non-used productid.
    If you need help with this code, please provide the table, field and control names you use.
    Francois

  6. #6
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no duplicates (Access 2000)

    Thank you for your reply.I am sending the attachment.Actually with this simplified version, the hard code works.It will be too complicated for us to send you the full code i am using. But with me, the following happens.If i choose the product X second time, then i am sent back to the original position.However if i choose another product, then my code sticks.Could you help build the condition in the before update event
    to prevent the user at all choosing, say, the product X again ?

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no duplicates (Access 2000)

    Here the modified db. See the Before Update event of the combo box ProductID.

    P.S. the two DoCmd.Setwarnings lines are not necessary.
    Francois

  8. #8
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no duplicates (Access 2000)

    Thank you so much !! It works excellent and it is exactly what i wanted.

    Kind regards

  9. #9
    2 Star Lounger
    Join Date
    Apr 2005
    Location
    Plovdiv
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no duplicates (Access 2000)

    In order to have a better view i have saved my record source of the table as an sql and not as
    query2.Can i still use your BeforeUpdate code? How can i refer to it?
    Since in its original code it was :

    If Not IsNull(DLookup("productid", "query2", "productid = " & Me.ProductID & " and orderid = " & Me.Parent.orderid)) Then
    MsgBox "The product is already in the invoice"
    Me.Undo
    Cancel = True
    End If


    My full query2, converted into sql looks like that:


    SELECT DISTINCTROW [Order Details].OrderID, [Order Details].paymentid, [Order Details].ProductID, Products.size, [Order

    Details].Quantity, Products.pack, [Order Details].liters, [Order Details].cartons, [Order Details].UnitPrice,

    Products.branch0, Products.branch1, Products.branch2, Products.branch3, Products.branch4, Products.branch5, Products.branch6,

    Products.branch7, Products.branch8, Products.branch9, Products.items0, Products.items1, Products.items2, Products.items3,

    Products.items4, Products.items5, Products.items6, Products.items7, Products.items8, Products.items9, Products.office,

    Products.cons, Products.dealer, Products.pref, Products.vip, Products.bosch, Products.drummer, Products.rig, Products.ddu,

    Products.sgkt, Products.sinit, Products.items, Products.branch, [Order Details].Quantity, [Order Details].Discount, [Order

    Details].pieces, [Order Details].liters, [Order Details].extendedprice, [Order Details].LastUpdated, [ExtendedPrice]*1.2 AS

    SalesPrice
    FROM Products INNER JOIN [Order Details] ON Products.Productid = [Order Details].ProductID
    ORDER BY [Order Details].OrderID;


    Can i use your code and refer to the saved sql instead of the query 2?


    Thank you in advance

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: no duplicates (Access 2000)

    I used query2 because it was the recordsource of the subform. In fact, as long as OrderID and ProductID are in the query, you can use it. You can even use the Order Detail table as the two field are also present in the table.

    If Not IsNull(DLookup("productid", "Order Details", "productid = " & Me.ProductID & " and orderid = " & Me.Parent.orderid)) Then
    ...

    This will be the fastest as you don't have to open a query to do the search.
    Francois

Posting Permissions

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