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

[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 =fRetSeqECONumb er() 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