Increasing number

[micropalla]

Hello !!

It's my first post in this forum and I hope you can help me...

I've a table with an ID counter that increase obviously every time of one number and another field in which 'till now I wrote a number by myself.
Now I would like that this second number is increased of 1 automatically by Access.
In a few words it should see the number in the previous record and in the next increase it of 1.
Can I do it for example indicating something in the property "before update" of the field or in some other way ??

Please help me !!

Thanks a lot and have a nice day !!

Lorenzo

 

[Mile-O]

In the Before_Update event of your form you can use the DMax function to get the highest number (I'm assuming that will also be the previous number) and then add one to it.


i.e.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.txtNumberField = DMax("[MyField]", "MyTable") + 1
End Sub

 

[micropalla]

Have a little patience...

have I got to put this code changing only the two names of my control and table in the Befor_Update of the control that i want to increase by one ??
Pressing the button "..." ??

I've tried but it gives me an error in syntax....

Thanks for your patience...

 

[Mile-O]

Me.txtNumberField

I'm guessing that you have a textbox on your form that you currently enter a number into. txtNumberField, in this instance, represents that textbox. It should be bound to the underlying table/query.

[MyField]

This is the field that contains the numerical value in your underlying table/query.

MyTable

Obviously, this is the table that you want the result to come from.


Note that I put brackets around the Field, this can also go around the table name, but are not required if: your field and table names are completely text - no spaces, no characters; just text. That might explain your syntax error.


As a second precaution, it might be possible that your table does not have a value, so it would be tidier to use the Nz() function around the DMax function which will convert any Null result to a value of our choosing (in this case 0.)

i.e

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.txtNumberField = Nz(DMax("[MyField]", "MyTable"), 0) + 1
End Sub

Also, not the Before Update of the control but the Form

 

[micropalla]

Of course my tables and fileds are named with spaces, so I've got to put the brackets.
Moreover I can't find FORM ....
Please, help me, my version of Access 2000 is not in English...

Thanks a lot

Lorenzo

 

[Mile-O]

Forma?

Open your form in design view and select properties (proprietà?) and then the Before_Update event.

 

[micropalla]

Ok, ok, i've understand that I've to modify the "Befor Update" of the form.

I've put a code like this, could it be right ??

Private Sub Numero_Protocollo_BeforeUpdate(Cancel As Integer)
Me.txtNumero_Protocollo = Nz(DMax("[Numero_Protocollo]", "[Protocolli_in]"), 0) + 1
End Sub

It seems it doesn't do anything....

Lorenzo

 

[Mile-O]

Put this line in there too in order to see if anything is happening:

MsgBox Nz(DMax("[Numero_Protocollo]", "[Protocolli_in]"), 0) + 1

 

[Pat Hartman]

If your names have embedded spaces, get rid of the underscores and surround the field names with square brackets.

Private Sub Numero_Protocollo_BeforeUpdate(Cancel As Integer)
Me.[txtNumero Protocollo] = Nz(DMax("[Numero Protocollo]", "[Protocolli in]"), 0) + 1
End Sub

PS, in the future do NOT use embedded spaces when naming objects in your db. As you can see, Access will sometimes replace the spaces with underscores but not always. It simply causes too much confusion and is considered poor practice.

 

[micropalla]

Thanks to everybody for the help but up till now it doesn't seem to function....

Can anybody help me watching my Database ?
I could send it by email....

Please... I'm will be very grateful...

Lorenzo

 

[Mile-O]

Can't you just make a copy, strip out all the unnecessary stuff leaving only the components relating to the problem: form, table, query, whatever.

Compact the streamlined copy and then post it in a .zip file to this thread.

 

[micropalla]

So...
here is the DB...

You will find a form named Protocolli out.
If you open it you can see that there is a hidden counter and the first field is "Numero Protocollo" (3410).
I would like that this number increase itself automatically every new record (3411, 3412, ecc..)
Can anybody help me, please ??

Thanks in advace

Lorenzo

 

[WayneRyan]

micropalla,

How's this?

Wayne

 

[micropalla]

YES !!!
This is what I want !!!

Many tahnks to everyone and expecially to you !!!

Thanks again !!

Lorenzo