Validation check

[lamore48]

Hi All,


I created a access database to scan tools in and out of our tool room to be reworked. I have a scan in form which works fine, and I have a scan out form which works also. The problem I have is I have Message Box pop up that asks if any tools are missing from the box that they come in(i.e. scrap or broken) And that part of the database also works. Problem is I want to verify that the user is not entering more missing tools than there are in a box. Example would be that a particular box that was scanned holds 12 tools, and the user scans it and the database asks if there are any missing and the user enters 30. That would skew my data, so I want to put a check in place to look at that particular tool number to see what the default number of tools that are supposed to be in the box and deduct the missing from that number. So I have the following code:

str_SQL2 = "SELECT CGBoxStatusIn.CGBox, ToolDetail.ToolsperBox " & _
                       "FROM ToolDetail INNER JOIN CGBoxStatusIn " & _
                       "ON [ToolDetail].CGBox = [CGBoxStatusIn].CGBox " & _
                       "WHERE (((CGBoxStatusIn.CGBox) ='" & str_CGBox & "'));"




            Set toolsperbox = db.OpenRecordset(str_SQL2, dbOpenDynamic, dbSeeChanges)

And that's where I'm stuck. How to I look at the result of the above query so that I can place it into a variable and subtract the amount missing from that?


The "str_CGBox" is the input field from the scanned box and when I run this query in access it tells me how many tools are in the box.




Where do I go from here?


Thanks in advance,
Lenny

[ranman256]

you could use a list box.
the list box would fill with items in that box.
user can dbl-click the item NOT in the box.
this runs a query to add it to the tMissing tble
the list box qry is updated and no longer shows that item since it is listed in the tMissing tbl : lstBox.requery
thus cannot add items not in the box.

the list box query:
SELECT tBoxes.Part, tMissing.Part
FROM tBoxes LEFT JOIN tMissing ON tBoxes.Part = tMissing.Part
WHERE (((tMissing.Part) Is Null));

...shows items in tBoxes that are NOT in tMissing

[ssanfu]

so I want to put a check in place to look at that particular tool number to see what the default number of tools that are supposed to be in the box and deduct the missing from that number.

You have a text box that has the scanned in number of tools. (Me.ScannedInTools)
You have a text box where the number of scrap or broken tools are entered. (Me.ScrapBrokenTools)
Do you have a table that has the number of tools in a specific box? Query the table to get the number of tools in a specific box - add it to a variable. (intToolsInBox)

Then it would be something like

If intToolsInBox <> (Me.ScannedInTools +  Me.ScrapBrokenTools) Then
    MsgBox "Wrong number of tools for box." & vbcrlf & "Number of Scrap/Broken tools must be " & (intToolsInBox - Me.ScannedInTools)
End If

[lamore48]

You have a text box that has the scanned in number of tools. (Me.ScannedInTools)
You have a text box where the number of scrap or broken tools are entered. (Me.ScrapBrokenTools)
Do you have a table that has the number of tools in a specific box? Query the table to get the number of tools in a specific box - add it to a variable. (intToolsInBox)

Then it would be something like

If intToolsInBox <> (Me.ScannedInTools +  Me.ScrapBrokenTools) Then
    MsgBox "Wrong number of tools for box." & vbcrlf & "Number of Scrap/Broken tools must be " & (intToolsInBox - Me.ScannedInTools)
End If

I have a textbox with the scanned in part number, which I then use a inner joined query to extract the number of tools that are suppose to be in that box. In other words box # 46001-1 defaults to having 12 tools in it. If I scan in #46001-1 and then have a message box pop up and ask if there is any missing and the operator puts in 23, I want it to say stop there are only 12 in this box. So here is my query to find out how many are supposed to be in the box when it is full.

str_SQL2 = "SELECT CGBoxStatusIn.CGBox, ToolDetail.ToolsperBox " & _
                       "FROM ToolDetail INNER JOIN CGBoxStatusIn " & _
                       "ON [ToolDetail].CGBox = [CGBoxStatusIn].CGBox " & _
                       "WHERE (((CGBoxStatusIn.CGBox) ='" & str_CGBox & "'));"



             Set toolsperbox = db.OpenRecordset(str_SQL2, dbOpenDynamic, dbSeeChanges)

So the variable "str_CGBox" would be the box #46001-1 and coming from the above query would be the default number of tools in the box, in this case "ToolDetail.ToolsperBox "
is the field where that number resides. And then I would do something like:
"toolsperbox - missingtools and either throw a messagebox that there is a mistake or continue with my code if missing is less than toolsperbox.

Hope I'm not to confusing in my explaining this.

thanks,
Lenny

[ssanfu]

I understand what you are trying to do, but I don't know where you are stuck.
Please post the code for the subroutine.