Combo box - custom validation rule

[JiTS]

Hi,

I have a question about a combo box issue:

Situation:
I have a combo box (Size). You can choose 'Small', 'Medium' or 'Large'. But you are also able to enter a size in numbers.
The datatype of the combo box is text.

Problem:
When I am entering 'aaa' in the combo box, it will be saved.

Question:
How to prevent entering letters and accepting only numbers or the 3 size options?

Own solution until so far:
I need to put code behind the event BeforeUpdate. The code will check if the input is numerical or the 3 size options. I don't know what kind of code I have to write.

Does somebody knows the code for this problem?

 

[Oldsoftboss]

The combobox will have a NotInList event and a limit to list property.

Set the LimitToList to Yes.

I copied the code from one of my Db's

Private Sub Combo32_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Combo32_NotInList

MsgBox " This Job No cannot be found." & Chr(13) & "Be sure to select an existing Job No from the list. ", vbInformation, " Service Operations"

Response = acDataErrContinue
Me.Undo
Me.Combo32 = ""

Exit_Combo32_NotInList:
Exit Sub

Err_Combo32_NotInList:
MsgBox Err.Description, , " Service Operations"
Resume Exit_Combo32_NotInList

End Sub

HTH
Dave

 

[JiTS]

Dave,

Thanks for your reply, but it's not an answer on my question.

It's allowed to choose one of the 3 options or to enter a size for example '52'. But it's not allowed to enter letters, only numbers. (see attachment)

So it has to be something with adding a code behind BeforeUpdate to check if the input is right.
- If the input is ok (numbers) then it will be saved into the table.
- If the input is wrong (not numbers) then you will get a message.

Hopefully my question is more clearly now.

 

[Mile-O]

If IsNumeric(NewData) Then

 

[JiTS]

The solution is almost there, but it's not working yet.

I put the following code behind the event NotInList of the combo box:

Private Sub txtSize_NotInList(NewData As String, Response As Integer)
    If IsNumeric(NewData) Then
    MsgBox "The input is numeric.", vbInformation
    Response = acDataErrContinue
    Exit Sub
    Else
    End If    
End Sub

I think that the code is ok, so I have still some question marks.

Do I miss something?!?

 

[JiTS]

Finally, it works but the code is not that good because I wrote it by myself.

I use the following code:

Private Sub txtDiameter_BeforeUpdate(Cancel As Integer)
    If IsNumeric(Me.txtDiameter) Or Me.txtDiameter = "Extra fijn" Or Me.txtDiameter = "Zeer fijn" Or Me.txtDiameter = "Fijn" Or Me.txtDiameter = " fijn" Or Me.txtDiameter = "Middel fijn" Or Me.txtDiameter = "Grof" Or Me.txtDiameter = "n.v.t." Then
    Exit Sub
    Else
    MsgBox "No alpha input is allowed, only numerical input.", vbExclamation
    Cancel = True
    End If

End Sub

Is it possible to write the 'if line' in a better code condition?

 

[Oldsoftboss]

The only thing I can suggest is that you dont need the Exit Sub line as the if statement will automatically exit if there are no arguments the the line
Dave

 

[Mile-O]

Private Sub txtDiameter_BeforeUpdate(Cancel As Integer)
    Select Case Me.txtDiameter
        Case Is <> "Extra fijn", "Zeer fijn", "Fijn",  _
                        "Middel fijn", "Grof", "n.v.t."
            If Not IsNumeric(Me.txtDiameter) Then
                MsgBox "No alpha input is allowed, only numerical input.", vbExclamation
                 Cancel = True
            End If
    End Select
End Sub

 

[Oldsoftboss]

That will teach me for not reading how long the If line was...
Dave