Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Access increase by 1

Posted on 2009-04-05
Medium Priority
566 Views
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
Question by:etechit
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +2

LVL 2

Expert Comment

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 31

Accepted Solution

hnasr earned 2000 total points
ID: 24074393
NextNumber = DMax("Field1", "Table1", "ID='value'") + 1
0

LVL 58

Expert Comment

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

LVL 11

Expert Comment

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

ID: 24076923
...correction to above.
public sub txtBatch_Num_AfterUpdate()
0

LVL 1

Author Comment

ID: 24088848
mildurait;

Set rs = CurrentDb.OpenRecordset(sql)
0

LVL 1

Author Comment

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

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 31

Expert Comment

ID: 24092630
Try to post a sample db!
0

LVL 11

Expert Comment

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

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

## Featured Post

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retrâ€¦
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that aâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦
###### Suggested Courses
Course of the Month8 days, 9 hours left to enroll