Preventing a duplicate record from being added on an order form

[Kipandrews06]

I just posted recently and am trying not to get in a habit of posting that much. I hate waiving the white flag and admitting defeat so I usually will be stubborn and spend days sometimes trying to figure out the solution to a problem on my own, but this project has been 100x more complex than anything I've done before so I'm learning alot and getting stuck alot lol so I'm having to ask for help more than usual since I'm now facing a tighter time frame and need to keep moving ahead building the database. This forum has been invaluable and I appreciate everyone's help they've given me so far. So I'm stuck again on something and am not sure how to go about doing it or if this is even possible (I would definitely think that it is). So I will give a quick summary of my database and what it functions as then segway into the issue.

I have an inventory/ordering system I'm trying to setup. We store tradeshow products for several companies in our warehouse and when they have a show coming up and need those materials, they can login the database and select the products and their amounts to have shipped out. So on my main form, I have 2 subforms. Subform1 is a continuous form that's the product list where they can scroll through and select the products they need. I have a cmd button on each record where if they click it, it will add that product to subform2 which acts as an "Item Cart". When a product is added to the cart, it has a default qty of 1 for the qty ordered. The customer can change the amounts obviously but when the order is submitted it automatically deducts those amounts of each product that were ordered in inventory which leads me to the problem I'm having. It probably wouldn't happen that often but it could happen and I want to prevent a user error from throwing off the on hand inventory as best as I can. I want to try and prevent the user from adding a duplicate product to the item cart. Ideally, instead of blocking it from being added if its a duplicate, code it to add to the existing product qty. So for instance if Product A was in the cart, it will have a default qty of 1 like I mentioned for the qty to order. If the customer added 20 products and then added Product A again by accident, then it would make the Product A qty to order become "2". If that's not feasible then I'm open to just blocking it altogether from being added if its already in the cart (that is most likely the easiest method I'm assuming). I just want to make it as user friendly as possible and limit the amount of msg boxes and prompts, etc. I'm not sure if I should code this from the OnClick event of the cmd button on my product list (subform1) since that's how products are added to the cart or if I need to code it on subform2 (item cart). Any suggestions on how I can go about accomplishing this?

One more note: the item cart is linked to an orders table so I don't want to prevent any duplicates ever. I only want to prevent duplicate products within each order or OrderID.

[ranman256]

key the users 'data entry temp' t.OrderDetailUsr table, so they cant duplicate.
then when user is done, add the

t.OrderDetailUsr to the master t.OrderDetail tbl.

[Micron]

You might eliminate the problem by providing a quantity field for the selection process. As for how to automatically increment a count, you could run a Totals query grouped by product (or whatever you call the item) and post the summed values, the product info and whatever details go along with it. This post might be easily accomplished by basing the cart form on the Totals query. I might also use a staging table to base the Totals query on - i.e. a table that reproduces the target table except for the ID field. When you write the "order" to the final table, you clear the staging table. If this is a multi user db and it is split, staging tables usually have to go in the front end. If you put them in the back end, you need a field that separates one person's records from another person.

[Gicu]

Can you show us the code you use in the button to add the items to the cart? Looks like you simply need to add a check to see if the product is already in, if no do what you currently do, if yes run an update query to increment the quantity.

Cheers,

[Kipandrews06]

I haven't gotten as far as setting up the user permissions, etc. As for the totals query, how would I do that? I'm still pretty new to access and learning as I go. I tried the totals query before but didn't understand how to get it to total the way I wanted to. For example, in general customers will order the same product over and over so there will be tons of duplicates. I only want to restrict duplicates within a recordset. So if the totals query has Product A 10 times through all the orders, it sums the qty available incorrectly or the qty ordered. I guess what I mean is lets say the on hand qty for product A is 4. If Product A has 10 records, it will sum 4*10 and there would definitely not be 40 on hand. Or if I totaled it by qty ordered, it's going to total the amount from ALL the orders and not from a recordset between each order #. Maybe I'm just stupid, but when I was messing around with it, I couldn't get it to give me the totals I wanted.

This may be a dumb question, but could it be done through a DAO Recordset function that loops through the recordset of just that order number to look for duplicates? Or what about a composite key that indexes the orderID and productID as unique? I have no clue if those would work, just stuff I seen through google searches and such and wasn't sure if it was applicable here?

[Kipandrews06]

@Gicu, here is the code I'm using for adding products:

 Private Sub Command30_Click()      Dim Response As Integer
      Dim dbsMydbs As DAO.Database
      Dim rstMyTable As DAO.Recordset
30        Me.Dirty = False
      
        If Me.QtyAvailable < 1 Then
      Response = MsgBox("There isn't enough inventory in stock to order this product.", vbCritical + vbOKOnly + vbApplicationModal, "Insufficient Inventory")
           If Response = vbOK Then Exit Sub
        Else
40            Set dbsMydbs = CurrentDb
50            Set rstMyTable = dbsMydbs.OpenRecordset("TblOrders")
60            With rstMyTable
70                .AddNew
80                !ProductOrderID = Me.ProductOrderID
90                !ProductID = Me.ProductID
100                !CompanyID = Me.CompanyID
110               !ProductCode = Me.ProductCode
120               !QtyAvailable = Me.QtyAvailable
130               !Title = Me.Title
                  
140               .Update
150               Me.Requery
160           End With


170       MsgBox "Item Added To Cart", vbInformation, "Complete"


180           rstMyTable.Close
190           Set rstMyTable = Nothing
200           Set dbsMydbs = Nothing
210      [Forms]![frmOrderDetails]![frmItemCartOrdersSubform].Requery


          
220       On Error GoTo 0
230       Exit Sub


Command30_Click_Error:


240       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command30_Click, line " & Erl & "."


    End If
    


   End Sub

My cart subform is linked to "tblOrders" so when it adds it to that, the requery updates the subform with the product in it.

[Micron]

Gicu's suggestion is a good one too, but like many things to be done in Access, there are many ways. That method would require code with a BeforeUpdate event to prevent the additional record as opposed to an update. Alternative would be an unbound form, but I'm not seeing a need to introduce that level of complexity.

There might only be one right way for you; there could be several you could choose from. The totals would have to be restricted to the current order. If you cannot do that based on the order number (it may not exist until finalized) then that's where the staging table could come in. I guess I wasn't clear about the association between the totals query and the staging table, where the only records that would be added to that table would be for one order at a time.

[Kipandrews06]

Oh ok yeah that makes alot more sense to me now about it being associated to the staging table and only totaling on a per order basis. There usually always seems to be so many different ways to do things, it's hard to know which one is better sometimes. Ideally I'd like something like Gicu is suggesting and would be less tables and queries to setup. I'm just not sure how I'd go about doing that though, but I am willing to try anything if it will work. But since I'm not that advanced in access development, the easier the method the better! lol

[Gicu]

I am editing your code right now but I don't see in it the quantity to be order in your recordset.add. Is it the QtyAvailable (which I would have thought would be in the parent form somewhere, not on the subform)?

[Gicu]

I am editing your code right now but I don't see in it the quantity to be order in your recordset.add. Is it the QtyAvailable (which I would have thought would be in the parent form somewhere, not on the subform)? Also where is the OrderID?

[Gicu]

Here is what I was thinking, as I mentioned in my previous post I am a bit confused with your table, could you show a screenshot of a couple of records from tblOrders?
In your code you are not adding the OrderID when adding the new record and also the field for the quantity seems missing or misnamed:

Private Sub Command30_Click()      Dim Response As Integer
Dim dbsMydbs As DAO.Database
Dim rstMyTable As DAO.Recordset
30      Me.Dirty = False
      
        If Me.QtyAvailable < 1 Then
      Response = MsgBox("There isn't enough inventory in stock to order this product.", vbCritical + vbOKOnly + vbApplicationModal, "Insufficient Inventory")
           If Response = vbOK Then Exit Sub
        Else
40            Set dbsMydbs = CurrentDb
50            Set rstMyTable = dbsMydbs.OpenRecordset("TblOrders")
              If DCount("*","TblOrders","[ProductID] = " & me.ProductID & " And [OrderID] = " & Me.OrderID)=0 Then 'new product, just add as before
60                With rstMyTable
70                   .AddNew
80                     !ProductOrderID = Me.ProductOrderID
90                     !ProductID = Me.ProductID
100                    !CompanyID = Me.CompanyID
110                    !ProductCode = Me.ProductCode
120                    !QtyAvailable = Me.QtyAvailable
130                    !Title = Me.Title                  
140                   .Update
150                   Me.Requery
160               End With
	      Else
		'need to just edit the quantity orders	
		CurrentDb.Execute "UPDATE TBlOrders SET [QtyAvailable] = [QtyAvailable] + " & Me.QtyAvailable & " WHERE [ProductID] = " & me.ProductID & _
				" And [OrderID] = " & Me.OrderID,dbFailOnError
170       MsgBox "Item Added To Cart", vbInformation, "Complete"


180           rstMyTable.Close
190           Set rstMyTable = Nothing
200           Set dbsMydbs = Nothing
210      [Forms]![frmOrderDetails]![frmItemCartOrdersSubform].Requery




          
220       On Error GoTo 0
230       Exit Sub




Command30_Click_Error:




240       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command30_Click, line " & Erl & "."


    End If
    
   End Sub

[Kipandrews06]

Gicu, so everything is linked through the ProductOrderID. The main form is called frmOrderDetails, in it there's two subforms. Both continuous forms, one is the product list called frmProductListSubform and the other is the item cart which is called frmItemCartOrdersSubform. I do have a QtyAvailable field which is in the product list subform where the add button is. There is also a QtyAvailable textbox in the item cart form that is a dlookup of QtyAvailable that is set to not visible and use it to reference the value. There is no orderID on the product list or item cart, but they both have a ProductOrderID and I have an unbound txt box on the main form to reference the ProductOrderID to link the subforms to the main form. The OrderID though is on the main form and through the table relationships and queries links it all together with each order.

[Kipandrews06]

Gicu yeah I will get a screenshot here soon, but in regards to the qty thats ordered, there is a field called QtyOrdered which is in the item cart and is editable in case the user wants to adjust the qty, but the default is set to 1 so when a product is added to the tblOrders it naturally has a qty of 1 so it didn't really need to be put in the code I thought.

[Gicu]

So please replace OrderID with ProductOrderID in my code (in 2 places).
Still confused about which quantity field you want to edit:

Product A was in the cart, it will have a default qty of 1 like I mentioned for the qty to order. If the customer added 20 products and then added Product A again by accident, then it would make the Product A qty to order become "2"

Which field defaults to 1 and you want to make 2 if added again? That is the one you need to specify in the Else part of my If Statement (instead of QtyAvailable).

Cheers,

[Gicu]

Just saw your latest post, so replace QtyAvailable with QtyOrdered:

Else
        'need to just edit the quantity orders    
        CurrentDb.Execute "UPDATE TBlOrders SET [QtyOrdered] = [QtyOrdered] + " & Me.QtyOrdered & " WHERE [ProductID] = " & me.ProductID & _
                " And [ProductOrderID] = " & Me.ProductOrderID,dbFailOnError