Visual 'reminder' in a form

[Lee Grant]

Hi there,

I was wondering if someone may have solution or some pointers for me.

I've built a database for a running event and I need to find a way to ensure
that new entrants are given a unique race number. Each runner's unique race
number is the one that ends up on the bib that the runner wears on raceday.

In a related table is a field, EventID, and for this years race it is set to
2009 (last year was 2008, etc.)

So when I enter a new entrant to this years race (with EventID = 2009) I
need to ensure that the race number they get is unique, and is also the next
in sequence from the previous entrant that also had an EventID of 2009.

Is there a nice simple (I'm really new to access!) way to do this with a
form?

I hope someone may be able to point me in the correct direction,

Kindest Regards,

Lee

 

[BruceM]

It can be done, but it is difficult to suggest how without knowing something
of the database structure. You have mentioned "a related table", but
related to what exactly, and by what field?

I will assume you have something like this:

tblRace
RaceID (primary key, or PK)
RaceDate
Other fields specific to the race event

tblRunner
RunnerID (PK)
RaceID (number field)
BibNumber (number field)
FirstName
LastName
etc.

If RaceID in tblRace is a number field, make RaceID in tblRunner the same
type of number field. If RaceID is an autonumber in tblRace it needs to be
Long Integer in tblRunner. If you already have the race date there is no
need for RaceID to have any meaning. There is no harm to typing in "2009",
but no help either.
Create a one-to-many relationship between the two RaceID fields. Make a
form based on tblRace, with a subform based on tblRunner. The linking field
for the subform control is RaceID. In the DefaultValue for the text box
bound to BibNumber (give it a name such as txtBib):
=DMax("[BibNumber]","[tblRunner]","[RaceID] = " & [RaceID]) + 1

It would probably be best to have a list of Runners to start with,
particularly if you are going to track information from year to year or you
will assemble a list of runners before you start assigning bib numbers. In
that case you need three tables:

tblRace
RaceID (primary key, or PK)
RaceDate
Other fields specific to the race event

tblRunner
RunnerID (PK)
FirstName
LastName
Other information specific to the runner

tblRunnerRace
RunnerRaceID (PK)
RaceID
RunnerID
BibNumber

Create a relationship between the two RaceID fields, and another between the
twoRunnerID fields. Make a form based on tblRunner to record runner
information. Make a form based on tblRace, and a subform based on
tblRunnerRace. The linking field is RaceID. Bind a combo box to RunnerID
on the subform. Use tblRunner as its Row Source (the combo box wizard can
help with this). The BibNumber DefaultValue will be incremented the same as
above.

This is just a sketch, but I can provide more details if you like.

 

[Lee Grant]

Hi Bruce,

Thanks for the reply - I'll try and work through your suggestions. The
actual table structure is this:

Table 1 - Runner Information

RunnerID (PK)
firstname
surname
dob
sex
address1
address2
address3
town/city
county
landline
mobile
email

Table 2 - Race_Details

Race_Details_ID (PK)
EventID
racenumber
dateofentry
lap1
lap2
total
status
source
tshirtsize
RunnerID
Fee
paymenttype
chequenumber
cashed_batch
ssaeenc
emailsent
informationposted
phonecallmade

Table 3 - Event

EventID (PK)
dateofevent
nameofevent

So the value I need to be unique is Race_Details.racenumber when
Event.EventID=2009

I'll let you know how I get on!

Thanks for the help!

It can be done, but it is difficult to suggest how without knowing
something of the database structure. You have mentioned "a related
table", but related to what exactly, and by what field?

I will assume you have something like this:

tblRace
RaceID (primary key, or PK)
RaceDate
Other fields specific to the race event

tblRunner
RunnerID (PK)
RaceID (number field)
BibNumber (number field)
FirstName
LastName
etc.

If RaceID in tblRace is a number field, make RaceID in tblRunner the same
type of number field. If RaceID is an autonumber in tblRace it needs to
be Long Integer in tblRunner. If you already have the race date there is
no need for RaceID to have any meaning. There is no harm to typing in
"2009", but no help either.
Create a one-to-many relationship between the two RaceID fields. Make a
form based on tblRace, with a subform based on tblRunner. The linking
field for the subform control is RaceID. In the DefaultValue for the text
box bound to BibNumber (give it a name such as txtBib):
=DMax("[BibNumber]","[tblRunner]","[RaceID] = " & [RaceID]) + 1

It would probably be best to have a list of Runners to start with,
particularly if you are going to track information from year to year or
you will assemble a list of runners before you start assigning bib
numbers. In that case you need three tables:

tblRace
RaceID (primary key, or PK)
RaceDate
Other fields specific to the race event

tblRunner
RunnerID (PK)
FirstName
LastName
Other information specific to the runner

tblRunnerRace
RunnerRaceID (PK)
RaceID
RunnerID
BibNumber

Create a relationship between the two RaceID fields, and another between
the twoRunnerID fields. Make a form based on tblRunner to record runner
information. Make a form based on tblRace, and a subform based on
tblRunnerRace. The linking field is RaceID. Bind a combo box to RunnerID
on the subform. Use tblRunner as its Row Source (the combo box wizard can
help with this). The BibNumber DefaultValue will be incremented the same
as above.

This is just a sketch, but I can provide more details if you like.

Hi there,

I was wondering if someone may have solution or some pointers for me.

I've built a database for a running event and I need to find a way to
ensure that new entrants are given a unique race number. Each runner's
unique race number is the one that ends up on the bib that the runner
wears on raceday.

In a related table is a field, EventID, and for this years race it is set
to 2009 (last year was 2008, etc.)

So when I enter a new entrant to this years race (with EventID = 2009) I
need to ensure that the race number they get is unique, and is also the
next in sequence from the previous entrant that also had an EventID of
2009.

Is there a nice simple (I'm really new to access!) way to do this with a
form?

I hope someone may be able to point me in the correct direction,

Kindest Regards,

Lee

 

[BruceM]

It looks to me as if RaceDetails is a junction table, and the design in
general looks pretty good from what I can tell. Is every race two laps, and
will every future race be two laps? If not, think about placing such
information in a related table. In any case, calculate the total as needed
(if that is what "total" represents) rather than storing the calculation
result. You may want to give yourself some more flexibility with phone
numbers by storing them in a related table, in records that include the type
(home, cell, work, etc.).

You spoke about assigning BibNumber automatically, but I don't see that
field in there. If it has another name, plug it into the suggestion I made
(assuming you are using a form/subform setup as the user interface). Here
is the expression again, with RaceDetails as the name of the table. I am
assuming BibNumber as a field in RaceDetails.
=DMax("[BibNumber]","[RaceDetails]","[EventID] = " & [EventID]) + 1
This is saying to find the largest value in the RaceDetails table, BibNumber
field in a record that has the same EventID as the current record, and to
add one to that value. Actually, the expression should allow for the first
BibNumber to be assigned for an Event, before which there are no BibNumbers,
so the expression returns Null. Use Nz to substitute a value for Null in
that case:
=Nz(DMax("[BibNumber]","[RaceDetails]","[EventID] = " & [EventID]),0) + 1

Hi Bruce,

Thanks for the reply - I'll try and work through your suggestions. The
actual table structure is this:

Table 1 - Runner Information

RunnerID (PK)
firstname
surname
dob
sex
address1
address2
address3
town/city
county
landline
mobile
email

Table 2 - Race_Details

Race_Details_ID (PK)
EventID
racenumber
dateofentry
lap1
lap2
total
status
source
tshirtsize
RunnerID
Fee
paymenttype
chequenumber
cashed_batch
ssaeenc
emailsent
informationposted
phonecallmade

Table 3 - Event

EventID (PK)
dateofevent
nameofevent

So the value I need to be unique is Race_Details.racenumber when
Event.EventID=2009

I'll let you know how I get on!

Thanks for the help!

It can be done, but it is difficult to suggest how without knowing
something of the database structure. You have mentioned "a related
table", but related to what exactly, and by what field?

I will assume you have something like this:

tblRace
RaceID (primary key, or PK)
RaceDate
Other fields specific to the race event

tblRunner
RunnerID (PK)
RaceID (number field)
BibNumber (number field)
FirstName
LastName
etc.

If RaceID in tblRace is a number field, make RaceID in tblRunner the same
type of number field. If RaceID is an autonumber in tblRace it needs to
be Long Integer in tblRunner. If you already have the race date there is
no need for RaceID to have any meaning. There is no harm to typing in
"2009", but no help either.
Create a one-to-many relationship between the two RaceID fields. Make a
form based on tblRace, with a subform based on tblRunner. The linking
field for the subform control is RaceID. In the DefaultValue for the
text box bound to BibNumber (give it a name such as txtBib):
=DMax("[BibNumber]","[tblRunner]","[RaceID] = " & [RaceID]) + 1

It would probably be best to have a list of Runners to start with,
particularly if you are going to track information from year to year or
you will assemble a list of runners before you start assigning bib
numbers. In that case you need three tables:

tblRace
RaceID (primary key, or PK)
RaceDate
Other fields specific to the race event

tblRunner
RunnerID (PK)
FirstName
LastName
Other information specific to the runner

tblRunnerRace
RunnerRaceID (PK)
RaceID
RunnerID
BibNumber

Create a relationship between the two RaceID fields, and another between
the twoRunnerID fields. Make a form based on tblRunner to record runner
information. Make a form based on tblRace, and a subform based on
tblRunnerRace. The linking field is RaceID. Bind a combo box to
RunnerID on the subform. Use tblRunner as its Row Source (the combo box
wizard can help with this). The BibNumber DefaultValue will be
incremented the same as above.

This is just a sketch, but I can provide more details if you like.

Hi there,

I was wondering if someone may have solution or some pointers for me.

I've built a database for a running event and I need to find a way to
ensure that new entrants are given a unique race number. Each runner's
unique race number is the one that ends up on the bib that the runner
wears on raceday.

In a related table is a field, EventID, and for this years race it is
set to 2009 (last year was 2008, etc.)

So when I enter a new entrant to this years race (with EventID = 2009) I
need to ensure that the race number they get is unique, and is also the
next in sequence from the previous entrant that also had an EventID of
2009.

Is there a nice simple (I'm really new to access!) way to do this with a
form?

I hope someone may be able to point me in the correct direction,

Kindest Regards,

Lee

 

[Lee Grant]

Hi Bruce,

Sorry about the delay in getting back to you. I'm delighted to report that
I've got the solution to work.

Thanks so much for the help - it has really helped with the management of
the database.

Kindest Regards,


Lee

It looks to me as if RaceDetails is a junction table, and the design in
general looks pretty good from what I can tell. Is every race two laps,
and will every future race be two laps? If not, think about placing such
information in a related table. In any case, calculate the total as
needed (if that is what "total" represents) rather than storing the
calculation result. You may want to give yourself some more flexibility
with phone numbers by storing them in a related table, in records that
include the type (home, cell, work, etc.).

You spoke about assigning BibNumber automatically, but I don't see that
field in there. If it has another name, plug it into the suggestion I
made (assuming you are using a form/subform setup as the user interface).
Here is the expression again, with RaceDetails as the name of the table.
I am assuming BibNumber as a field in RaceDetails.
=DMax("[BibNumber]","[RaceDetails]","[EventID] = " & [EventID]) + 1
This is saying to find the largest value in the RaceDetails table,
BibNumber field in a record that has the same EventID as the current
record, and to add one to that value. Actually, the expression should
allow for the first BibNumber to be assigned for an Event, before which
there are no BibNumbers, so the expression returns Null. Use Nz to
substitute a value for Null in that case:
=Nz(DMax("[BibNumber]","[RaceDetails]","[EventID] = " & [EventID]),0) + 1

Hi Bruce,

Thanks for the reply - I'll try and work through your suggestions. The
actual table structure is this:

Table 1 - Runner Information

RunnerID (PK)
firstname
surname
dob
sex
address1
address2
address3
town/city
county
landline
mobile
email

Table 2 - Race_Details

Race_Details_ID (PK)
EventID
racenumber
dateofentry
lap1
lap2
total
status
source
tshirtsize
RunnerID
Fee
paymenttype
chequenumber
cashed_batch
ssaeenc
emailsent
informationposted
phonecallmade

Table 3 - Event

EventID (PK)
dateofevent
nameofevent

So the value I need to be unique is Race_Details.racenumber when
Event.EventID=2009

I'll let you know how I get on!

Thanks for the help!

It can be done, but it is difficult to suggest how without knowing
something of the database structure. You have mentioned "a related
table", but related to what exactly, and by what field?

I will assume you have something like this:

tblRace
RaceID (primary key, or PK)
RaceDate
Other fields specific to the race event

tblRunner
RunnerID (PK)
RaceID (number field)
BibNumber (number field)
FirstName
LastName
etc.

If RaceID in tblRace is a number field, make RaceID in tblRunner the
same type of number field. If RaceID is an autonumber in tblRace it
needs to be Long Integer in tblRunner. If you already have the race
date there is no need for RaceID to have any meaning. There is no harm
to typing in "2009", but no help either.
Create a one-to-many relationship between the two RaceID fields. Make a
form based on tblRace, with a subform based on tblRunner. The linking
field for the subform control is RaceID. In the DefaultValue for the
text box bound to BibNumber (give it a name such as txtBib):
=DMax("[BibNumber]","[tblRunner]","[RaceID] = " & [RaceID]) + 1

It would probably be best to have a list of Runners to start with,
particularly if you are going to track information from year to year or
you will assemble a list of runners before you start assigning bib
numbers. In that case you need three tables:

tblRace
RaceID (primary key, or PK)
RaceDate
Other fields specific to the race event

tblRunner
RunnerID (PK)
FirstName
LastName
Other information specific to the runner

tblRunnerRace
RunnerRaceID (PK)
RaceID
RunnerID
BibNumber

Create a relationship between the two RaceID fields, and another between
the twoRunnerID fields. Make a form based on tblRunner to record runner
information. Make a form based on tblRace, and a subform based on
tblRunnerRace. The linking field is RaceID. Bind a combo box to
RunnerID on the subform. Use tblRunner as its Row Source (the combo box
wizard can help with this). The BibNumber DefaultValue will be
incremented the same as above.

This is just a sketch, but I can provide more details if you like.

Hi there,

I was wondering if someone may have solution or some pointers for me.

I've built a database for a running event and I need to find a way to
ensure that new entrants are given a unique race number. Each runner's
unique race number is the one that ends up on the bib that the runner
wears on raceday.

In a related table is a field, EventID, and for this years race it is
set to 2009 (last year was 2008, etc.)

So when I enter a new entrant to this years race (with EventID = 2009)
I need to ensure that the race number they get is unique, and is also
the next in sequence from the previous entrant that also had an EventID
of 2009.

Is there a nice simple (I'm really new to access!) way to do this with
a form?

I hope someone may be able to point me in the correct direction,

Kindest Regards,

Lee

 

[BruceM]

You're welcome. I'm glad to pass along some of what I have learned here.

Hi Bruce,

Sorry about the delay in getting back to you. I'm delighted to report
that I've got the solution to work.

Thanks so much for the help - it has really helped with the management of
the database.

Kindest Regards,


Lee

It looks to me as if RaceDetails is a junction table, and the design in
general looks pretty good from what I can tell. Is every race two laps,
and will every future race be two laps? If not, think about placing such
information in a related table. In any case, calculate the total as
needed (if that is what "total" represents) rather than storing the
calculation result. You may want to give yourself some more flexibility
with phone numbers by storing them in a related table, in records that
include the type (home, cell, work, etc.).

You spoke about assigning BibNumber automatically, but I don't see that
field in there. If it has another name, plug it into the suggestion I
made (assuming you are using a form/subform setup as the user interface).
Here is the expression again, with RaceDetails as the name of the table.
I am assuming BibNumber as a field in RaceDetails.
=DMax("[BibNumber]","[RaceDetails]","[EventID] = " & [EventID]) + 1
This is saying to find the largest value in the RaceDetails table,
BibNumber field in a record that has the same EventID as the current
record, and to add one to that value. Actually, the expression should
allow for the first BibNumber to be assigned for an Event, before which
there are no BibNumbers, so the expression returns Null. Use Nz to
substitute a value for Null in that case:
=Nz(DMax("[BibNumber]","[RaceDetails]","[EventID] = " & [EventID]),0) + 1

Hi Bruce,

Thanks for the reply - I'll try and work through your suggestions. The
actual table structure is this:

Table 1 - Runner Information

RunnerID (PK)
firstname
surname
dob
sex
address1
address2
address3
town/city
county
landline
mobile
email

Table 2 - Race_Details

Race_Details_ID (PK)
EventID
racenumber
dateofentry
lap1
lap2
total
status
source
tshirtsize
RunnerID
Fee
paymenttype
chequenumber
cashed_batch
ssaeenc
emailsent
informationposted
phonecallmade

Table 3 - Event

EventID (PK)
dateofevent
nameofevent

So the value I need to be unique is Race_Details.racenumber when
Event.EventID=2009

I'll let you know how I get on!

Thanks for the help!

"BruceM" <bamoob_at_yawhodotcalm.not> wrote in message
It can be done, but it is difficult to suggest how without knowing
something of the database structure. You have mentioned "a related
table", but related to what exactly, and by what field?

I will assume you have something like this:

tblRace
RaceID (primary key, or PK)
RaceDate
Other fields specific to the race event

tblRunner
RunnerID (PK)
RaceID (number field)
BibNumber (number field)
FirstName
LastName
etc.

If RaceID in tblRace is a number field, make RaceID in tblRunner the
same type of number field. If RaceID is an autonumber in tblRace it
needs to be Long Integer in tblRunner. If you already have the race
date there is no need for RaceID to have any meaning. There is no harm
to typing in "2009", but no help either.
Create a one-to-many relationship between the two RaceID fields. Make
a form based on tblRace, with a subform based on tblRunner. The
linking field for the subform control is RaceID. In the DefaultValue
for the text box bound to BibNumber (give it a name such as txtBib):
=DMax("[BibNumber]","[tblRunner]","[RaceID] = " & [RaceID]) + 1

It would probably be best to have a list of Runners to start with,
particularly if you are going to track information from year to year or
you will assemble a list of runners before you start assigning bib
numbers. In that case you need three tables:

tblRace
RaceID (primary key, or PK)
RaceDate
Other fields specific to the race event

tblRunner
RunnerID (PK)
FirstName
LastName
Other information specific to the runner

tblRunnerRace
RunnerRaceID (PK)
RaceID
RunnerID
BibNumber

Create a relationship between the two RaceID fields, and another
between the twoRunnerID fields. Make a form based on tblRunner to
record runner information. Make a form based on tblRace, and a subform
based on tblRunnerRace. The linking field is RaceID. Bind a combo box
to RunnerID on the subform. Use tblRunner as its Row Source (the combo
box wizard can help with this). The BibNumber DefaultValue will be
incremented the same as above.

This is just a sketch, but I can provide more details if you like.

Hi there,

I was wondering if someone may have solution or some pointers for me.

I've built a database for a running event and I need to find a way to
ensure that new entrants are given a unique race number. Each
runner's unique race number is the one that ends up on the bib that
the runner wears on raceday.

In a related table is a field, EventID, and for this years race it is
set to 2009 (last year was 2008, etc.)

So when I enter a new entrant to this years race (with EventID = 2009)
I need to ensure that the race number they get is unique, and is also
the next in sequence from the previous entrant that also had an
EventID of 2009.

Is there a nice simple (I'm really new to access!) way to do this with
a form?

I hope someone may be able to point me in the correct direction,

Kindest Regards,

Lee