Full Version: self Auto Number
UtterAccess Forums > Microsoft® Access > Access Forms
mkkashif
hello dears
please see the code .i try this code 100 of times but not working.
where i am wrong .i see a example where this code is working very properly but in my case it is not working .i am dead sure there no problem of any spelling of my field.
only the difference is i have query attach and form like order form northwind.
Main Form and subform.
can somone help.
!--c1-->
CODE
Private Sub Form_Current()
  If Me.NewRecord Then
    On Error Resume Next 'It should never occur, just to be sure...
    Form_invoice!autonumber.DefaultValue = Nz(DMax("[autonumber]", "autonumber"), 0) + 1
  End If
End Sub
GroverParkGeorge
In your database, do you have a table called "autonumber" and a field in that table called "autonumber"?
eorge
dannyseager
your table is called autonumber and your field is called autonumber?
You also don't have (or appear to have) the lable you are sending it to on error
I would change it to
Private Sub Form_Current()
On Error Resume Next1
If Me.NewRecord Then
Form_invoice!autonumber = Nz(DMax("[autonumber]", "autonumber"), 0) + 1
End If
Exit Sub
Next1:
msgbox "It errored out"
End Sub
dannyseager
George.. you read my mind... frown.gif
GroverParkGeorge
And you mine. giveup.gif
eorge
mkkashif
Hello every one
Onow change the my table name to bill2.
it's working
Realy i am not lying .when i shutdown the system last time it's not working but when i wake up it's working.
please have a look it's right.
CODE
On Error GoTo ErrorHandler
If Me.NewRecord Then
Me!autonumber = Nz(DMax("[autonumber]", "bill2"), 0) + 1
End If
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub
GroverParkGeorge
Congratulations on getting it to work.
eorge
mkkashif
hello
the problem is that when i open the form .the record shows "1" in the autonumber field.
Oset the form "DataEntry" No.
but it goes to new record i have a button to proceed all that things.
what is that problem.
GroverParkGeorge
Do you have a table called "bill2"?
Is there a field named "autonumber" in that table?
George
mkkashif
yes i have all that things which you write.
GroverParkGeorge
What is the largest current value in the "autonumber" field in bill2?
eorge
mkkashif
i set that field to 0 i mean default value is "0"
start from the begining
I want to start from 1
"0"
GroverParkGeorge
So, if the current largest value in that field in your table is "0", then your code is working correctly when it puts "1" in the field on your form.

The DMAX function returns the largest value from the field "autonumber" in the table bill2. Since that is currently "0", DMAX returns "0"

The next part of your code adds "1" to that value. 0 + 1 = 1. That is what your form displays.


George
mkkashif
ok
Oknow that.
but the problem is that i have a button to run new record.
i want when that button pressed it's should go to new record and then show 1.
but now when i open the form the autonumber field is showing 1
my all control are disabled.
i have a code to enabled in the button.
so i want when the button is presed it should work.
i set the form data entry property to no.
R. Hicks
Where are you executing the code you posted ???

No code is needed here ...
All that is needed is to place the following in the Default Value property of the txtbox on the form in question:
CODE
= Nz(DMax("[autonumber]", "bill2"), 0) + 1

Remove the code you posted in your earlier reply ...

Also ... I'm not real keen on your field name being "autonumber" ...
I suggest that the name of this field be named something else ...

RDH
GroverParkGeorge
I'm sorry, but I'm just not following here.
That is the purpose of the "AutoNumber" field? I assumed from your codethat you want it to increase each time you add a new record, but now you say,

Do you want it to show "1" each time you press the New Record button? Or do you want it to show the new, next larger number created by the code?
Why are all your controls disabled? Have you disabled yourself, or is that part of the problem you are having?
Having the form's data entry property set to "no" means that it will show all of the records in the table. it should make no difference here.
George
mkkashif
hello every one.
Ricky your code is not working.i put this code in the field autonumber Default Property .
it is showing 0 always.
= Nz(DMax("[autonumber]", "bill2"), 0) + 1
Geroge my Second Problem.
when i put the first record manualy in the the table .it's work fine.
but if i want to enter that record by form it is not working to my need.
basicaly i have a button which enable the customerid and go to new record.
can you guide some.
mkkashif
Hello Ricky .
Ochanged the field name Autonumber to Auto.
if your code work i think it can solve my problem.
GroverParkGeorge
I have asked a set of questions about your form and the specific problem. Until you answer them, I have no idea how to proceed.

HAs Ricky has pointed out, you'd be wise to rename this field from "autonumber" to something else.


George
Edited by: GroverParkGeorge on Thu Dec 2 2:02:45 EST 2004.
mkkashif
your question
Do you want it to show "1" each time you press the New Record button? Or do you want it to show the
new, next larger number created by the code?
I want new record .next larger number created by the code.
i disabled my code for my own purpose.
sample code of one button.
!--c1-->
CODE
Private Sub cmdestimate_Click()
If Me.cmdestimate.Visible = False Then
MsgBox "Please Save Record Before Go to Next Record"
Exit Sub
End If
On Error GoTo ErrorHandler
    DoCmd.GoToRecord , , acNewRec
    Me.cmdenterdate.Enabled = True
    Me.CustomerID.Value = 1
    Discount = DLookup("discount", "customers", "customerid=id")
stype = "SELECT [customers].[id],[Customers].[name] " & _
"FROM customers " & _
"WHERE [custype] = 'Local' Or [custype] = 'Udhar' Or [custype] = 'Estimate';"
Me.CustomerID.RowSource = stype
Me.Desc.Enabled = True
Me.Transiction = Me.text
Me.infoName.Visible = True
Me.infoPhone.Visible = True
Me.infoName.Enabled = True
Me.infoPhone.Enabled = True
Me.FullRate.Value = "NormalRate"
Me.search.Value = Null
Me.search.Requery
Me.text.Value = "Estimate"
Me.billdate.Enabled = True
Me.CustomerID.Enabled = True
Me.infoName.SetFocus
Me.Terms.Value = "No Terms"
'''visibilty of the buttons
Me.cmdcashpurchase.Visible = False
Me.cmdcashpurreturn.Visible = False
Me.cmdcashsale.Visible = False
Me.cmdcashsaleout.Visible = False
Me.cmdestfull.Visible = False
Me.cmdestimate.Visible = False
Me.cmdfull.Visible = False
Me.cmdReturn.Visible = False
Me.cmdfullReturn.Visible = False
Me.cmdnew.Visible = False
Me.cmdpurchase.Visible = False
Me!vitemtab.Form.remain.ColumnWidth = 10
Me!vitemtab.Form.remain1.ColumnWidth = 10
Me!vitemtab.Enabled = True
    'Me![CustomerID].Dropdown
    'Me!vitemtab.Enabled = False
    
ErrorHandlerExit:
   Exit Sub
ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit
End Sub
mkkashif
i change the field name auto.
and set default value why it's not working.
= Nz(DMax("[auto]", "bill2"), 0) + 1
GroverParkGeorge
Is it possible to compress your database into a ZIP file and post it?
eorge
mkkashif
i observe that thing.
if i put this line on current event
Me!autonumber = Nz(DMax("[auto]", "bill2"), 0) + 1
it's woks
if i put this line it's not wok
Me!autonumber.DefaultValue = Nz(DMax("[auto]", "bill2"), 0) + 1
is this right
GroverParkGeorge
We have a language barrier, which makes this a lot more complicated. Therefore, I'm going to repeat some specific questions and ask some additional, specific questions, so that we can be sure we are talking about the same things.
. Your form has a text box on it. This text box is named "autonumber". Corrent?
2. You have a table in your database called "bill2". This table has a field in it called "auto". Correct?
3. You want to use the DMAX function to find the largest number already used in the field called "auto" and add 1 to it to create a new ID. Correct?
4. Is the field named "autonumber" on your form bound to the field named "auto" in the table "bill2", or is it bound to a different field?
5. What table is the form bound to? Is it bound to "bill2" or to something else?
mkkashif
no its not possible it's size is large.
why default value property is not working.
Has R.hicks said to set the property default propety
= Nz(DMax("[auto]", "bill2"), 0) + 1
why it's not work
GroverParkGeorge
>
What exactly do you mean when you say it doesn't work?
oes it display anything?
Does it always display "1" for every new record?
Does it display the word #Error? or #name? or nothing at all?
mkkashif
it always show "0"
when i set the default value
= Nz(DMax("[auto]", "bill2"), 0) + 1
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.