Trying to get a field to automatically generate in a form

[NDBC]

I am trying to use access 2003 to record rider details and keep lap times at
a local enduro race. I am trying to input riders details. There are a few
classes of competitors, a grade (rider number 100-199), b grade (rider number
200-299) etc. I have set up an optiion group for rider class so if a grade is
ticked it comes back with the response 100 and 200 for b grade etc. What I
need in the rider number box is the next available rider number to be
generated automatically. For instance if the a grade option is ticked and the
largest rider number in the 100's already stored in the table is 103 then it
automatically comes up with 104 as the rider number. All classes rider
numbers are stored in the one table at this stage. Does it make it easier if
I have a table for each class. I don't know how to make a form look at
multiple tables.

I have developed a query that can determine the highest number used in any
class based on an input of (100, 200 etc) but don't know how to use a query
from a form if this is an option.

I have a very limited experience with access and vba so may need very
detailed help if possible but any advice will be gladly accepted.

Thanks

 

[hasek.josef]

I am trying to use access 2003 to record rider details and keep lap times
at
a local enduro race. I am trying to input riders details. There are a few
classes of competitors, a grade (rider number 100-199), b grade (rider
number
200-299) etc. I have set up an optiion group for rider class so if a grade
is
ticked it comes back with the response 100 and 200 for b grade etc. What I
need in the rider number box is the next available rider number to be
generated automatically. For instance if the a grade option is ticked and
the
largest rider number in the 100's already stored in the table is 103 then
it
automatically comes up with 104 as the rider number. All classes rider
numbers are stored in the one table at this stage. Does it make it easier
if
I have a table for each class. I don't know how to make a form look at
multiple tables.

I have developed a query that can determine the highest number used in any
class based on an input of (100, 200 etc) but don't know how to use a
query
from a form if this is an option.

I have a very limited experience with access and vba so may need very
detailed help if possible but any advice will be gladly accepted.

Thanks

 

[Arvin Meyer MVP]

Use an expression like:

DMax("[FieldName]", "TableName") + 1

No, you don't want multiple tables, If there are only 4 classes you could
get away with 4 fields but it is better to store the data using 2 fields:

RiderNumber
RiderClass

Then the expression would look like:

DMax("[RiderNumber]", "TableName", "RiderClass=" & Me.OptionGroupName) + 1