Tables/Form

[Jenn Civello]

Yes fiels 3 is the autonumber field. Ok I will try that. Thanks for your
help.

Assuming that Field3 is the Autonumber field, you may need a hidden text
box on the form bound to Field3.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I looked at the control souce, it is bound to the field in my query
that I created with my formula. MyField: Field1 & Field2 & "-" & Field3

Look at the ControlSource property for the text box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm not sure, how can I tell?

message 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?

in 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

 

[Jenn Civello]

I put in field 3 as a hidden text box, but when I enter the next record it
is still not automatically putting in the next autonumber. Any ideas?

Yes fiels 3 is the autonumber field. Ok I will try that. Thanks for your
help.

Assuming that Field3 is the Autonumber field, you may need a hidden text
box on the form bound to Field3.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When I looked at the control souce, it is bound to the field in my query
that I created with my formula. MyField: Field1 & Field2 & "-" & Field3

Look at the ControlSource property for the text box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm not sure, how can I tell?

message 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?

in 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

 

[Douglas J. Steele]

Sorry, no further ideas.

Since this thread has already gone on for some time, it's likely few others
will be looking at it.

I'd suggest making a new post, summarizing what's already been done, in the
hopes that someone new might have some suggestions.

Sorry I couldn't be of more help.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I put in field 3 as a hidden text box, but when I enter the next record it
is still not automatically putting in the next autonumber. Any ideas?

Yes fiels 3 is the autonumber field. Ok I will try that. Thanks for
your help.

Assuming that Field3 is the Autonumber field, you may need a hidden text
box on the form bound to Field3.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I looked at the control souce, it is bound to the field in my
query that I created with my formula. MyField: Field1 & Field2 & "-" &
Field3

message Look at the ControlSource property for the text box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm not sure, how can I tell?

message 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.

in 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?

in 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