By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,407 Members | 2,842 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,407 IT Pros & Developers. It's quick & easy.

Increase value by 1

P: 4
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/te...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
Oct 30 '08 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
Oct 30 '08 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 31 '08 #3

P: 4
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)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Entry_Number_DblClick(Cancel As Integer)
  2. Me.Entry_Number = Val(Left(Me.Entry_Number, 5) + 1)
  3. DoCmd.RunCommand acCmdSaveRecord
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Entry_Number_GotFocus()
  2. Me.Entry_Number = DMax(Val([Entry_Number], “AccountTransactions”)) + 1
  3. End Sub
But, I get Compile error: Wrong number of arguments or invalid property assignment.

What am I doing wrong?
Nov 1 '08 #4

nico5038
Expert 2.5K+
P: 3,072
You need to change the code to:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Entry_Number_GotFocus()
  2. Me.Entry_Number = DMax("Val([Entry_Number]", “AccountTransactions”)) + 1
  3. End Sub
Or better:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Entry_Number_GotFocus()
  2. Me.Entry_Number = NZ(DMax("Val([Entry_Number]", “AccountTransactions”)),0) + 1
  3. End Sub
To make sure that an empty table works too.
Personally I prefer to put this code in the OnInsert Event...

Nic;o)
Nov 1 '08 #5

P: 4
You need to change the code to:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Entry_Number_GotFocus()
  2. Me.Entry_Number = DMax("Val([Entry_Number]", “AccountTransactions”)) + 1
  3. End Sub
Or better:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Entry_Number_GotFocus()
  2. Me.Entry_Number = NZ(DMax("Val([Entry_Number]", “AccountTransactions”)),0) + 1
  3. 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??
Nov 1 '08 #6

nico5038
Expert 2.5K+
P: 3,072
Oops, overlooked theclosing ')' in your Val() function, try:
Me.Entry_Number = NZ(DMax("Val([Entry_Number])", “AccountTransactions”)),0) + 1
Nov 1 '08 #7

P: 4
Oops, overlooked theclosing ')' in your Val() function, try:
Me.Entry_Number = NZ(DMax("Val([Entry_Number])", “AccountTransactions”)),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
Nov 1 '08 #8

nico5038
Expert 2.5K+
P: 3,072
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])", “AccountTransactions”),0) + 1

Nic;o)
Nov 2 '08 #9

NeoPa
Expert Mod 15k+
P: 31,494
Nico,

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

If you look at AccountTransactions - you will see they are special characters, presumably from a word-processor, rather than the actual (") character itself.
Nov 2 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.