25 Jan 12 22:57
I have a form that is being filled out. I need to prevent duplicate entries (a duplicate is defined as ALL field values from one record match ALL the field values on any other record).
Now, I've used Dcount quite a bit, but I'm having a little problem here. I can get no value for Dcount except zero.
If DCount("[txtMultiplier_ID]", "tblMultiplier", "[Manufacturer] = """ & Nz(Me.cboManuf) & """ AND [PartType] = """ & Nz(Me.cboPartType) & """ AND [DiscountCode] = """ & _
Nz(Me.cboDiscount) & """ AND [VolDiscount-Low] = " & Nz(Me.txtVolLow) & " AND [VolDiscount-High] = " & Nz(Me.txtVolHigh) & " AND [Multiplier_ID] <> " & _
Nz(Me.txtMultiplier_ID)) > 0 Then
Cancel = True
If MsgBox("This would create duplicate conditions. Press 'OK' to return and complete form. Press 'Cancel' to undo changes.", vbOKCancel, "Please Complete Entry") = vbCancel Then
In some cases, some records under review will have blank fields. After some troubleshooting, it appears that blank fields (such as [DiscountCode]) are considered Null, which, I think, messes with the Dcount function. I have used Nz to make sure the form's text boxes are not null, but I don't think I can prevent it on the Field side of things.
Am I correct about Null, or is there something else going on? I only get 0 for an answer when I know there is at least 1 matching record.