Define limits for integer

[mike_dowler]

Hi - I would appreciate some help please. I would like to be be able to declare a variable as an integer, and specify that the integer should have a value only between 1 and 6. Ideally, any value outside that range would produce an error condition, which I could then use to trigger another event.

The reason, if you're interested (& in case you can think of an alternative solution) is that I have 6 text boxes on a form, which are populated in order with messages that apply (i.e. TextBox1 to TextBox6) by using Me("TextBox" & n) where n is incremented by 1 each time. Sometimes there might be more than 6 relevant messages, but I want to avoid the program looking for TextBox7 (as it doesn't exist).

Any ideas please?

Mike

 

[Pat Hartman]

Use >=1 And <=6 as the Validation rule in the table.

This is not a good solution however since you have a 1-many relationship that you have implemented as a repeating group. Do some reading about normalization and data base design. Once you have more than one of something, you have many and that translates to a 1-many relationship even when many is fixed at some small number.

You will find that even though on the surface it looks more complicated to set up a separate table and subform, it turns out to be much simpler since it simplifies queries and eliminated the need to write VBA to work with each field.

 

[mike_dowler]

Re: define limits for integer

Sorry - I obviously didnn't explain myself too well. This isn't a value in a table, but a variable in VBA. The sub starts with n = 1, and increments by 1 every time it writes a value to one of the text boxes (so that each message is written to a new text box). However, I was wondering if there is a way to limit n to a mximum value of 6, without including an if routine every time the value of n is changed.

 

[Mile-O]

If you only require a value between 1 and 6 then don't use an Integer - use a Byte.

 

[ChrisO]

Haven’t got a clue, just a guess as to the requirements: -

Option Explicit
Option Compare Text


Private Function IncrementNumber(ByVal lngMax As Long) As Long
    Static lngValue As Long
    
    lngValue = lngValue Mod lngMax + 1

    IncrementNumber = lngValue

End Function

Now I would go with Pat on this one unless it is an academic question.

When I see questions that require things done in certain ways, in this case “without including an if routine every time the value of n is changed”, it sounds alarm bells in my head.

If it is not an academic question then I apologize but if it is then I would like a word with the person that placed the restriction on the methodology.

In any case the Mod operator should be able to do what you want with or without the variable lngValue being Static... we can do that another way.

Hope that helps.

Regards,
Chris.