Sequential Number by group in a field

[bennyhana88]

Hello all. I have access 2010 and 2013 between work and home. I need a solution for my problem that I can use interchangeably between both platforms.

Here is the problem, I have a table like the one below. I need to Auto Sequence based on JobIDOne in the field that says sequence in the manner that I've typed. Hand typing is not an option because by table will be updated regularly. All other fields are updated via a form. I need a either a macro or VBA solution that can reconcile this, preferably through the table. Note, I do not want to use a query to create this sequencing or SQL language as I do not know how to write SQL commands.

I know it can be done but I've seen about a thousand ways to do it that I haven't been able to modify for my specific table.

DateOne	TimeIn	JobIDOne	InitialsOne	Dates Worked One	Sequence
7/17/2015	6:45:10 PM	12345	AR	7/17/2015	1
7/17/2015	6:45:17 PM	12346	BVD	7/17/2015	1
7/17/2015	6:45:35 PM	12345	AR	7/17/2015	2
7/18/2015	4:15:47 PM	12345	AR	7/18/2015	3
7/18/2015	4:15:53 PM	12346	BVD	7/18/2015	2

[June7]

Generating custom identifier is a common topic. If you want to save this value into table, will require code that looks up the most recent Sequence for a group and increments it. Otherwise, this sequence can be generated 'on-the-fly' in a report by using textbox RunningSum property (only available on report, not form) and report Sorting & Grouping feature. Review http://allenbrowne.com/ranking.html

Also https://www.accessforums.net/forms/a...ing-23329.html

[bennyhana88]

I've seen this kind of code before but I don't know how to apply it to my table. I need table specific assistance. I'm new to access and I'm learning VBA at a snail's pace. Is there an example of the code using my example table fields that I could utilize or that anyone would be willing to produce?

I'm sorry if I'm asking a lot, all of these codes get really confusing when they aren't using your table.

[bennyhana88]

P.S. yes I want to save this in a table, not a report.

[June7]

Since your sequence is a number (not a mix of text and digits), might just be able to use DefaultValue property of textbox. Something like:

DMax("Sequence", "mytable", "JobIDOne=" & [JobIDOne]) + 1

The real trick is figuring out where to put this expression. Possibly in a subform that is linked to main form on JobIDOne PK/FK fields. Don't really know your db design well enough.

However, issue with generating custom identifier is the risk of multiple users generating and attempting to save the same identifier. Minimize this risk by immediately committing the record to table. Now this adds complication if the user is allowed to abort data entry of a new record. Managing this to assure data integrity can get rather complicated.

That is for new record data entry. If you want to update a table full of existing records, that is another issue.

[bennyhana88]

This expression will add a new number to the Sequence field or the JobIDOne Field? JobIDOne is my group field that identifies how I want my sequence field to run. The numbers need to increase in the sequence field based on the number entered in the JobIDOneField. JobIDOne is the ID number for a particular employee. I'm just trying to count the number of times an employee clocks in through my database. Would it be helpful if I uploaded my db so you can see the form, tables and queries to get a better understanding of my end goal?

[bennyhana88]

Attached is the access file for my DB. Hopefully it works.

[June7]

The expression is intended to increment the Sequence field, the JobIDOne field is in the WHERE CONDITION argument.

Why does the table have field name TimeIn but no TimeOut?

Perhaps this will be helpful http://allenbrowne.com/subquery-01.html#AnotherRecord

[bennyhana88]

I placed time out in a second table and figured if I had code that I could apply to the time in table I could make small changes to apply it to the time out table. I put them in two different tables so I could run a query and compare time in and time out in the same line. If I had them in the same table I would have to right more code to fill in a null value and it seemed like a bigger headache to do it that way.

You'll also see two date columns; one has a time stamp but the other just carries over the date value out of the time stamp so I can do my analysis in an excel pivot table and sort by date.

[bennyhana88]

P.S. This is the error I get when updating the default value in sequence with the first equation you gave me.

[June7]

I've never actually tried that technique and apparently DefaultValue property cannot handle domain aggregate functions. Use the expression in VBA code in an event. Try the form Current event.

If Me.NewRecord Then
Nz(DMax("Sequence", "mytable", "JobIDOne=" & [JobIDOne]),0) + 1
DoCmd.RunCommand acCmdSaveRecord
End If

Review http://www.baldyweb.com/CustomAutonumber.htm

If you had one table would need code to find the last record with a time in value but time out was still null and then populate the time out so each record would have a time in and time out pair. That is a conventional approach and I recently assisted another poster with that schema.

[bennyhana88]

Unfortunately the second line of that command gives me an error (Expected: =)

[June7]

Sorry, forgot.

Me!Sequence = Nz(DMax("Sequence", "mytable", "JobIDOne=" & [JobIDOne]),0) + 1

[bennyhana88]

Still having trouble getting it to work

[June7]

What is the 'trouble' - error message, wrong results, nothing happens?