Validating an InputBox on a Form

[shaz123]

I have an inputbox on a form which asks for a cylinder number, the user has to therefore type an cylinder number which they want it to be filled, at the moment the individual can type in any cylinder number, but i would like the cylinder number entered to be validated against the cylinder master table, which is a list of all the cylinders avialable.

I have tried to implement this however even if the cylinder number is the same as the cylinder number within the cylinderMaster table an error still occurs "Run-Time error '3021' No current record".

I have set the recordset to ("tbl_transactionMaster") could this be the fault, would i need to set it to the cylinder master table to???

My COde:

Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim StrSQl As String
    Dim i As Integer
    Dim tringy As Integer
        
    
    Set db = CurrentDb
    
    Set rs = db.OpenRecordset("tbl_TransactionMaster")
    
    For i = 1 To Quantity
        Stringy = InputBox("Do You Wish To Fill A Cylinder For Works Order Number  :-" & [Works Order Number] & " Line Number " & [Line Number] & "...", "Please Enter/Scan Cylinder Serial Number")
      
        If Stringy = rs![Cylinder Number] Then
        Stringy1 = InputBox("Please Input The Transaction Date, (DD / MM / YY)", "Spec Gas System", Me!Text20 & "'")
        Else
        
        MsgBox ("The Cylinder Number entered is not a valid Cylinder Number Please Try Again")
                 
                             
                   
        If [Batch Number] = True Then
       Stringy2 = InputBox("Please Type in the Type of Batch Number")
         End If
        If [Expiration Required] = True Then
    Stringy3 = InputBox("Please Type in the Type of Expiration Date, (DD / MM / YY)", Me!Text20 & "'")
    End If
         
      
      If Stringy <> "" Then
            rs.AddNew
                rs![Works Order Number] = Me![Works Order Number]
                rs![Line Number] = Me![Line Number]
                rs!CustNo = Me!CustNo
                rs!ProdNo = Me!ProdNo
                rs!Status = "Available To Deliver"
                rs![Cylinder Number] = Stringy
                rs![Transaction Date] = Stringy1
                rs![Batch Number] = Stringy2
                rs![Expiration Required] = Stringy3
                         
            rs.Update
        End If
            End If
    Next i
        
    
    rs.close
    db.close
End Sub

 

[BSman]

Why don't you just use a combo box for the cylinder number and bind it to a query that gets the cylinder numbers from the cylinderMaster table? That way the user can only select a valid number from the list.

 

[shaz123]

I would try that but the thing is there is goign to be alot of cylinder numbers within the table, startign from atleast a thousand, whicc would be to consuming for an idividual to go thru, and also the drivers will be scanning the cylinder number into the input box.

 

[BSman]

A thousand is not a lot of records to deal with. When entering a number manually, remember that the combo box automatically does text matching, so it goes to the first match of the text as you type each number in.

Scanning should be an alternate input process. Perhaps click a "Scan" button, then when the scan is done just do a dLookUp function to validate that the scanned number is valid (before you actually get the data). Or just watch for the scanning input and, if scanned, do the dLookUp check, otherwise use the manual combo box input. Even if the scanning goes to the combo box it won't be a problem (unless it doesn't match the values, perhaps because of a bad scan). And then you could handle it appropriately.

 

[shaz123]

I see ur point, im guna give it ago, if i mees up no doubt ill get back to you if i get stuck thnkyou

 

[TheAceMan1]

How are ya shaz123 . . .

Have a look at the [blue]DLookup[/blue] function! . . . example:

[blue]   Dim Criteria As String
   Criteria = "[cylinder number] =[red][b]'[/b][/red]" & [i][purple][b]InputBoxReturn[/b][/purple][/i] & "[red][b]'[/b][/red]"
   
   If IsNull(DLookup("[cylinder number]", "TableName", Criteria)) Then
      'cylinder number not foumd
   Else
      'cylinder number found
   End If[/blue]

If [blue]cylinder number[/blue] is numeric, drop the single quotations in [red]red[/red].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .