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 fRetSeqECONumber() 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 =fRetSeqECONumber().
4. The existing ECO Numbers will be displayed, but when you add a New Record fRetSeqECONumber() 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.

[cadmaster]

Hi zmbd,
Can I send you some image to see I do it right or not?

[cadmaster]

I entered either [ECO Number] - {TEXT 8}
or [ECO Number] in the Field name and it gave me an error message when I hit enter...

[zmbd]

creating the database
(this is the sublink http://profsr.com/access/accless03.htm its for ACC2003; however, this is so fundimental that it really hasn't changed.

work thru building the tables.

We simply can not train you in the basics here, that is why I provided the links to the tutorials.

[cadmaster]

I already made the Table and everything... The only thing that I need is to make the ECO Number field generate the number by itself and I got it. But I am not very happy with what I got.
I want something like you wrote... but unfortunate, I can't follow your instruction...
I entered [ECO Number] and Access gave me the error...

[zmbd]

In the table you do not use the []
In the code you use the []
In the form you bind the text control to the table field = control source
In the form you set the default value of the textbox control to the function.
The function is either in the form or in a standard module.
This is what was said in post#5

To use the modified form of the code, then at the table level you must increase the field from 8 charactors to 12.
This was said inpost#9

[cadmaster]

throughout the books and training I have, I've never seen those brackets are used in the field record...I give up...

[zmbd]

What part do you not understand?

do NOT use the [..] in the table designer.

because the field name has a space in it you will have to use the [..] for the VBA and Macro.

This is in just about every reference book, tutorial site, and database class I've ever taken over the past 25 years.

[cadmaster]

The part I don't understand is I put the brackets there and access gave me the error message...This website doesn't allow me to post the image otherwise you will know what I am saying... If I use =Nz(DMax("[ECO Number]","ECO Table"))+1... Is there a way to to make the access to generate the ECO Number like
ECO2013-001, ECO2013-002 and so on...and it will also generate the ECO Number to this ECO2014-001... whenever the new year comes up... Thanks

[zmbd]

The part I don't understand is I put the brackets there and access gave me the error message

Where, in your left pocket? Sorry to be snide; however, I cannot read your mind.
I was very clear as to the proper usage.

...This website doesn't allow me to post the image

Yes you can post an image. Use the advanced editor; however, you really should not need to post an image.

otherwise you will know what I am saying... If I use =Nz(DMax("[ECO Number]","ECO Table"))+1

IN design view of the form, show the properties for the text box that is bound to the field [ECO Number]
This formula goes in the default value for the control; however, what we gave you was a custom function.
You must open the VBE first: <alt><f11> and then insert a module or if in ACC2007 or ACC2010 you can use the ribbon create and module or if you want it in the form you put it there - in any case the code ADezii gave, or his code with my modifications, is to be placed there...
You then place =fRetSeqECONumber() in the default value for the control.

... Is there a way to to make the access to generate the ECO Number like ECO2013-001, ECO2013-002 and so on...

Yes, take ADezii's code and modify it as I have instructed.

• I know that his code works.
• I know that his code modified as I have instructed works.
• I used both in one of my test databases without issue.

I added a field to one of my tables, named it "ECO Number" set it to text size 12
Opened the form I had that was already bound to the record set (I usually use a query to the table and not directly to the table; however that's a moot point) where I had added the new field, I use the show fields dialog and added the new field to my form. I then showed the properties for that control and inserted the formula exactly as shown in the default value for the control (NOT THE CONTROL SOURCE!!!).
I inserted a standard code module, copied ADezii's code directly from this thread to the module, altered it as I showed in my thread.

The results were exactly what you have asked for.

I'm sorry if you are unable to follow this... it may be that you simply have not had the correct instructor and the books you've referenced may have been poorly written, lord knows that there are plenty of those on the bookshelves. THUS, if you cannot follow me at this point, then You MUST work thru all of the tutorial links I gave you earlier. Once you have done so, the instructtions here will be clear as water.

Best of Luck.
-z

[cadmaster]

Can you please send me the one you make for the test purpose so I can see what you did? please, please...

[zmbd]

really?

how is the test database going to help you when you cannot understand the prior explanations?

explain to me what you are going to look at, how you are going to get to each section to look at it?

Do the tutorial.

[cadmaster]

zmbd
I opened the module and paste the codes onto it. I did all the required setting.
I went back to the form and clicked on the button to generate the number. I saw the #ERROR popped out.
What did I do wrong? Grrrrrrrr....
Help me please!
Can you send the one you did to me so I can follow it?
If you send it to me I am 100% sure I can follow it and will my form work. I spent lot if times to try out your codes but it didnt work. I might have done something wrong or your codes dont work at all.


The below is for the General tap of the ECO Number

Field Size : Long Integer
Format:
Decimals Places: Auto
Input Mask:
Caption:
Default Value: 0
Validation Rule: Is Not Null
validation ext: Cannot be blank.
Required: Yes
Indexed: Yes (No Duplicate)
Text Align: General

[cadmaster]

zmbd
I have tried exactly what you said and it didn't work.
I think your codes are incorrect. Thanks for helping.

[zmbd]

The below is for the General tap of the ECO Number

Field Size : Long Integer
Format:
Decimals Places: Auto
Input Mask:
Caption:
Default Value: 0
Validation Rule: Is Not Null
validation ext: Cannot be blank.
Required: Yes
Indexed: Yes (No Duplicate)
Text Align: General

We told you "Text" not numeric and field size 12.
RT*M!

cadmaster
zmbd
I have tried exactly what you said and it didn't work.
I think your codes are incorrect. Thanks for helping.

No you didn't
No neither ADezii nor My code has an error.

You lack the required basics.

Do the tutorial.

MAKE SURE YOU DO THE REMEDIAL LINKS INCLUDED THEREIN TOO!
(yes, I yelled)

[cadmaster]

Hi zmbd
I already told you that I am a beginner. I have spent lot of times to follow your instructions. Your instructions are for either intermediate and advanced users.
Please help me out.
I have tried to figure out field size 12 I didnt find it I asked you about it but you did answer me
So which field should I use text nit numerical. and where should I find size 12?
Thank you. I really need your help

[NeoPa]

Why is it that you repeatedly ask for help, yet when you are given advice you refuse to take it.

No-one can explain to you how to do anything until you understand the language that the subject works in. It's not a foreign language, but it may as well be for you, as you show no indication of understanding it.

You have been provided with some tutorial links in very basic English. This you should be able to understand. There is no excuse for ignoring such instructions then begging for more detailed instructions that you are unable to follow.

Why would anyone do more work to help you when you've already shown that you aren't prepared to follow the instructions already provided for your benefit? Obviously Z and ADezii have done that anyway, but because you don't understand enough you have been unable (or unwilling) to follow those further instructions properly.

If you are serious about wanting to make any progress in this then I suggest you do what everyone who has any experience here knows is necessary for you first. Explore the tutorials so you are in a position to move forward with help.

PS. As a friendly tip, I suggest you don't make claims about code not working when you are in a position of such ignorance that you cannot possibly know. People don't generally take well to such claims, even when made by others that know what they're talking about. When it comes from someone who's demonstrated they have little idea of the subject matter, they have every right to feel insulted. No-one should ever do that.

[cadmaster]

OK Neopa...
you sound like you are very smart. Let's assume I am a dumb person.
Could you please show me more about the [ECO Number] - {TEXT 8}.? Please show it to me.. this is the only one that no one answer me this....
If you don't mind, please make a table and a form only for the ECO Number only and send it to me.. I will figure it out from there...
Thanks

[NeoPa]

You really think that refusing to follow advice is a good way to get people to help you? That sounds dumb to a level I find hard even to imagine. Did nothing I said in the previous post make any sense to you? Did you actually read it?

I'm not going to expend time and effort to help someone who cannot even be bothered to help themselves by following the very simple instructions provided earlier in this thread (Post #11 by Zmbd). There is already large quantities of help in the thread which someone with a basic level of competence could use to solve the problem. You have illustrated already that you don't have that level yet. Whatever I were to tell you is just as likely to be misunderstood as the rest of what's here. I couldn't tell you anything new.

I'm certainly not prepared to go to the extra effort of spoon-feeding you simply because you're not prepared to put even half that much effort into your own cause. That you don't appear to appreciate that in spite of the number of times it's been explained is very hard for me to appreciate. I admit to being a little baffled.

[cadmaster]

why don't you show me how? or follow their instruction and do it and send the file to me? I just want to see your talk goes with your knowledge or not? I appreciate zmpd help but I don't appreciate with someone who just sits and says something which they don't even know anything like me...Prove to to me that you can follow their instructions...:)
I try to get this done so I can present this to my group... since I am a novice in access therefore I need help... if I know much about access.. I will not come here and ask for help...

[NeoPa]

It seems to be your habit to ask questions immediately after those questions have been answered and fully explained in the previous post. Perhaps this can help you to understand why no-one is prepared to work with you at such a spoon-feeding level. Why waste the effort when you clearly have great difficulty comprehending what is being said.

Everything I could say to you to answer your question has already been said. ADezii's post #3 is a good example. Much against my better judgement I actually did some searching for you for other resources that might have made it easier for you to follow, but when I'd spent some time doing that I came back to this thread again, only to discover it's explained better and more simply even in here.

What can I possibly tell you that you haven't been told already? Having seen the results and your responses to these instructions, it's clear to me, as indeed it is to the others involved in the thread, that you will get nowhere until you get a grip on yourself and actually do some necessary preparatory work to enable you to understand what's being said. That's why Z posted the links to some tutorials. Not because he and ADezii aren't some of the most generous of their time and experience that you'll find anywhere. We have all realised that without that help you're unlikely to make any progress, no matter what details we pass on to you.

It seems extraordinary that you ask for help, yet resist so strenuously doing any work to help yourself. If you'd put the same amount of time and effort into helping yourself as you have into trying to argue that we're all wrong and don't know what we're talking about, you'd probably already be past this problem by now.

NB. I hope you manage to appreciate what is being said this time. I must tell you now that I will not spend further time and effort on any recurrence of this fatuous line of discussion. The same basic question has been asked many times and the answer given in great detail in an attempt to help you understand. If that's still not enough then I'll give up.