Generate a sequential number when adding a row to an Access table

Question

What I have is a table that stores information under an ID and a visit number. I want to automatically calculate the visit number the next time a visit record is added. To do this I need to be able to go into the table look under the ID for the last visit number, add one and make this the new visit number.

Does anyone know how to do this? I don't know how to get the data from the table and be able to use it how I want.

I was thinking maybe of using FindRecord? But that could be completely wrong.

Thank you!

EDIT--

I see on another thread the possibility of using DMax... can you use this while looking under an ID for a different value?

score 0 · Accepted Answer · answered May 27 '14 at 13:40

0

In Access 2010 and later you can accomplish that by using an event-driven "Before Change" Data Macro like this:

enter image description here

For more information see

Create a data macro

answered May 27 '14 at 13:40

Gord Thompson

116,920
32
215
418

Thanks! That looks like what I need however, I can't seem to access the BeforeChange option. Its disabled – VT555 May 27 '14 at 13:59
Any ideas why I cannot access that option? – VT555 May 27 '14 at 14:10
@Victoria Perhaps the comment at the end of [this answer](http://stackoverflow.com/a/20686675/2144390) might help. – Gord Thompson May 27 '14 at 14:20
Thank you again. But I've got that option selected. I'm confused as to why I cannot do a data macro – VT555 May 27 '14 at 14:30
@Victoria You say that you are using Access 2010, but are you working on an older-style .mdb database file? If so then that would explain it. – Gord Thompson May 27 '14 at 14:34
Maybe... That would explain it – VT555 May 27 '14 at 14:35
Do you have another way to do the numbering without using macros as I cannot access them? Please? – VT555 May 27 '14 at 14:36
@Victoria One alternative would be something like [this](http://stackoverflow.com/a/19789563/2144390). – Gord Thompson May 27 '14 at 14:40
Thanks for all your help Gord! I'll try something like that out! – VT555 May 27 '14 at 14:54

Generate a sequential number when adding a row to an Access table

1 Answers1