Solved

Access increase by 1

Posted on 2009-04-05
11
561 Views
Last Modified: 2013-11-28
I have not found an exact answer in the exchange for my question, so I thought I would ask.  I have an access table which uses BATCH_NUM as the primary key.  We are now doing multiple tests on each batch in our lab.  I will add a value called LINE_NUM to this table.  I want LINE_NUM to start at 1 and then increment 1 for each additonal test on the batch.  So, in my form, lets say batch 1111 has it's first test and by default value property of LINE_NUM, LINE_NUM on the form shows 1.  Now, the next day I am going to run another test on batch 1111 and I want LINE_NUM to be a value of 2 for this test.  Can I use an expression or VB on the form to do this?  Something like select BATCH_NUM where BATCH_NUM = txtBATCH_NUM and then find the MAX of LINE_NUM and increment by 1?  Autonumber would work, but it would just keep increasing the number for all records, not by batch number.
0
Comment
Question by:etechit
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 2

Expert Comment

by:pskeens
ID: 24074178

On event

Dim Lnum as Integer
Dim bnum as integer
Dim formbNum as integer

lnum = "SELECT max(LINE_NUM) FROM yourTable Where BATCH_NUM = formbNum"
bnum = Me.txtBATCH_NUM

txtLINE_NUM = bnum +1
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 24074393
NextNumber = DMax("Field1", "Table1", "ID='value'") + 1
0
 
LVL 57
ID: 24076319
Just be aware that if multiple users can be working within a batch at the same time that Max/DMax may possibly hand out the same number if two users got to save at the same time.
If you have this situation, you'll need a key generation routine which has some type of lock built in so that only one user can be grabbing a key at one time.
JimD.
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 11

Expert Comment

by:mildurait
ID: 24076913
You could  try something like this.

public sub txtBatchNo_AfterUpdate()

       If isnull(me.txtBatch_Num)=true then exit sub
       If isnull(me.txtLine_Num)=true then exit sub
       dim lastline as long
       dim sql as string
       sql = "SELECT TOP 1 LINE_NUM"
       sql = sql & " FROM TABLE"
       sql = sql & " WHERE BATCH_NUM=" & me.txtBatch_Num
       sql = sql & " AND LINE_NUM IS NOT NULL"
       sql = sql & " ORDER BY LINE_NUM DESC"
       dim rs as dao.recordset      
       set rs = CurrentDb.openrecordset(sql)
       if not rs.eof = false then lastline = rs.fields("LINE_NUM").value else lastline = 0
       rs.close
       set rs = nothing
       me.txtLine_Num = lastline+1
   
End sub

Note you will have to change TABLE to the actual name of your table.
0
 
LVL 11

Expert Comment

by:mildurait
ID: 24076923
...correction to above.
public sub txtBatch_Num_AfterUpdate()
0
 
LVL 1

Author Comment

by:etechit
ID: 24088848
mildurait;

Your example errors out on
Set rs = CurrentDb.OpenRecordset(sql)
0
 
LVL 1

Author Comment

by:etechit
ID: 24088871
hnasr;

Your example doesn't work as it has no idea of what the table name is.  I did insert my correct table name, but in troubleshooting my table name; tblLOG, says empty.
0
 
LVL 1

Author Comment

by:etechit
ID: 24088910
pskeens;

I get a type mismatch error on the first line
Lnum = "SELECT max(LOG_LINE_NUM) FROM tblLOG Where BATCH_NUM = formbNum"
0
 
LVL 30

Expert Comment

by:hnasr
ID: 24092630
Try to post a sample db!
0
 
LVL 11

Expert Comment

by:mildurait
ID: 24092758
@etechit      
Errors in all solutions here would point to the BATCH_NUM being of a non-numeric data type.
0
 
LVL 1

Author Comment

by:etechit
ID: 24093453
hnasr;
Your answer was correct, I was putting it in a Sub and not a Function.  It works!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question