Textbox validation

DoubleD

New Member

Join Date: Jun 2015
Posts: 24

Textbox validation

Jun 9 '15, 08:09 AM

Hi,

I need assistance. I have a form in MS Access 2007. On this form I have 30 textboxes. 15 Of these are disabled and get data from a SQL query. The values of these textboxes is a number of which some are more than or equal to 0 and some are less than 0. The other 15 textboxes are editable, but they are set to the value of the parent (uneditable) textboxes.

I need to set the editable textboxes so that the value I enter in there cannot be more than the parent textbox value but also not less than 0.

I have implemented some code to do this, but from the 10th editable textbox onward, the code no longer works. Up to the 9th it still works the way I want. I must be doing something wrong.

Here is the code I use:

Code:

 'Populate size on hands
    Dim db As Database
    Dim rs As Recordset
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT ROUND(S1,0) As Size1, Round(S2,0) AS Size2, Round(S3,0) AS Size3, Round(S4,0) AS Size4, Round(S5,0) AS Size5, Round(S6,0) AS Size6, Round(S7,0) AS Size7, Round(S8,0) AS Size8, Round(S9,0) AS Size9, Round(S10,0) AS Size10, Round(S11,0) AS Size11, Round(S12,0) AS Size12, Round(S13,0) AS Size13, Round(S14,0) AS Size14, Round(S15,0) AS Size15, ROUND(Total,0) AS TOTALUNITS FROM dbo_vw_StockBySize WHERE LEFT (ITEMNO,4) = '" & Me.styleSkuTxt.Value & "' AND RIGHT(ITEMNO,3) = '" & Me.brandSkuTxt.Value & "' AND LOCATION = '" & Me.fromBranchTxt.Value & "';")
       
    Me.s1OhTxt = rs!Size1
    Me.s2OhTxt = rs!Size2
    Me.s3OhTxt = rs!Size3
    Me.s4OhTxt = rs!Size4
    Me.s5OhTxt = rs!Size5
    Me.s6OhTxt = rs!Size6
    Me.s7OhTxt = rs!Size7
    Me.s8OhTxt = rs!Size8
    Me.s9OhTxt = rs!Size9
    Me.s10OhTxt = rs!Size10
    Me.s11OhTxt = rs!Size11
    Me.s12OhTxt = rs!Size12
    Me.s13OhTxt = rs!Size13
    Me.s14OhTxt = rs!Size14
    Me.s15OhTxt = rs!Size15
    'Populate Units
    Me.unitsTxt = rs!TOTALUNITS
    
    'Populate Size Send Qty
    Me.s1SndQtyTxt = Me.s1OhTxt.Value
    Me.s1SndQtyTxt = Me.s1OhTxt.Value
    Me.s2SndQtyTxt = Me.s2OhTxt.Value
    Me.s3SndQtyTxt = Me.s3OhTxt.Value
    Me.s4SndQtyTxt = Me.s4OhTxt.Value
    Me.s5SndQtyTxt = Me.s5OhTxt.Value
    Me.s6SndQtyTxt = Me.s6OhTxt.Value
    Me.s7SndQtyTxt = Me.s7OhTxt.Value
    Me.s8SndQtyTxt = Me.s8OhTxt.Value
    Me.s9SndQtyTxt = Me.s9OhTxt.Value
    Me.s10SndQtyTxt = Me.s10OhTxt.Value
    Me.s11SndQtyTxt = Me.s11OhTxt.Value
    Me.s12SndQtyTxt = Me.s12OhTxt.Value
    Me.s13SndQtyTxt = Me.s13OhTxt.Value
    Me.s14SndQtyTxt = Me.s14OhTxt.Value
    Me.s15SndQtyTxt = Me.s15OhTxt.Value
End Sub

'Check to see if Qty sending more than on hand and reset back to on hand if more


Private Sub s1SndQtyTxt_AfterUpdate()
    If Me.s1SndQtyTxt > Me.s1OhTxt.Value Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s1SndQtyTxt.Value = Me.s1OhTxt.Value
    ElseIf Me.s1SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s1SndQtyTxt.Value = Me.s1OhTxt.Value
    End If
End Sub

Private Sub s2SndQtyTxt_AfterUpdate()
    If Me.s2SndQtyTxt > Me.s2OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s2SndQtyTxt.Value = Me.s2OhTxt.Value
    ElseIf Me.s2SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s2SndQtyTxt.Value = Me.s2OhTxt.Value
    End If
End Sub

Private Sub s3SndQtyTxt_AfterUpdate()
    If Me.s3SndQtyTxt > Me.s3OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s3SndQtyTxt.Value = Me.s3OhTxt.Value
    ElseIf Me.s3SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s3SndQtyTxt.Value = Me.s3OhTxt.Value
    End If
End Sub

Private Sub s4SndQtyTxt_AfterUpdate()
    If Me.s4SndQtyTxt > Me.s4OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s4SndQtyTxt.Value = Me.s4OhTxt.Value
    ElseIf Me.s4SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s4SndQtyTxt.Value = Me.s4OhTxt.Value
    End If
End Sub

Private Sub s5SndQtyTxt_AfterUpdate()
    If Me.s5SndQtyTxt > Me.s5OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s5SndQtyTxt.Value = Me.s5OhTxt.Value
    ElseIf Me.s5SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s5SndQtyTxt.Value = Me.s5OhTxt.Value
    End If
End Sub

Private Sub s6SndQtyTxt_AfterUpdate()
    If Me.s6SndQtyTxt > Me.s6OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s6SndQtyTxt.Value = Me.s6OhTxt.Value
    ElseIf Me.s6SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s6SndQtyTxt.Value = Me.s6OhTxt.Value
    End If
End Sub

Private Sub s7SndQtyTxt_AfterUpdate()
    If Me.s7SndQtyTxt > Me.s7OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s7SndQtyTxt.Value = Me.s7OhTxt.Value
    ElseIf Me.s7SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s7SndQtyTxt.Value = Me.s7OhTxt.Value
    End If
End Sub

Private Sub s8SndQtyTxt_AfterUpdate()
    If Me.s8SndQtyTxt > Me.s8OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s8SndQtyTxt.Value = Me.s8OhTxt.Value
    ElseIf Me.s8SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s8SndQtyTxt.Value = Me.s8OhTxt.Value
    End If
End Sub

Private Sub s9SndQtyTxt_AfterUpdate()
    If Me.s9SndQtyTxt > Me.s9OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s9SndQtyTxt.Value = Me.s9OhTxt.Value
    ElseIf Me.s9SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s9SndQtyTxt.Value = Me.s9OhTxt.Value
    End If
End Sub

Private Sub s10SndQtyTxt_AfterUpdate()
    If Me.s10SndQtyTxt > Me.s10OhTxt.Value Then
       MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
       Me.s10SndQtyTxt.Value = Me.s10OhTxt.Value
    ElseIf Me.s10SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s10SndQtyTxt.Value = Me.s10OhTxt.Value
    End If
End Sub

Private Sub s11SndQtyTxt_AfterUpdate()
    If Me.s11SndQtyTxt > Me.s11OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s11SndQtyTxt.Value = Me.s11OhTxt.Value
    ElseIf Me.s11SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s11SndQtyTxt.Value = Me.s11OhTxt.Value
    End If
End Sub

Private Sub s12SndQtyTxt_AfterUpdate()
    If Me.s12SndQtyTxt > Me.s12OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s12SndQtyTxt.Value = Me.s12OhTxt.Value
    ElseIf Me.s12SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s12SndQtyTxt.Value = Me.s12OhTxt.Value
    End If
End Sub

Private Sub s13SndQtyTxt_AfterUpdate()
    If Me.s13SndQtyTxt > Me.s13OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s13SndQtyTxt.Value = Me.s13OhTxt.Value
    ElseIf Me.s13SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s13SndQtyTxt.Value = Me.s13OhTxt.Value
    End If
End Sub

Private Sub s14SndQtyTxt_AfterUpdate()
    If Me.s14SndQtyTxt > Me.s14OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s14SndQtyTxt.Value = Me.s14OhTxt.Value
    ElseIf Me.s14SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s14SndQtyTxt.Value = Me.s14OhTxt.Value
    End If
End Sub

Private Sub s15SndQtyTxt_AfterUpdate()
    If Me.s15SndQtyTxt > Me.s15OhTxt Then
        MsgBox "Cannot send more than available", vbOKOnly, "Send Qty More than OH"
        Me.s15SndQtyTxt.Value = Me.s15OhTxt.Value
    ElseIf Me.s15SndQtyTxt < 0 Then
        MsgBox "Cannot send negative", vbOKOnly, "Send Qty less than 0"
        Me.s15SndQtyTxt.Value = Me.s15OhTxt.Value
    End If
End Sub

Tags: None

DoubleD

New Member

Join Date: Jun 2015

Posts: 24
#2

Jun 9 '15, 11:31 AM

I fixed it by converting the text values to numbers using Csng(textbox) in the If statements
Comment

Previous template Next

Textbox validation

Textbox validation

Comment