Generate Autonumber

[furnitureheaven]

Hi,
I have a table in access with 600 records. a field RefNo got numbers in non sequences. I want to put a auto number on this field. so when user click on Add button, its automaticlly generate a number in a RefNo Coloum.
I have a code like this, but its not worked. Could anyone help in this,

Private Sub cmdadd_Click()
On Error GoTo Err_cmdadd_Click

    DoCmd.GoToRecord , , acNewRec
    Me![RefNo] = NewRefNo()
    Me![PartyName].SetFocus
Exit_cmdadd_Click:
    Exit Sub
Err_cmdadd_Click:
    MsgBox Err.Description
    Resume Exit_cmdadd_Click
    
End Sub

 

[pbaldy]

Clarify how it doesn't work. Error, incorrect result, etc. We would need to see the function as well.

 

[furnitureheaven]

Clarify how it doesn't work. Error, incorrect result, etc. We would need to see the function as well.

Please check the attach file for details.

 

[lala]

where's the NEWREFNO function?

 

[pbaldy]

That database does not contain the posted code, and the button works correctly as it is (goes to a new record).

 

[lala]

yes, i know, so i just put his code from the forum in the button
he mustve posted the old database version

anyway, the function is not there, did he forget that too?

 

[furnitureheaven]

yes, i know, so i just put his code from the forum in the button
he mustve posted the old database version

anyway, the function is not there, did he forget that too?

What function you are talking about. this is the whole code to add new ref no. do i need to mention function here. please mention brifely. i don't have much experience in access.

database version is current. Add button is worked fine but only not generate any Auto number.

 

[lala]

    Me![RefNo] = NewRefNo()

there has to be a function in your database called NEWREFNO

if there's not - then you should be getting an error message

also, in the database you posted, your button doesnt have your code, all that button does is goes to the new record and that's all the code says to do

but still, if you want to use that code, you have to have newrefno function

 

[lala]

http://www.access-programmers.co.uk/forums/showthread.php?t=143842&highlight=autonumber

i haven't tried this, but it sounds like what you need

 

[beanbeanbean]

i think what furnitureheaven needed is an automatic number generator.

even though microsoft access has an autonumber function. but when u delete records from the table. the autonumber will still continue going on.

for example, 1 2 3. i delete 3 off the table and insert a new record. the number will go 1 2 4.


i think what he wants is a code for a autonumber which would go 1 2 3 even after a record is deleted. this means when he inserts a record the code would manually generate an auto number.


but i'm sorry i dont know how to do it. i'm trying to find a solution for it too.

 

[beanbeanbean]

hey lala ! that auto number is jus what i needed. but i cant seem to add it to my database. i copied the module 1 and put it in my module one. created a mynumber in the codings but there's a exit function error. y is that so ?

 

[lala]

i will look at it little later

 

[lala]

i copied the module 1 and put it in my module one. created a mynumber in the codings but there's a exit function error. y is that so ?

let me see everything that you modified
meaning, the MYNUMBER whatever that is
and where you put the GETNEXTNUMBER function, everything you wrote

 

[beanbeanbean]

hey lala thanks for helping me !

i tried so many times but i could not get it.


here's a copy of my database and the auto generate key number. both are new pieces so u can do what ever u like with them =)


god bless.

 

[lala]

you want it to autogenerate number in which field?

i didn't see you even trying to put the code there

that's what i was saying, let me see what you've done so far

anyway, which field do you want the autonumber for?

 

[beanbeanbean]

oh sorry i forgot to tell u. really my bad. i want the auto number for the case number field in the query database field. it is curretly an auto number. i also want it for the fldlogid in the audit table. it is also currently an auto number.


i did try putting the code in. i imported the function into my module but it seems to screw some parts of the form up thats y i decided to give u a new piece to see if u could figure it out.



thanks again !

 

[lala]

is there a reason why your form is unbound?

also, what do you want to happen, as soon as you open the form the number should go there?
or after clicking a button
or after updating some other field?
or what?

 

[lala]

anyway, here's your database

first of all, i commented out all your code

second of all, i set your query as the datasource of that form
third, i renamed the query and the field
and last, the field had to be set as a number, not text


so press the SUBMIT button on a new record and you will get next number
go to the next record, press it again, you will get another number and so on

if you need it to work with all your code and the form being unbound, you'll have to mess with it some more

i can't, i'm at work