Popup box If criteria met

[Kelsey]

I have a screen "frmIssueNew" where we issue parts from our parts room to trucks being built. Parts get issued to an Operation Number in the truck. Currently there is no way of limiting which operation number the inventory items go to. for example... They could issue Wrenches to operation number 1.00 when it really goes to 2.11. I have made a table "tblNon" that lists some of the obvious InventoryID's that do NOT go with certain OperationNumberID's.

tblNon
NonID
InventoryID
OperationNumberID

I'm not sure if this is the best way to set it up. Also, I need help creating a pop up box that does not allow these parts to get issued to those certain operation Numbers.

Any advice is greatly appreciated!! Thanks!

 

[pbaldy]

That seems like a reasonable way to go. In your code, at the appropriate point, you can test that table using values from the form users are entering. I happen to have open a db that does something similar, comparing parts to vehicles. It uses a query to check a table similar to yours, and the code is:

If DCount("*", "qryLookupPartbyCarIssuePart") = 0 Then

Basically you create a query that uses the form for its criteria. If there's a match in the table it will return 1 record, otherwise 0.

 

[Kelsey]

Great. I might need a little help on this one. I just created a query "qryPartsPerOperationNumber" that looks at a couple tables at what has been checked out to what operation number. Now for the DCount....

Private Sub OperationNumberID_BeforeUpdate(Cancel As Integer)
If DCount("*", "qryPartsPerOperationNumber") = 0 Then
MsgBox "Bad"
End If
End Sub

I just put the MsgBox "bad" on there to try and get the DCount Working like I want it. I've never used DCount before. I'm sure I missed something obvious.

 

[pbaldy]

Does the query return a record for a valid combination and none for an invalid combination? I assume it's using the form to know what's been entered?

 

[Kelsey]

There we go, I got it working. Thanks so much for your help!!

 

[pbaldy]

Happy to help!