I'm not sure, how can I tell?
Is the textbox on the form bound to the autonumber field?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
We I enter the information into the table, it gives the next
autonumber, but it doesn't work when I do it in the form. Am I doing
something wrong?
message That doesn't sound right. Autonumber fields get new values even if
you don't use them.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
It is an autonumber field, but when I go to the next record it
doesn't populate.
message It's the 3rd field (what Jeff had as [YourSequenceNumberField])
that needs to be incremented.
I think Jeff was assuming that would be an Autonumber field, so
that it would increment itself. (Of course, it won't reset itself
to 0 when the year changes)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Ok, Thanks. That work. But now I have one last thing to do.
I want to create a form so that I can use this to enter all of my
information, but I want the Field that we just setup the
expression for, to automatically generate the next number every
time we add a new record. How do I do this?
When I created my form it does not populate this field with the
next auto number.
message If you've got 3 fields, you can get away with:
MyField: Field1 & Field2 & "-" & Field3
(BTW, if you've got a field named Year, you should rename it.
Year is a reserved word, and using it for your own purposes can
cause problems.)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I have all 3 fields as "text" fields. Should the year field be a
"date" field?
message In a blank column in the "Field" row of the grid.
Note that if you type exactly that, Access will assign a field
name of something like Expr1 to the new field. To give it a
more meaningful name, you can put a name (followed by a colon)
in front of the concatenation:
MyField: "ITCC" & Format(Date(),yy) & "-" &
[YourSequenceNumberField]
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Jeff,
Where would I type "ITCC" & Format(Date(),yy) & "-" &
[YourSequenceNumberField] in the query? I have set up a field
for each fact.
E.g. ITCC is one field, 07 (the year) is another field and 001
is the third field.
Jenn
<scratch, scratch!> I could have sworn there was information
at mvps.org, but I don't find it there either.
The basic concept is that you will use DMax() to find the
maximum value currently used in your [SequenceNumber] field,
add one to it, and use THAT for your new sequence number in
your new record.
If you want to have a "number" that looks like "ITCC07-001",
concatenate "ITCC" & Format(Date(),yy) & "-" &
[YourSequenceNumberField] in a query (note that this is
untested aircode). Use that value from the query in your
forms and reports.
Regards
Jeff Boyce
Microsoft Office/Access MVP
I can't seem to find any articles on "custom auto numbers",
can you please tell me where I should be looking?
Jean
Take a look at
www.mvps.org/access for "Custom Autonumbers"
for an example of how to do that.
By the way, I hope you aren't saying you have that entire
string of characters in one field. From your description,
it consists of at least two separate values (year, sequence
#), and I suspect at least one or two more. You need one
field for each fact, and can easily use a query to
concatenate the field values together for display in a
form. If you try putting them all together in one field,
you (and Access) will have to work a lot harder.
Regards
Jeff Boyce
Microsoft Office/Access MVP
I have a table that I have consecutive numbers in and I
would like to setup a form that will automatically enter
the next number each time. E.G. ITCC07-001, once this
number is used I would like it to automatically go to
ITCC07-002. But I need to be able to stop this at the end
of the year. Can you please let me know if this can be
done.
Thanks
