Form display when no records 1

[arthurbr]

I have a query with 100 records.
Field "nr" is numbered 1 to 100.

When I choose an existing value for nr, the associated fields of the record are displayed on a form.

However I would like to display a message box when a value greater than 100 is chosen. ( there is no record where nr>100).
How can I achieve this ?

( this is a simplified example of the real thing - I may not exclude values >100 with fi a validation rule)

 

[TheAceMan1]

How are ya arthurbr . . . . .

[blue]   Dim Msg As String, Style As Integer, Title As String

   If Me!nr > 100 Then [green]' "100" if string[/green]
      Msg = "Selection must be 100 or less!"
      Style = vbInformation + vbOKOnly
      Title = "Greater than 100 Error!"
      MsgBox Msg, Style, Title
   Else
      [green]'Your Selection Code Here![/green]
   End If[/blue]

See Ya! . . . . . .

 

[arthurbr]

Thx Iceman, and how are you?

I actually oversimplified the problem.
In fact some numbers are missing between 1 and 100 and I would like to know what to do if gthat nr is selected ( I try to implement the condition in a macro but it doesn't work)

Thx 4 ur help

 

[Waud]

You could either use a Select Case procedure
e.g.

Select Case Me!nr
  Case 1 to 10
    'You're Number is between 1 and 10
  Case 50
    'You're number is 50
  Case > 100
    'You're number is greater than 100
  Case Else
    'Does not match any of the above criteria
End Select

or you could add additional criteria to the if statement using the ElseIf keyword.

 

[TheAceMan1]

OK arthurbr . . . . .

Try this (you substitute names in [purple]purple[/purple]):

[blue]   Dim Msg As String, Style As Integer
   Dim Title As String, Criteria As String, DL As String
   
   DL = vbNewLine & vbNewLine
   Criteria = "Between " & Me!Text0 & " And " & Me!Text1
   
   If IsNull(DLookup("[nr]", "[purple][b]TableName[/b][/purple]", Criteria)) Then
      Msg = "Selection does not exist!" & DL & _
            "           0r" & DL & _
            "Selection is greater than 100 . . ."
      Style = vbInformation + vbOKOnly
      Title = "Selection Not Found!"
      MsgBox Msg, Style, Title
   Else
      [green][b]'Your Selection Code Here![/b][/green]
   End If[/blue]

See Ya! . . . . . .

 

[arthurbr]

Thank you Iceman, that helped me out