Avoid Duplicating in a given Condtion

[shahiq]

Guys, I have been working on this Inventory application using Ms Access and I am almost done. A big thanks to some beautiful tips on ur forum. However I ve got stucked on one little thing.

Its, for example when I create a purchase order and start entering Product items in Purchase order, I dont want same Product item to be entered twice in a same Purchase order. Meaning for example Product ID: 11001 shouldnt be entered more than once for the Purchase Order number 'PO0010'.

Can anyone please help me in this. Here I have attached a sample application containing necessary forms.

Big thanks

 

[rainman89]

use a
if dcount("*", yourtablename, "productid = productid" and "orderID=orderID" )>0 then
msgbox ("More than one product in same order")
else
end if
http://www.mvps.org/access/general/gen0018.htm

 

[shahiq]

use a
if dcount("*", yourtablename, "productid = productid" and "orderID=orderID" )>0 then
msgbox ("More than one product in same order")
else
end if
http://www.mvps.org/access/general/gen0018.htm

Thank you very much for your help dude. that code seem to be okay but I get this error message. "run time error 13", "type mismatch"

Any idea?

Would appreciate if you could work it on my attachment and put it back.



thanks

 

[rainman89]

Thank you very much for your help dude. that code seem to be okay but I get this error message. "run time error 13", "type mismatch"
Any idea?
Would appreciate if you could work it on my attachment and put it back.
thanks

Type mismatch means that u are not referencing something right.. what exact code are u using?

 

[shahiq]

Type mismatch means that u are not referencing something right.. what exact code are u using?

Private Sub ProductID_BeforeUpdate(Cancel As Integer)

If DCount(tblinvInventoryTransactions, "ProductID = ProductID" And "PurchaseOrderID=PurchaseOrderID") > 0 Then
MsgBox ("More than one product in same order")
Else
End If
End Sub

thats the code i used.

thanks

 

[rainman89]

First thing is should be after update

Private Sub ProductID_[B]After[/b]Update(Cancel As Integer)

If DCount("*", "tblinvInventoryTransactions", "ProductID =ProductID" _
            & "AND PurchaseOrderID=PurchaseOrderID") > 0 Then
MsgBox ("More than one product in same order")
Else
End If
End Sub

 

[shahiq]

Sorry to disturb you again. this code thing isnt working. now I am getting this message.

Run-time error '3075'

Syntax error(missing operator) in query expression 'ProductID=ProductIDAND purchaseOrderID=PurchaseOrderID'



Please help me..

 

[rainman89]

K give me a minute to look at ur db

 

[rainman89]

If DCount("*", "tblinv_Inventory_Transactions", "ProductID=" & Me.ProductID & "AND [PurchaseOrderID]=" & Me.[PurchaseOrderID]) > 0 Then

This goes in the after update of your purchaseID box~!

 

[shahiq]

If DCount("*", "tblinv_Inventory_Transactions", "ProductID=" & Me.ProductID & "AND [PurchaseOrderID]=" & Me.[PurchaseOrderID]) > 0 Then

This goes in the after update of your purchaseID box~!

thanks dude... it works well.

 

[EdFred]

On my purchase order items table I simply have primary keys of PONumber and ProductID, No coding necessary.

PS - I'm a heretic, I don't use autonumbers. But it saves coding down the line.