autonumber with text value

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

Hi there

I would just like to find out whether it is possible to put "text" in an
"autonumber" field.

eg. For each" monthlyreport" i create in my db, the "monthlyreportnumber"
is eg. ABC001, then ABC002, and for another client (CDE TRAding), it would
be CDE001

is this possible?

Thank you

Melissa
 
It's not possible using an AutoNumber field.

You'd have to "roll your own" field.
 
Melissa,

I'd recommend against concatenating two values together like that. Instead,
have a ClientID field to store (ABC, CDE, ...) and a numeric field (RptNum),
and create a composite index which contains both fields and requires that
combined they are unique (no duplicates).

Lets assume your form has a combo box (cbo_Client) and a text box
(txt_RptNum) that is mapped to the RptNum. You could do something along the
lines of the following in the AfterUpdate event of the combo box:

Private Sub cbo_Client_AfterUpdate

strCriteria as string
if me.newrecord then
strCriteria = "[ClientID] = '" & me.cbo_Client & "'"
me.txt_RptNum = NZ(DMAX("RptNum", "yourTable", strCriteria), 0) + 1
endif

Next Sub

When you create a new record, and select the client in the cbo_Client combo
box, Access will automatically generate the next higher value for that
client. The problem with this technique is that if you are developing a
multi-user database, then two users could attempt to create records for the
same client at the same time, and they could both end up with the same
RptNum. If you create the index mentioned above, then when the first user
saves their record, they will get no error, but when the second user tries
to save their record, they will get an error indicating that the action
would create duplicates. You could then trap for this error in your error
handler and recompute the RptNum for the second record.

There are other ways to do this as well, one would be to maintain a table
that tracks the NextNum for each of your clients. In this scenario, you
would create a table (tblClientNum) which contains just two fields
(ClientID, NextNum). You would then create a function that retrieves the
value from the NextNum field and then increments it. Because this would be
an almost instantaneous process, you would not have to worry about
duplicates in the RptNum field for the same client. The function might look
like:

Public Function fnNextNum(ClientID as String) as long

Dim rs as DAO.Recordset

set rs = Currentdb.Openrecordset("tblClientNum",,dbfailonerror)
rs.findfirst "[ClientID] = '" & ClientID & "'"
if rs.nomatch then
rs.AddNew
rs!ClientID = ClientID
rs!NextNum = 2
rs.Update
fnNextNum = 1
else
rs.edit
fnNextNum = rs!NextNum
rs!NextNum = rs!NextNum + 1
rs.Update
end if

End Sub

HTH
Dale
 
Jeff.

Thank you kindly to everyone for their help...firstly.

The reason for this is i am creating a db for a security company, who
currently has a "system" in place for the numbering of monthly reports. eg.
ABC Trading received a monthly report named "ABC008" last month, therefore
once the db is in place, it will need to create "ABC009" for next month.

The person im creating the db for does not want to change the way he has
been numbering his monthly reports.

Kind Regards

Melissa
 
Melissa

Why do you want to do this? I understand "what", but why? What will having
the combination allow you to do?

(If I can read between the lines of your example, you are trying to come up
with a way of combining company name/initials with month number in a single
field. This is not a good idea for several reasons. First, what if more
than one company has the same initials, second, what will you do if you
generate more than one report for a company during a month, and third, what
will you do NEXT March? Instead, consider storing a sequence number - 'roll
your own', as Doug points out - and a date/time value. From these, plus the
company name/initials, you can use a query to generate the concatenated
string that you appear to be trying to come up with. And fourth, storing
more than one fact in a single field is poor database design...)

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Melissa said:
Jeff.

Thank you kindly to everyone for their help...firstly.

The reason for this is i am creating a db for a security company, who
currently has a "system" in place for the numbering of monthly reports.
eg.
ABC Trading received a monthly report named "ABC008" last month, therefore
once the db is in place, it will need to create "ABC009" for next month.
Click to expand...

So you need two fields, one for the lettering and the other for the numbers.
You can use the DMax function to find the next available number (using the
"ABC" part as search criteria) and concatenate the fields at run-time.

Keith.
www.keithwilby.co.uk
 
There is no need to change the numbering that the user sees. Just as you
can store FirstName and LastName separately, but combine them to display as
the full name, you can store the company code and the number separately, and
combine them as needed. Dale described a way to increment the number, and
added some options for guarding against duplicate numbers in a multi-user
environment. His first option is probably the simplest to implement.
Another option in a multi-user environment is not to add the number until
the form's Before Update event, if the users are OK with that. You still
should add additional error handling in the form's Error event, but first
give it a try with a single user.

By the way, one of the requirements, or at least one of the
highly-recommended things, is that you store the customer code along with
other customer information in the Customer table. (This assumes you have a
separate customer table. If you don't, you need one). The combo box from
which you select the customer name (assuming that is how you do it) can
include the customer code in the Row Source. You can hide the column, but
reference it as needed.
 
Back
Top