Auto increment of numbers with alphabets

[Shiva Kemkar]

Yes, I have made necessary changes to the code posted on this site. I.e changed the table name as change number and field name as change no

 

[arnelgp]

Do you have space on tablename or fieldname. If so you must enclosed them in sqr bracket in the function.

"[change number]"
"[change no]"

 

[Shiva Kemkar]

This is taken care. I have used underscore as separator. May be I can share the snap shots tomorrow.

 

[jdraw]

Shiva,
You said you are new to MS Access and vba, so I'd like to know why you need this codified string. I'm not saying you do not, what I am asking is the business rationale. Quite often I see newcomers to Access with a preconceived solution of how to do something. Quite often there are underlying database concepts that make their "solution" more of a kludge than database approach. Again, I am not saying that is what you are doing or thinking, I'd just like to hear the requirement rather than a solution to something that hasn't been described in business terms.

And ,as Doc suggested (powers to be) it wouldn't be the first time I've heard a "boss identify the solution" with no knowledge of database, Access, vba nor technical skills.

Good luck with your project.

 

[Shiva Kemkar]

It's self initiative to bring in some improvement. Want to change from excel to access and learn also opportunity to learn new things.

 

[Shiva Kemkar]

If you can post some solution to my requirement, it would be of great help. Thanks

 

[plog]

It's self initiative to bring in some improvement

A page back it was dictated from the mountain. 16,347 by any other name, even if it includes weird characters and is suffixed with a letter representing a year is still the same: An identifier.

You're not really learning anything of value with this exercise. Autonumbers are the way to go.

 

[Shiva Kemkar]

Ya, I know. Auto will help. It's a guideline to follow this format. Also want to link this to SAP change number creation.

 

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Shiva Kemkar]

Hello arnelgp, June7,
I have attached the snapshot of the changes i have made. Hope we can figure out what went wrong. thanks for your support until now

 

[Shiva Kemkar]

, missed it, now you can find the attachment.

 

[arnelgp]

Remove the =NewId() expression from the control. Set the controlsource to change_no.
Add event the the Form's beforeInsert event.

private sub form_beforeInsert(cancel as integer)
Me.change_no=NewId()
End sub

you are asking for solution but you have not tried my function.

 

[Shiva Kemkar]

sorry master, i forgot to mention that. i did use your code. while posting i used the latest one. i could see the same error. I also have your code mapped in the module.

After the recent changes. in both the cases, the form is blank. does not show up any value.

 

[arnelgp]

The code will run on new record so go to new record, type something in the description textbox.

 

[Shiva Kemkar]

does not work, i have inserted 2 records manually in the table
A002AA414A/ sample 1
A002AA415A/ sample 2
i can only see these 2 records on the form.

 

[arnelgp]

You add record through the form. What is it that you dont understand.

 

[Shiva Kemkar]

My understanding is that every time I open the form I see the new change number I the field and I need to update the description to add new record.
1. I did add a button and changed to add new record, does not work.
2. I don't see the incremental value in the field when I open the form. Sorry, things are not working on my side. I don't want to give up reaching soo far.

 

[arnelgp]

As i have said and will say iy again you have to type something in the description on new record before the function will work. You also need to place the beforeinsert event of the form.

 

[Shiva Kemkar]

supper master, now it works. looks like it was a problem with the module.
i have it on the form page code and it works sweet.

 

[arnelgp]

Now you're good. Nobody is mastet here, all are servants.