I need help on how to make the form generate the number automatically

[cadmaster]

Hi,
I have a form. There is a field name ECO Number.
I want this field to generate the alpha number such as ECO-0001, ECO-0002 and so on when I lick on a button to create a new record. Could you please help me out?
Thanks

[zmbd]

The easest is to simply use the autonumber field type in the table. The "ECO-" would be added at runtime either in the query, form, or report.

Now, I wouldn't use this if you need to be assured of a sequential number, in which case, one can use vba and a simple agregate query to return the maximum of the number within a field and simply add one.

If you will search here on Bytes.com, there are several Q&A's with this information available for various situations.

[ADezii]

Basic Assumptions:

1. Your Table name is tblECO and in this Table is a Field named [ECO Number] - {TEXT 8}.
2. [ECO-Number] must be sequential in nature, cannot be NULL (Required = Yes), is the Primary Key or is a Unique Index in the Table, and is of the exact Format of ECO-XXXX.
3. The Field ([ECO Number]) is already seeded, meaning that at least a single Value of the Format ECO-XXXX exists in it.
4. The following Public Function will generate the next Unique, Sequential ECO Number for you.
   

   Public Function fRetSeqECONumber()
   Dim strLastECONum As String
   Dim strNextECONum As String
   
   strLastECONum = DLast("[ECO Number]", "tblECO")
   strNextECONum = "ECO-" & Format$(Val(Mid$(strLastECONum, 5)) + 1, "0000")
   
   fRetSeqECONumber = strNextECONum
   End Function

5. Assuming ECO-0099 is the last ECO Number in tblEco, executing fRetSeqECONumbe r() will output
   

   ECO-0100

6. Any questions, feel free to ask.

[cadmaster]

ADezii,
Thank you for your help.
I am totally new at MS Access.
With the provided information, where should I enter them?
Thanks

[ADezii]

Assuming you are entering Data from a Form, using the previous scenario:

1. Set the Record Source of the Form to tblECO.
2. Create a Text Box and set its Control Source to ECO Number.
3. Set the Default Value of the same Text Box to =fRetSeqECONumb er().
4. The existing ECO Numbers will be displayed, but when you add a New Record fRetSeqECONumbe r() will be executed and this Value placed in the Text Box.

[cadmaster]

Where is the Record Source? I can't see it.

Thanks
Can I email you the file so you can help me out? Thanks

[zmbd]

what I was trying to do was get more detail about how the number would be used before offering any code.

[cadmaster]

Hi zmbd,
I created a table and name it as ECO Table. In this ECO Table, there is a field called ECO Number.

I also created a form, which I use to enter all the data. I named it as ECO Form.

The ECO Number will be on this ECO Form. I need the ECO Number to automatically generate the number when there is new record created.

The ECO Number should be like ECO-001, ECO-002... and so on

(If you can, I would like the ECO to generate the ECO number like ECO2013-001, ECO2013-002 and so on.. (2013 is the year. I would like the ECO Number to generate its number from the beginning again with the new year number such as ECO2014-001 and so on)
If you can help me out, I really appreciate for your help a lot.
thanks

ps. I use this =Nz(DMax("[ECO Number]","ECO Table"))+1
to generate the ECO number and I worked but I am not satisfied with it because it starts with the 1 first..
for example, whenever I click on the new record, it gives me 1,then 2 then 3....
If I would like it to give me the ECO-001 (if with the year it would be great) Can someone kindly help me out?
thank you

[zmbd]

That's not so bad.
ADezii's code can be modified to include the year.
in the table: increase the [ECO number]{text(12)}

In the code:
Insert at line 4:

Dim strYear as String

Insert after this but before line 5:

strYear = year(date())

Line 6: change to read:

strNextECONum = "ECO" & strYear & "-" & Format$(Val(Mid$(strLastECONum, 9)) + 1, "0000")

You could place the year(date()) directly within the string. I personally just don't like this approach however many prefer it, if you do then eliminate the Dim and the strYear as suggested for lines 4 and 5, instead just modify line 6:

strNextECONum = "ECO" & year(date()) & "-" & Format$(Val(Mid$(strLastECONum, 9)) + 1, "0000")

ADezii and I approach this a tad differently; however, if this works then fine.

cadmaster]
Where is the Record Source? I can't see it.

Thanks
Can I email you the file so you can help me out? Thanks

- The record source is the table ADezii said to create.

- Please do not pm attachments to our experts unless they have requested it.

[cadmaster]

Hi Zmbd,
Where actually I have to type the provided information? I am very new at MS Access. Could you please kindly write me step by step from the beginning through the end?

Thanks

[zmbd]

See ADezii's post: POST#5

also these will help you get the foundations you need:
---

• MS Access 2010 Tutorials
• Access Basics - By Crystal
• Micrsoft: Office2010 tutorials
• Microsoft: Getting started with Access 2010
• > Database Normalization and Table Structures.

---

[cadmaster]

Hi zmbd

[ECO Number] - {TEXT 8} <<-- does it one go onto the ECO table?
Thanks

[zmbd]

Yes, however, for your table:

Table named: ECO
Field named: [ECO Number]
Properties: Text 8 characters for ADezii's code in #5, or if you use my modification then 12 characters in size.

You will need at least one entry such as:
Record #n: [ECO Number] = ECO-0000

OR with my modification:
Record #n: [ECO Number] = ECO2013-0000

Call the function in your default value of a textbox with the control's control source set to [ECO]![ECO Number]

As ADezii stated, if you start with "0000" then the next number will be "0001" or if you start with "0099" then the next will be "0100"

I've proofed both ADezii's codes and my mods in a DB with good results.

Mind you, I detest spaces in field names and would further modify the code to replace the spaces in both code and the field name with the underscore; however, that's just the old school programmer in me.

[cadmaster]

Do I need to put the bracket [...] in front and after ECO Number?
thanks

[zmbd]

In the code, required because it has a space, and because it's good practice to squarebrace field names, yes.