Criteria help

[jcaptchaos2]

I have a form were the employee scans a bar code and it will enter the data into a table, the code below helps so the employee only has to scan 1 bar code instead of 3. What is happening is that some employees are typing in the information instead of scanning it and it is resulting in incorrect data. I have changed all of the entry boxes to combo boxes allowing me to block invalid item or shop order numbers from being entered. The issue I have now is that a shop order number is being entered with a wrong item number, the shop order number is assigned to a specific Item number.I need to come up with something that would block that from happening. I have a table named "shoporderstatus" that has the item number and shop order number matched up. Is there a way lets say in the "Shop order Number" combo box after event that I could have it flag that the item number doesn't match up with the shop order number?

Private Sub Item_number_AfterUpdate()
    Static abort As Boolean
    If abort Then abort = False: Exit Sub
    Shop_Order_Number = Mid(Item_Number, 6, 5)
    Op_Number = Mid(Item_Number, 11)
    abort = True
    Item_Number = Mid(Item_Number, 1, 5)
End Sub

 

[Trevor G]

Have you considered 2 combos, one to list the shops, then the next to filter and only show the order numbers?

You can use a requery/refresh to the second combo.

 

[jcaptchaos2]

Trevor,

Thanks for replying, the issue I am having is for example:
We create a new order number 12345 and it is tied to Item #55555 there is nothing to stop an employee from entering Item # 77777 and then Shop order number 12345. I want to block that so the employee can only enter shop order 12345 if in the above combo box the Item number is 55555. I may not understand what you are suggestion.

 

[Trevor G]

OK let me ask you this.

Are you saying that they must place in an Order Number before being allowed to add in a New Item number !

 

[jcaptchaos2]

Prior to the employee transacting any data the schedulers create a shop order # which is tied to an item number, the employee has a piece of paper with a bar code on it and if they would scan that bar code it would enter an Item number in the first combo box, a shop order in the second combo box and an op # in the third combo box. If they used the scan all would be well but they are also trying to type that same info in and when they do that they are able to type an item number in that is in the itemnumbertbl lets say 55555 and then they are able to type in a shop order number that is in the shoporderstatus table lets say 12345. Both of these numbers have been created prior to the employee trying to enter them in. I need to make sure that the employee doesn't type in Item number 44444 and then shop order number 12345 because it belongs with Item number 55555.

 

[jcaptchaos2]

Alos Item number 55555 may have multiple shop order numbers but a shop order number can only have 1 item number attached to it.

 

[jcaptchaos2]

Ok I have found a way around my initial question and am populating the item number using Dlookup and looking at the shop order table where they are matched up. Eliminating the Item number combo box makes the top code invalid so I am modifying the bottom code to take it's place. What it does is allow the employee to scan one bar code and then the data is split into the proper text/combo box. The bottom modified code is not working, can you see anything that I may have done wrong? I know in excel you use mid when you are splitting things into 3's and use right and left when you split 2 things is it the same in Access? The 3rd code here is my final code where it uses the Dlookup to fill in the item number and then I want it to split the shop order number and the op number but that is not working.

old code

Private Sub Item_Number_AfterUpdate()
    Static abort As Boolean
    If abort Then abort = False: Exit Sub
    Shop_Order_Number = Mid(Item_Number, 6, 5)
    Op_Number = Mid(Item_Number, 11)
    abort = True
    Item_Number = Mid(Item_Number, 1, 5)
End Sub

modified code

Private Sub Shop_Order_Number_AfterUpdate()
    Static abort As Boolean
    If abort Then abort = False: Exit Sub
    Op_Number = Mid(Item_Number, 6, 2)
    abort = True
    Shop_Order_Number = Mid(Shop_Order_Number, 1, 5)
End Sub

final code: The Dlookup alone works fine in this but adding the rest does not split the shop order and op numbers.

Private Sub Shop_Order_Number_AfterUpdate()
Item_Number = DLookup("[itemnumber]", "[shoporderstatus]", "[shoporderstatus]![shopordernumber]= " & [Forms]![Shop Floor Data Entry]![Shop Order Number])
   Static abort As Boolean
    If abort Then abort = False: Exit Sub
    Op_Number = Mid(Item_Number, 6, 2)
    abort = True
    Shop_Order_Number = Mid(Shop_Order_Number, 1, 5)
End Sub

 

[jcaptchaos2]

Ok, I got this to work with one exception, I had to remove the limit to list "Yes" and change it to "No". Is there a way to continue to have it limit to the list. I guess what I would need the limit to list to do is check it after the formula below is through and since it's checking as soon as the first data is entered by scanning a bar code it errors out.

Private Sub Shop_Order_Number_AfterUpdate()
   Static abort As Boolean
    If abort Then abort = False: Exit Sub
    Op_Number = Mid(Shop_Order_Number, 6, 3)
    abort = True
    Shop_Order_Number = Left(Shop_Order_Number, 5)
    Item_Number = DLookup("[itemnumber]", "[shoporderstatus]", "[shoporderstatus]![shopordernumber]= " & [Forms]![Shop Floor Data Entry]![Shop Order Number])
End Sub