• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

Access increase by 1

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
etechit
Asked:
etechit
  • 4
  • 3
  • 2
  • +2
1 Solution
 
pskeensCommented:

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
 
hnasrCommented:
NextNumber = DMax("Field1", "Table1", "ID='value'") + 1
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
milduraitCommented:
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
 
milduraitCommented:
...correction to above.
public sub txtBatch_Num_AfterUpdate()
0
 
etechitAuthor Commented:
mildurait;

Your example errors out on
Set rs = CurrentDb.OpenRecordset(sql)
0
 
etechitAuthor Commented:
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
 
etechitAuthor Commented:
pskeens;

I get a type mismatch error on the first line
Lnum = "SELECT max(LOG_LINE_NUM) FROM tblLOG Where BATCH_NUM = formbNum"
0
 
hnasrCommented:
Try to post a sample db!
0
 
milduraitCommented:
@etechit      
Errors in all solutions here would point to the BATCH_NUM being of a non-numeric data type.
0
 
etechitAuthor Commented:
hnasr;
Your answer was correct, I was putting it in a Sub and not a Function.  It works!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now