A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Trying to get a field to generate contents automatically on a form



 
 
Thread Tools Display Modes
  #1  
Old June 26th, 2009, 12:33 AM posted to microsoft.public.access.tablesdbdesign
NDBC
external usenet poster
 
Posts: 110
Default Trying to get a field to generate contents automatically on a form

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
  #2  
Old June 26th, 2009, 01:25 AM posted to microsoft.public.access.tablesdbdesign
Steve[_72_]
external usenet poster
 
Posts: 190
Default Trying to get a field to generate contents automatically on a form

All classes rider numbers are stored in the one table at this stage.

DON't change that; you have done that correctly!!

To get the next largest rider number, you need to look in your table for
only the class in question and then find the highest recorded rider number.
You can do this with the DMax function in Access which you can look in the
Help file for.

NextRiderNumber =
DMax("[NameOfRiderNumberField]","NameOfYourTable","[NameOfClassField] = '" &
Forms!NameOfYourForm & "'"") + 1

Note 1 Three single quotes after the equal sign
Note 2 Three single quotes and a double quote after the & sign at the
end.

Steve



"NDBC" wrote in message
...

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



  #3  
Old June 26th, 2009, 09:42 AM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Trying to get a field to generate contents automatically on a form

"NDBC" wrote in message
...

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


I'm assuming that you're using a form to manipulate your data (as you should
be!).

I'm also assuming that you have a text box called "txtUniqueNo" bound to a
field "UniqueNo" in table "tblEnduro" and your option group is bound to a
number field "Class" and is called "ogrClass" and that the option values are
100 (a), 200 (b) etc. Something like this should work in the form's Before
Update event (untested):

Me.txtUniqueNo = Nz(DMax("UniqueNo", "tblEnduro", "[Class] = " &
Me.ogrClass)+1,Me.ogrClass)

The "Nz" handles the case where there is no number for the selected class,
in which case it will take the value assigned to the selected option.

If your Class field is a text field then you'll need to use quotation marks:

Me.txtUniqueNo = Nz(DMax("UniqueNo", "tblEnduro", "[Class] = ' " &
Me.ogrClass & " ' ")+1,Me.ogrClass)

I've included extra spaces around the single quotes for clarity, you should
not include them in your code. The number will be assigned when the record
has been saved.

Hope that makes sense.

Keith.
www.keithwilby.co.uk

  #4  
Old June 26th, 2009, 11:51 AM posted to microsoft.public.access.tablesdbdesign
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default Trying to get a field to generate contents automatically on aform

hi,

NDBC wrote:
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.

You can use DLookup(), e.g. in the form's Before Update event

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me![RiderNumber] = DLookup("[Number]", "yourQueriesName")

End Sub




mfG
-- stefan --
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.