Increase value by 1

[prncstela]

Hi. I am fairly new at Access and am in need of some help...

I am trying to use the database located here:

http://office.microsoft.com/en-us/templates/TC102068781033.aspx?pid=CT101428241033

On the form Account Transaction List I would like for the Entry Number field to start with 08001 and then increase by 1 for each new entry.

How do I make this possible?

thx

tela

[NeoPa]

Tela,

We are going to need to see your question posted in here. No-one wants to go searching out, or linking across, to find out what your question means.

It needs to be expressed in the thread, clearly.

[nico5038]

You'll need to create your own function to create the needed increment.
A sample can be found at: http://bytes.com/topic/access/answer...umber-question

Nic;o)

[prncstela]

Tela,

We are going to need to see your question posted in here. No-one wants to go searching out, or linking across, to find out what your question means.

It needs to be expressed in the thread, clearly.

Ok... sorry about that.

Right now I have (thanks to Nico's post)

Private Sub Entry_Number_DblClick(Cancel As Integer)
Me.Entry_Number = Val(Left(Me.Entry_Number, 5) + 1)
DoCmd.RunCommand acCmdSaveRecord
End Sub

So, when I double click the Entry Number field on the Account Transaction List form it increases what is currently in the field by 1 each time it is double clicked (so, I could do it all day and it would +1 each time). Also, there isn't anything in the Entry Number field when I start a new record so I get Run-time error '94': Invalid use of Null.

What I would like is after I click New Entry the Entry Number field takes the highest value from the Account Transactions table(Entry Number field) and +1.

After looking around I found a code and tried to tailor it to what I need and came up with this:

Private Sub Entry_Number_GotFocus()
Me.Entry_Number = DMax(Val([Entry_Number], “AccountTransactions”)) + 1
End Sub

But, I get Compile error: Wrong number of arguments or invalid property assignment.

What am I doing wrong?

[nico5038]

You need to change the code to:

Private Sub Entry_Number_GotFocus()
Me.Entry_Number = DMax("Val([Entry_Number]", “AccountTransactions”)) + 1
End Sub

Or better:

Private Sub Entry_Number_GotFocus()
Me.Entry_Number = NZ(DMax("Val([Entry_Number]", “AccountTransactions”)),0) + 1
End Sub

To make sure that an empty table works too.
Personally I prefer to put this code in the OnInsert Event...

Nic;o)

[prncstela]

You need to change the code to:

Private Sub Entry_Number_GotFocus()
Me.Entry_Number = DMax("Val([Entry_Number]", “AccountTransactions”)) + 1
End Sub

Or better:

Private Sub Entry_Number_GotFocus()
Me.Entry_Number = NZ(DMax("Val([Entry_Number]", “AccountTransactions”)),0) + 1
End Sub

To make sure that an empty table works too.
Personally I prefer to put this code in the OnInsert Event...

Nic;o)

Thanks, Nico. With the code you suggested I'm getting a Compile error: Expected: end of statement

Any ideas??

[nico5038]

Oops, overlooked theclosing ')' in your Val() function, try:
Me.Entry_Number = NZ(DMax("Val([Entry_Number])", “AccountTransac tions”)),0) + 1

[prncstela]

Oops, overlooked theclosing ')' in your Val() function, try:
Me.Entry_Number = NZ(DMax("Val([Entry_Number])", “AccountTransac tions”)),0) + 1

I am now receiving a syntax error? I apologize for being a pain.

Also, does it matter if I am putting this on the VBA for the form or the table? I am putting it in the one for the table (wasn't sure if that made a difference).t

[nico5038]

I am now receiving a syntax error? I apologize for being a pain.

Also, does it matter if I am putting this on the VBA for the form or the table? I am putting it in the one for the table (wasn't sure if that made a difference).t

Try:
Me.Entry_Number = NZ(DMax("Val([Entry_Number])", “AccountTransac tions”),0) + 1

Nic;o)

[NeoPa]

Nico,

What you missed there I think is the fact that the double-quotes (") characters you're using are not!

If you look at AccountTransact ions - you will see they are special characters, presumably from a word-processor, rather than the actual (") character itself.