Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check for data duplication with Forms (2000)

    Hi,
    I have a form that is used to place credit card orders for parts. I used to have the MachineID and VendorID as the primary key for the table. I have been recently told that for a particular machine we could have more than one order from the same vendor. So I changed my tables primary key, to where the user can now pick the same machine and same vendor more than once, but I'd like to put some double checks in to make sure they don't order the same parts. My idea was to put a double check on date ordered, partnomen, qty, and price fields to check for dups. I'm not sure how to do this so any help would be aprreciated or a better idea.
    Thanks,
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check for data duplication with Forms (2000)

    You could use DCount to check for duplicates in the Before Update event of the form, probably only if the user is on a new record (If Me.NewRecord Then). If you find them, ask if the user wants to continue, and if not, set Cancel = True. The user then has to change something, or cancel the record entirely. In the following, I have assumed that PartNomen is a text field.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim lngDups As Long
    If Me.NewRecord Then
    lngDups = DCount("*", "TableName", "OrderDate = #" & Me.OrderDate & _
    "# And PartNomen = " & Chr(34) & Me.PartNomen & Chr(34) & _
    " And Qty = " & Me.Qry & " And Price = " & Me.Price)
    If lngDups > 0 Then
    Cancel = (MsgBox("There are " & lngDups & " record(s) with the same data." & _
    vbCrLf & "Do you want to add this record anyway?", vbYesNo) = vbNo)
    End If
    End If
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for data duplication with Forms (2000)

    Hi Hans,
    Thanks for respoding and the below worked for me but in another area of the database, but I ran into a few problems with the current form I'm trying to work with. This is the situation. The form I'm working with is based off of a table and it has a subform that shows the parts. So i quickly realized I had to do one of two things, either base the form off a query that combined the 2 tables so the fields would exist or change the field names to just the subform. I tried the later and am getting the following error message: "Run-time error 438 - Object doesn't support this property or method" Below is the code I'm using. I realized that I don't need to check the date ordered because if they are creating a new order a new date will show up, basically i need to check machine, vendor, partnomen, qty, & price to make sure it's not duplicated, which all those fields are in the subforms query, but I'm getting that error. This is problem 2, while doing some testing I added 2 new parts for the same machine and same vendor and when I select the machine and vendor on a new order ALL the parts whether ordered or not show up. So i obviously have to put some kind of check in there to see if the order data is null. But not sure how and not sure if on right track. Thanks, Deb

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim lngDups As Long

    If Me.NewRecord Then
    lngDups = DCount("*", "qryCCComponentPartsVendorSubform", _
    " lngMachineID = " & Me!sfrmCCComponentPartsVendorSubform.lngMachineID & _
    " and lngVendorID = " & Me!sfrmCCComponentPartsVendorSubform.lngVendorID & _
    " and txtPartNomen = " & Me!sfrmCCComponentPartsVendorSubform.txtPartNomen & _
    " and intQty = " & Me!sfrmCCComponentPartsVendorSubform.intQty & _
    " and curUnitPrice = " & Me!sfrmCCComponentPartsVendorSubform.curUnitPrice)

    If lngDups > 0 Then
    Cancel = (MsgBox("There are " & lngDups & " records that have the same JON" & _
    "and Vendor Part Qty and Price You are placing a duplicate Order" & vbCrLf & _
    "Do you want to create a duplicate order?", vbYesNo) = vbNo)
    End If
    End If
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check for data duplication with Forms (2000)

    If necessary, you can test for duplicates in the subform in the Before Update event of the subform, and test for duplicates in the main form in the Before Update event of the main form. But to test for duplicates in the subform in the Before Update event of the main form doesn't make sense to me.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for data duplication with Forms (2000)

    Is that why I'm getting that error message above? Something with the subform? I can do the testing in the subform as well, what kind of line would I add in to check for null value in the dataordered field?
    Thanks,
    Deb

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for data duplication with Forms (2000)

    Hans,
    When I put that in the before update of the subform nothing happend. I was clearly creating a second order with the same parts. Any thoughts?
    Deb

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check for data duplication with Forms (2000)

    If you just transplanted the code to the subform, it won't work. References such as Me!sfrmCCComponentPartsVendorSubform.lngMachineID should be changed to Me.lngMachineID.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for data duplication with Forms (2000)

    Sorry I should have been more specific. I did do that. And still nothing?
    Deb

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check for data duplication with Forms (2000)

    Is txtPartNomen a text field? If so, the line

    " and txtPartNomen = " & Me!txtPartNomen & _

    should be

    " and txtPartNomen = " & Chr(34) & Me!txtPartNomen & Chr(34) & _

    The Chr(34) put quotes around the value.

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for data duplication with Forms (2000)

    It was a text field and I forgot to copy those over but it didn't help, but I think the problem actually lies in how I have this form/subform setup or worse yet my relationships. Basically I have another form that I call a quote screen that when we get a quote back from a vendor they go into this screen choose the machine and all the parts that have been previously entered under that machine appear in the subform. The user then puts in the price and vendor. THEN they go to this order form where they pick the machine and pick the vendor and the parts will show up that we've said we were going to order for that machine from that particular vendor (from quote screen). Well you would think there would only be one order per machine per vendor but they said they could order from the same vendor for the same machine, bascially if they forgot something or during the build something came up, so I wanted to put a double check in to make sure they didn't double order same parts (has been a problem) so that sparked my posts. Well i've noticed trying to get this to work that if I go back into the quote screen and add some new parts with the same vendor and machine that it automatically takes on the order date of the previous parts that were ordered. Meaning if I go into an old order with the same machine same vendor the new parts automatically show up. So I'm assuming that's why the code wouldn't work but now I have to figure out why this is happening, which i believe is in my relationships.
    Any thoughts on what I might be doing from the explaination above?
    Deb
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check for data duplication with Forms (2000)

    I'm afraid I can't offer much help here. Perhaps someone could assist if you managed to post a stripped down copy of the database.

  12. #12
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for data duplication with Forms (2000)

    To be honest I didnt' think you could. I think I have to rethink my table design. Thanks for your help, that code does help in some other forms and databases I have.
    Thanks a ton.
    Deb

  13. #13
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for data duplication with Forms (2000)

    Actually I do have one question. If I wanted to add a check to the above code to also check to see if the dateordered was null, how would i do that?
    Thanks,
    Deb

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check for data duplication with Forms (2000)

    Do you want to check this separately? And is this about the original situation? If so, you could do something like this:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim lngDups As Long

    If IsNull(Me.OrderDate) Then
    MsgBox "Please enter an order date.", vbExclamation
    Me.OrderDate.SetFocus
    Cancel = True
    Exit Sub
    End If

    If Me.NewRecord Then
    lngDups = DCount("*", "TableName", "OrderDate = #" & Me.OrderDate & _
    "# And PartNomen = " & Chr(34) & Me.PartNomen & Chr(34) & _
    " And Qty = " & Me.Qry & " And Price = " & Me.Price)
    If lngDups > 0 Then
    Cancel = (MsgBox("There are " & lngDups & " record(s) with the same data." & _
    vbCrLf & "Do you want to add this record anyway?", vbYesNo) = vbNo)
    End If
    End If
    End Sub

  15. #15
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check for data duplication with Forms (2000)

    To be honest I'm not sure yet, but that gets me started with the code. I think that's what i want.
    Thanks a bunch.
    deb

Page 1 of 2 12 LastLast

Posting Permissions

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