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

Can Access Generate a Running No.

P: 4
Hi, I am a newbie to Access. Planning to create a receipt issuing module where a new receipt with a new running number is created after the previous receipt is saved. Is this possible? Thanks.
Jun 22 '07 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,692
Hi, I am a newbie to Access. Planning to create a receipt issuing module where a new receipt with a new running number is created after the previous receipt is saved. Is this possible? Thanks.
  1. Is this New Running Number to be saved to a Table or appear on a Report?
  2. What is the Format of this Running Number?
  3. How is the Running Number to be incremented?
Jun 22 '07 #2

P: 49
Hi, I am a newbie to Access. Planning to create a receipt issuing module where a new receipt with a new running number is created after the previous receipt is saved. Is this possible? Thanks.

why not use the auto number? simple straight forward and already there you could even set the number to random if you want to have unpredicted numbers

gareth
Jun 22 '07 #3

BradHodge
Expert 100+
P: 166
If you have AutoNumber on a table that has records from other tables that have to link to that number, you could run into trouble on Compact/Repair, as the AutoNumber may reset itself and you will lose your link to those other tables.

Another method would be to establish a table called tblIncrement, with one field in it (Increment). Add a number to that field, such as 1, 1000 or whatever. Then create a form called frmIncrement with the field Increment in it.

If you have a data entry form that needs to have a record ID, just add that frmIncrement as a subform and make it visible=No.

Put this in your OnCurrent or OnLoad event of the form...

Expand|Select|Wrap|Line Numbers
  1. If IsNull (Me.RecordID) Then
  2. Me.RecordID=Forms![frmDataEntry]![frmIncrement].Form![Increment]
  3. Forms![frmDataEntry]![frmIncrement].Form![Increment] = Forms![frmDataEntry]![frmIncrement].Form![Increment] +1
  4. End If
This will give your record a unique ID that will not have the chance of resetting itself.

Hope that helps,
Brad.
Jun 22 '07 #4

P: 4
Hi, see my reply in blue
  1. Is this New Running Number to be saved to a Table or appear on a Report?Save to both Table & report(appear on receipt)[/b]
  2. What is the Format of this Running Number?5 digit running [b]
  3. How is the Running Number to be incremented?In incremental of 1 starting from 20000
Jun 23 '07 #5

P: 4
Hi, thanks for your advise. Btw how do you add a number to the field? I am using access2003. Thanks in advance.


If you have AutoNumber on a table that has records from other tables that have to link to that number, you could run into trouble on Compact/Repair, as the AutoNumber may reset itself and you will lose your link to those other tables.

Another method would be to establish a table called tblIncrement, with one field in it (Increment). Add a number to that field, such as 1, 1000 or whatever. Then create a form called frmIncrement with the field Increment in it.

If you have a data entry form that needs to have a record ID, just add that frmIncrement as a subform and make it visible=No.

Put this in your OnCurrent or OnLoad event of the form...

Expand|Select|Wrap|Line Numbers
  1. If IsNull (Me.RecordID) Then
  2. Me.RecordID=Forms![frmDataEntry]![frmIncrement].Form![Increment]
  3. Forms![frmDataEntry]![frmIncrement].Form![Increment] = Forms![frmDataEntry]![frmIncrement].Form![Increment] +1
  4. End If
This will give your record a unique ID that will not have the chance of resetting itself.

Hope that helps,
Brad.
Jun 26 '07 #6

BradHodge
Expert 100+
P: 166
If you mean the Increment field... You just need to open the tblIncrement that you created and add the number there. Then whenever your form goes and gets that number from frmIncrement, the code will add 1 to that number and it will be ready for the next record.

Brad.
Jun 26 '07 #7

P: 4
Sorry, I am a complete idiot in access. Could you demostrate on how to Add a number to a field to establish the fielf Increment? Your reply is much appreciated. Thanks!

Rgds
voltrate


If you have AutoNumber on a table that has records from other tables that have to link to that number, you could run into trouble on Compact/Repair, as the AutoNumber may reset itself and you will lose your link to those other tables.

Another method would be to establish a table called tblIncrement, with one field in it (Increment). Add a number to that field, such as 1, 1000 or whatever. Then create a form called frmIncrement with the field Increment in it.

If you have a data entry form that needs to have a record ID, just add that frmIncrement as a subform and make it visible=No.

Put this in your OnCurrent or OnLoad event of the form...

Expand|Select|Wrap|Line Numbers
  1. If IsNull (Me.RecordID) Then
  2. Me.RecordID=Forms![frmDataEntry]![frmIncrement].Form![Increment]
  3. Forms![frmDataEntry]![frmIncrement].Form![Increment] = Forms![frmDataEntry]![frmIncrement].Form![Increment] +1
  4. End If
This will give your record a unique ID that will not have the chance of resetting itself.

Hope that helps,
Brad.
Jun 29 '07 #8

BradHodge
Expert 100+
P: 166
No problem at all.

If you haven't already, make a new table called "tblIncrement". This table should have one field in it, called "Increment". Open the table. Enter the number that you want the increment to begin with (e.g. 20000). Close the table.

You should also have this field ("Increment") as the only part of a form called "frmIncrement". Place this form on your Main form (e.g. frmDataEntry) or where you want this receipt number to show up. Go to the properties of frmIncrement and choose Visible: NO.

On the OnLoad event of frmDataEntry, you should put the code that is referenced above. This will look at a the field RecordID (or whatever your receipt field is called on the form) and see if it is Null. If it is Null, it will go get the number from frmIncrement and place it in your RecordID field. After it places that number in the field, it will then add one to the number in frmIncrement, and be ready for the next time you open the form.

I hope that helps get you going. Please feel free to respond if you have more questions.

Brad.
Jun 29 '07 #9

Post your reply

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