Validating if cylinder already in use

[shaz123]

Hi,

Im a bit stuck on validating, what i need to do is to be able to check if a cylinder already in use, by checking the status of the cylinder, if for example the cylinder new status is equal to null then the cylnder is out with a customer and it cannot be used again, however if its status is returned then it can be used. Another thing i have to take into consideration is over a period of tme the cylnider can be used a number of times, and therefore i have to check the status of latest date of that particular cylinder.

For example -
Cylinder number Status Date Delivered New Status
150007871 Delivered 1/11/06 Returned
150007871 Delivered 1/12/06

When production tries to fill a cylinder if they try to zap 15007871, then thye should get a message saying that this particular cylinder is out with a customer on the 1/12 and cannot be used. It is therefore checking the latest clyinder information.

 

[PHV]

Have a look at the DLookUp and the DMax function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shaz123]

This is the code i have come up with, but how would i check to for the latest cylinder?


StrSQ3 = Dlookup("[newStatus]", "tbl_TransactionMasterclient", "[Cylinder Barcode Label] = '" & Stringy & "'")

If StrSQ3 <> "Returned" Then
MsgBox "The Barcode Number is already in use. Please try another", vbCritical, "Barcode number"

 

[PHV]

Perhaps this ?
StrSQ3 = Dlookup("[newStatus]", "tbl_TransactionMasterclient", "[Cylinder Barcode Label]='" & Stringy & "' AND [Date Delivered]=#" _
& DMax("[Date Delivered]", "tbl_TransactionMasterclient", "[Cylinder Barcode Label] = '" & Stringy & "'") & "#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shaz123]

Thankyou i will try it,

 

[shaz123]

Ive tried the codeing but i am recieving an error message,
"Run time error 94" " invalid use of null. I am browsing on the net to try and see how to overcome the problem but any tips would be greatly apreciated.

 

[PHV]

Dim StrSQ3 As Variant

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shaz123]

Hi PHV,

When trying i can still add the same cylinder even though it has not been returned, it is possible to view the results of StrSQ3,

ive tried this
MsgBox "result = '" & StrSQ3 & "'"

When trying this it is not showing the results, is it because it is declared as a variant

 

[PHV]

It is because the result is null (as expected)
Replace this:
If StrSQ3 <> "Returned" Then
with this:
If Trim(StrSQ3 & "") <> "Returned" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shaz123]

Thankyou that wrked

 

[shaz123]

Hi PHV,

Ive picked up on two issues, when trying to run the code, It works in the sense that if a cylinder Barcode is already in use with the R Status being equal to Null then the message does appear to say the barcode is alreay in use, and to please try another, however if i change the R status to returned however the same message appears, it should recognise that the cylinder has been returned and it can be used again.

Another issue is that if i try to add a totally new cylinder barcodee label then i receive this error message!

Run-time error '3075':
"Syntax error in date in query expression '[Cylinder Barcode Label] = '200050202' AND [Time of Transaction] = ##."

To get over the above error message is it necessary to first check if the cylinder barcode is already in use for instance

 StrSQ3 = Dlookup("[Cylinder Barcode Label]", "tbl_TransactionMasterclient", "[Cylinder Barcode Label] ='" & Stringy & "'")
            
If StrSQ3 = "" Then
Flag = "False"
End If

 

[shaz123]

I have managed to get the prevent the error message from appearing, i now trying to get the first bit wrking, even if the cylinder barcode label has been returned, it still recognises it as bieing in use!!!!!!!!